Explain Open SQL & Database Performance in ABAP in SAP

10 / 65

ABAPintermediate

Open SQL & Database Performance in ABAP

Writing efficient Open SQL statements against the SAP database layer and avoiding classic ABAP performance anti-patterns.

Most real ABAP performance problems are database access problems. The pattern behind nearly all of them is doing row by row what the database could do in one statement.

Selecting inside a loop is like walking to the store separately for every single ingredient in a recipe; a joined or FOR ALL ENTRIES query is making one shopping list and buying everything in a single trip.

Key Concepts

The anti-pattern, and why it is so expensive.

    LOOP AT lt_orders INTO ls_order.
      SELECT SINGLE * FROM kna1 INTO ls_cust
             WHERE kunnr = ls_order-kunnr.     " one round trip, per row
    ENDLOOP.

Each round trip to the database costs perhaps half a millisecond even when the query itself is trivial. Ten thousand orders is 10,000 round trips:

    10,000 x 0.5 ms  =  5 seconds of pure waiting
    one JOIN         =  a single round trip

The statement is not slow. Doing it ten thousand times is.

Three fixes, in order of preference.A join, so the database does the matching:

    SELECT o~vbeln, c~name1
      FROM vbak AS o INNER JOIN kna1 AS c ON c~kunnr = o~kunnr
      INTO TABLE @DATA(lt_result).

FOR ALL ENTRIES, when a join is not possible:

FOR ALL ENTRIES

    IF lt_orders IS NOT INITIAL.          " this guard is mandatory
      SELECT * FROM kna1 INTO TABLE @DATA(lt_cust)
             FOR ALL ENTRIES IN @lt_orders
             WHERE kunnr = @lt_orders-kunnr.
    ENDIF.

The guard is not optional. With an empty driver table, FOR ALL ENTRIES ignores the WHERE condition entirely and returns the whole table. On KNA1 that is a report that hangs; on a document table it is a short dump or an outage. This is the most-cited gotcha in ABAP, and it is silent.

FOR ALL ENTRIESWHEREKNA1

Also note that FOR ALL ENTRIES removes duplicates from the result, so a result row appearing once when you expected several is normal behaviour rather than lost data.

FOR ALL ENTRIES

Push the logic down, on HANA: a CDS view or an AMDP does the aggregation inside the database, so a million rows are summed there rather than transferred to the application server to be summed in a loop.

Modern Open SQL does far more than it used to. Since 7.40 you can write the computation into the statement:

    SELECT kunnr, SUM( netwr ) AS total
      FROM vbak
      WHERE erdat >= @lv_from
      GROUP BY kunnr
      HAVING SUM( netwr ) > 10000
      INTO TABLE @DATA(lt_totals).

Inline declarations, joins, CASE, aggregates and arithmetic all run on the database. Every row you do not transfer is time you do not spend.

CASE

The smaller rules that add up.

  - Select only the fields you need, never `SELECT *` on a wide table
  - `SELECT SINGLE` when the key is complete; it stops at the first row
  - Never `SELECT ... ENDSELECT` into a work area when you can read into a table at once
  - Sort and aggregate on the database, not after
  - Client-dependent tables carry `MANDT`; Open SQL adds it automatically, native SQL does not

SELECT *SELECT SINGLESELECT ... ENDSELECTMANDT

Indexes. A WHERE clause that does not match the leading fields of any index causes a full scan. For a Z-table you can create a secondary index; for a standard table, check whether an existing index can be used by reordering the conditions, before adding one.

WHERE

How to know rather than guess. ST05 traces every statement a program issues, with the execution count and duration. A SELECT inside a loop is unmistakable there: the same statement, ten thousand times.

ST05

What the interviewer is probing.1. "Why is SELECT inside a LOOP the cardinal sin?" Probing: the fundamental rule. Stalls: "It is slow." Moves up: each iteration is a database round trip, so a thousand rows is a thousand calls — use FOR ALL ENTRIES or a join instead.

2. "What must you check before FOR ALL ENTRIES?" Probing: the trap. Stalls: "Nothing." Moves up: that the driver table is not empty, or it reads the entire table — and remember it removes duplicates.

*3. "Why avoid SELECT ?"* Probing: the cost. Stalls: "Style." Moves up:* it transfers columns you do not need and prevents index-only access, so name the fields.

4. "What is code pushdown and why does it matter on HANA?" Probing: the modern approach. Stalls: "Nothing." Moves up: doing aggregation and joins in the database through CDS views or AMDP rather than looping in ABAP, which is where HANA's advantage is.