Created by Unknown User (mro), last modified by James Hurrell on Feb 18, 2020
This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.

On this page:

Summary: This page provides an explanation of the Quality Rule 1101004 "Avoid non-indexed SQL queries" that is triggered by the SQL Analyzer during an analysis.

Quality Rule documentation

Description

This metric retrieves the artifacts containing at least one SQL Query not using a table's indexes.

An SQL Query is using table's indexes if all the  following conditions are true:

  • At least one index is defined for each non XXS table participating in the from list
  • Queries must reference left-most columns of the index key
  • the column must not be used in a function call

An XXS table is a table containing an extremely low amount of data. Default is 10.

Rationale

SQL Queries that do not use table indexes are extremely dangerous for the application's performance.

Indeed, each execution of the query will result in a full table scan which is extremely time consuming.

Remediation

Try to use indexed columns in WHERE and HAVING clauses. If it is not possible, then create a secondary index for these columns.

Reference

Enhancing the Quality of ABAP Development

Meijs - Krouwels - Heuvelmans - Sommen

SAP Press

ISBN 1-59229-030-2

Output

Associated to each Artifact with violations, the Quality Rule provides:

  • The number of violation patterns.
  • Each violation pattern shows :
  • the query
  • each table not using index
  • the table's indexes

Total

Number of Artifacts using Tables.

XXL Status

False

Parent Technical Criterion(s)

QRL - Efficiency - SQL and Data Handling Performance

  • Critical Quality Rule (for that criterion): false
  • Default value of Weight (in that criterion): 9

Metric ID

1101004

What is searched by this rule ?

The algorithm is the following for each SELECT clause:

  • all table references of the FROM clause are considered
  • we consider the columns used for filtering in WHERE (+ JOIN ON ) and we group them per table reference
  • finally we check that, given those filtered column, at least one index is usable for each table reference.

An index is usable if its first column is used as filtering.

Example 1

Let's take the following query :

SELECT * 
FROM 
   T1 as a, 
   T2 as b
WHERE 
   a.c = b.d and a.e = 1;

Here we have 2 table references :

  • T1 as a

  • T2 as b

What columns are used for each table references :

  • T1 as a

    • columns c and e

  • T2 as b

    • column d

Now we consider the indexes for each table reference. Let's say that all the indexes we have are :

CREATE INDEX i1 ON T1 (col1, e);
CREATE INDEX i2 ON T1 (c);
CREATE INDEX i3 ON T2 (col2);

For T1 as a :

  • index i1 is not usable as we do not filter by col1
  • index i2 is usable as we filter by c

For T2 as b :

  • index i3 is not usable

So query plan optimizer may use index i2 for T1 but cannot use any index for T2.

Example 2

Calculation is made for table references, not for tables.

Let's consider the same example as above but the query :

SELECT * 
FROM 
   T1 as a, 
   T1 as b
WHERE 
   a.c = b.d and a.e = 1;

Here we have table T1 twice:

For T1 as a :

  • index i2 is usable as we filter by c

For T1 as b :

  • no index is usable

Rationale

When we filter on a table not using any index, the query optimiser has less chance to do a correct job. For example :

SELECT * 
FROM T1, T2
WHERE T1.c = T2.d;

Assuming that T1.c is indexed, but not T2.d. One could say that query plan would be Full Scan ofT2, then Hash Join on T1.c. But what if T1.c is not that discriminant ? By not indexing T2.d, user takes a risk, and it is exactly what we are measuring here.