ABAP Keyword Documentation → ABAP − Reference → Processing Internal Data → Internal Tables → Internal Tables: Performance Notes

Optimization of the WHERE Condition

The statements LOOP AT, DELETE, and MODIFY can be specified together with a WHERE condition that selects specific rows of the internal table. Searches in a standard table using the primary table key are always linear and cannot be optimized. Searches using a sorted key or a hash key, however, can be optimized in certain circumstances. These keys are used when:

editing a sorted table using its primary key.

editing a hashed table using its primary key.

editing a table (any type) using a secondary table key specified by USING KEY.

The optimization takes place because all relational expressions or a group of the relational expressions in the WHERE condition are mapped to a specified key. This specified key performs an appropriate optimized key read (binary search or hash algorithm as in the statement READ TABLE or a table expression), to find one or more rows. The row or rows in question are then checked using those remaining relational expressions from the WHERE condition that were not mapped to the specified key.

Prerequisites for the optimization are, therefore, as follows:

The relational expressions of the WHERE condition can be transformed to a specified key.

The corresponding key read returns the same results as would the evaluation of this part of the logical expression. This is guaranteed only for compatible data types, since in the case of incompatible data types:

the content of the specified data objects is converted to the data type of the columns before the evaluation in the case of key reads.

The comparison rules for incompatible data types apply when evaluating a comparison expression. Here, all the data types involved play a part in determining which operand is converted into which comparison type.

If there are no relational expressions or too few to meet both of these prerequisites, no optimizations are possible and the behavior is as followed:

If the primary table key is used to access a sorted table or a hashed table, all rows of the internal table are checked (as in a standard table).

If the table is read using a secondary table key (if specified after USING KEY), a syntax error or warning is produced or an exception raised. Accesses that use a secondary table key must always be optimized.

The following sections describe exactly when an access can be optimized.

Prerequisites for the Optimization of Hash Keys

Prerequisites for the Optimization of Sorted Keys

Requirements Made on the Operands

Other versions: 7.31 | 7.40 | 7.54

Note

If the rows selected using WHERE are modified or deleted using MODIFY or DELETE and not just read using LOOP AT, further update costs are incurred alongside the row search. When deleting rows from standard tables, it should be noted that searches using a secondary key can optimize the selection of rows to delete but do not optimize the update of the primary key also needed to delete the rows (which is usually done in a linear search).

Prerequisites for the Optimization of Hash Keys

In the case of hash key reads, there must be precisely one relational expression joined using AND for each component of the key. This expression is either

a comparison expression with the relational operator = (or EQ), whose operands meet the requirements above or

a predicate expression IS INITIAL without the addition NOT.

These relational expressions construct the part of the logical expression used for the key read. This part cannot contain duplicate key components. The remainder of the logical expression can contain any number of relational expressions joined using AND. Optimization may not be possible, however, if the Boolean operator NOT or an OR join is used. Key components may be used in the relational expressions not used for the key read.

Note

Relational expressions other than comparisons using = (or EQ) or predicate expressions IS INITIAL are not involved in the key read. This applies particularly to the tabular relational operator IN range_tab, even if it can be traced back to optimizable comparisons.

Example

In the following example, the first two WHERE conditions can be optimized as key reads with the secondary table key key. This is because:

The optimizable conditions for b, d, and e cover the entire key.

The optimizable conditions b, d, and e cover the entire key and a is a condition on a non-key column that is not involved in the part of the WHERE condition required for the optimization.

The optimizable conditions b, d, and e cover the entire key and the other two conditions on the key columns b and d are not optimizable and are hence not involved in the part of the WHERE condition required for the optimization.

The next six WHERE conditions cannot be optimized and produce syntax errors. This is because:

The key component d is not specified.

A key component is joined using OR instead of AND.

Two non-optimizable relational operators are used.

An operand of type i is used in the comparison b of type c, which does not meet the requirements made on the operands.

The Boolean operator NOT is used in front of a key component.

A further comparison is joined using OR.

A further comparison is negated using NOT.

DATA: BEGIN OF line,
        a TYPE c LENGTH 3,
        b TYPE c LENGTH 3,
        c TYPE c LENGTH 3,
        d TYPE c LENGTH 3,
        e TYPE c LENGTH 3,
        f TYPE c LENGTH 3,
      END OF line.

DATA itab LIKE STANDARD TABLE OF line
               WITH UNIQUE HASHED KEY key COMPONENTS b e d.

DATA b_tab LIKE RANGE OF line-b.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d = '...' AND e IS INITIAL.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE a = '...' AND b = '...' AND d = '...' AND e IS INITIAL.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE  b = '...' AND d = '...' AND e IS INITIAL AND
            b IN b_tab AND d <> '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND e IS INITIAL.                     "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' OR d = '...' AND e IS INITIAL OR      "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d <> '...' AND e IS NOT INITIAL.  "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = 333 AND d = '...' AND e IS INITIAL.         "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...'  AND NOT d = '...' AND e IS INITIAL.  "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d = '...' AND e IS INITIAL OR     "syntax error
           a = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d = '...' AND e IS INITIAL AND    "syntax error
           NOT a = '...'.
