Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing Internal Data →  Internal Tables →  Internal Tables - Overview →  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:

The optimization is achieved by the entire WHERE condition (or parts of it) being mapped to a specified key. This key specification performs an appropriate optimized key access (binary search or hash algorithm as in the statement READ TABLE), to find and process one or more rows.

Prerequisites for the optimization are, therefore, as follows:

  • The logical expression of the WHERE condition can be transformed to a key specification.
  • The key access returns the same results as the evaluation of the logical expression would. This is guaranteed only for compatible data types, since the following happens when incompatible data types are compared:
  • When accessed using a key, the content of the specified data objects is converted to the data type of the columns before the comparison.
  • The comparison rules for incompatible data types apply when evaluating a logical expression. Here, all the data types involved play a part in determining which operand is converted into which comparison type.

If one of these prerequisites is not met, no optimization can take place, and the behavior is as follows:

  • 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 accessed using a secondary table key (if specified after USING KEY), a syntax error 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.

Other versions: 7.31 | 7.40 | 7.54

Prerequisites for the Optimization of Hash Keys

To enable the logical expression of the WHERE condition to be mapped to a key access, the expression must use (in the case of hash key accesses) AND comparisons with the operator = (or EQ) to cover all components of the key. An AND association with further comparisons is possible. Optimization may not be possible if the boolean operator NOT or an OR relationship between two comparisons is used. A condition specified using the operator IN in a selection table cannot be optimized, regardless of its structure.


Example

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

  • b, d, and e cover the entire key.
  • b, d, aund e cover the entire key and a is an independent condition.

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

  • The key component d is not specified.
  • OR is used in an association.
  • An inequality comparison takes is performed.
  • A comparison in a selection 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 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 = '...' .
    ENDLOOP.

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

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

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

    LOOP AT itab INTO line USING KEY key
                 WHERE b = '...' AND d = '...' AND e <> '...' ."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 = '...' ).
    ENDLOOP.

    Prerequisites for the Optimization of Sorted Keys

    To enable the logical expression of the WHERE condition to be mapped to a key access, the expression must use (in the case of sorted key accesses) AND comparisons with the operator = (or EQ) to cover an initial part of the key consisting of at least one component. An AND association with further comparisons is possible. Optimization may not be possible if the boolean operator NOT or an OR relationship between two comparisons is used. A condition specified using the operator IN in a selection table cannot be optimized, regardless of its structure.

    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 accesses 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 place.
  • A comparison in a selection 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 = '...'.
    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 = '...' 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 = '...' ).
    ENDLOOP.

    Requirements Made on the Operands

    The part of the logical expression that can be mapped to a key access must select the same rows as a statement READ TABLE 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 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. Instead, 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 key specification 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).

    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.

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

    APPEND text_short TO itab.

    LOOP AT itab INTO text_short USING KEY key
                 WHERE table_line = text_long.
    ENDLOOP.
    WRITE: / 'LOOP:', sy-subrc.

    READ TABLE itab INTO text_short WITH TABLE KEY key
                                    COMPONENTS table_line = text_long.
    WRITE: / 'READ:', sy-subrc.