ENDLOOP.

Prerequisites for the Optimization of Sorted Keys

In the case of reads using a sorted key, the same applies as to a hash key. The only difference is that only an initial section of the key consisting of at least one component needs to be covered rather than the entire key.

The internal table is accessed in partly sequential form. The starting point for the processing of the table is determined by a binary search using the subconditions that cover the table key in full or in part. From the starting point onwards, the table is only processed for as long as these subconditions remain fulfilled.

Example

In the following example, the first five WHERE conditions can be optimized as key reads with the secondary table key key. This is because:

b is an initial part of the key.

b is an initial part of the key and a is an independent condition.

b and e are an initial part of the key.

b, e, and d are an initial part of the key and the order in the WHERE condition is ignored.

b is an initial part of the key and in this case d is an independent condition, even though it is part of the key.

The next five WHERE conditions cannot be optimized and produce syntax errors. This is because:

e is not an initial part of the key.

An inequality comparison does not take place.

A comparison in a ranges table is specified.

The Boolean operator NOT is used.

An additional OR relationship is used.

DATA: BEGIN OF line,
        a TYPE c LENGTH 3,
        b TYPE c LENGTH 3,
        c TYPE c LENGTH 3,
        d TYPE c LENGTH 3,
        e TYPE c LENGTH 3,
        f TYPE c LENGTH 3,
      END OF line.

DATA itab LIKE STANDARD TABLE OF line
               WITH UNIQUE SORTED KEY key COMPONENTS b e d.

DATA b_tab LIKE RANGE OF line-b.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE a = '...' AND b IS INITIAL.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...' AND e = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...' AND d IS INITIAL AND e = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...' AND d <> '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE e = '...'.                    "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b <> '...'.                   "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b IN b_tab.                   "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...'  AND NOT e = '...'. "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key             "syntax error
             WHERE b = '...'  AND ( d = '...' OR e IS INITIAL ).
ENDLOOP.

Requirements Made on the Operands

The part of the logical expression that can be mapped to a key read must select the same rows as a statement READ TABLE or a corresponding table expression that specifies the corresponding components as keys.

When comparing incompatible data objects, the WHERE condition is subject to the same comparison rules for incompatible data types. Here, the data types involved determine which operand is converted to which comparison type.

If the additions WITH TABLE KEY and WITH KEY of the statement READ or KEY in a table expression are used, however, the content of the specified data objects is always converted to the data type of the columns before the comparison.

If this produces differing results, an optimization is not possible. Due to the complexity of the comparison rules (particularly for elementary data types, it is not a good idea to construct a set of rules detailing exactly when the comparison type matches the data type of the left operand. Generally speaking,

only fully compatible operands are optimizable,

in some cases, elementary operands with different data types can be optimized, if the value ranges or lengths are suitable. For example, a comparison of a column with the type of a floating point number with an operand of type Integer or of a column of c with a similar operand, if its length is less than the length of the column.

For this reason, it is advisable to use only pairs of compatible operands in the WHERE condition. This guarantees that the differences in behavior of the WHERE condition and the specified key do not influence the result.

Example

The following example is largely similar to the example in the section WHERE log_exp in LOOP AT itab. In that case, access takes place using the primary key and no optimization. Here, however, access takes place using a secondary table key and a syntax check warning is produced and an exception raised (when the program is executed). The number of rows in the internal table determines whether the exception is raised.

DATA text_short TYPE c LENGTH 2.
DATA text_long  TYPE c LENGTH 4.

DATA itab LIKE TABLE OF text_short
          WITH UNIQUE HASHED KEY key COMPONENTS table_line.

itab = VALUE #( ( 'AA' )
                ( 'BB' )
                ( 'CC' )
                ( 'DD' )
                ( 'EE' )
                ( 'FF' )
                ( 'GG' )
                ( 'HH' )
                ( 'II' )
                ( 'JJ' )
                ( 'KK' )
                ( 'LL' )
                ( 'MM' ) ).

text_short = 'AA'.
text_long  = 'AAXX'.

LOOP AT itab INTO text_short USING KEY key
             WHERE table_line = text_long.
ENDLOOP.
cl_demo_output=>write( |LOOP: { sy-subrc }| ).

"Statement
READ TABLE itab INTO text_short WITH TABLE KEY key
                                COMPONENTS table_line = text_long.
cl_demo_output=>write( |READ: { sy-subrc }| ).

"Expression
TRY.
    text_short = itab[ KEY key COMPONENTS table_line = text_long ].
  catch CX_SY_ITAB_LINE_NOT_FOUND.
    ...
ENDTRY.
cl_demo_output=>display( |Expression: { text_short }| ).