ABAP Performance Analysis: Finding and Fixing a Slow Program with ST05, SAT and SQL Monitor

When the "this report is too slow" complaint comes in, the first reflex is usually to read the code and fix a line that looks suspicious. Sometimes that works; more often, after half a day spent improving a loop, the runtime has barely moved, because the time wasn't being spent there but in a database read nobody looked at.

Performance work is not a guessing game; it is measurement. SAP provides mature tools for it, and all of them ship with the standard system. In this article I describe which tool answers which question, how to read the measurement and how to fix the most common bottlenecks.

Measure First, Then Change

To say whether a fix worked, you first need a baseline measurement. That measurement should meet three conditions:

• Realistic data volume: A program that takes seconds with a hundred records in development can take hours with a hundred thousand in production. Where possible, measure on a test system that is a recent copy of production.
• Repeatable conditions: The same selection screen values, the same user, ideally similar system load. Save the selection as a variant.
• The second run: On the first run, table buffers, the program buffer and database caches are cold. Record the first and second runs separately, and compare like with like.

Record the measurement in a short note. Later, this note will be the answer to "how long did it use to take?":

Program      : ZSD_ORDER_REPORT (variant: MONTHLY_1000)
System / data: QAS, production copy (copy date: ...)
Run          : 2nd run, foreground
Total time   : ...
DB time      : ...        CPU time: ...
SQL summary  : ... statement executions, ... records (ST05)

Which Tool Answers Which Question?

First Look: Where Did the Time Go?

Starting an analysis directly in ST05 or SAT is like setting off without looking at the map. First, use the statistical records in STAD to see how the total response time breaks down. Filter by user, transaction or program and time window, open the relevant run, and three values decide the next step:

• High database time: The problem is most likely on the SQL side; continue with ST05.
• High CPU time: The time is spent in ABAP code, very often in internal table operations; continue with SAT.
• The sum of both is far below the response time: The program is waiting for something: an RFC call, an external system, a lock, a free work process. Look for the cause of the wait, not for code optimisations.

Statistical records are kept for a limited time that depends on system settings. When the complaint is "the job ran far too long last night", the first thing to do is look in STAD before the records are gone.

SQL Tracing with ST05

ST05 records every statement a program sends to the database, along with its duration, the number of records returned and the ABAP line it was called from. The basic flow:

1. In ST05, select SQL Trace and activate it with a filter: user name and, if possible, transaction or program. An unfiltered trace collects everyone's statements on that server.
2. Run the program in another session.
3. Deactivate the trace as soon as the program finishes. Trace files are limited; a trace left running can overwrite the records you need.
4. Display the trace and switch from individual statements to the structure-identical statements summary.

Keep in mind that the trace is held on the application server where it was activated: on a system with several application servers, if the background job runs on a different server, the trace stays empty.

Reading the summary

The summary groups statements with the same structure into one row. Column names vary slightly between releases, but what you read is the same, and there are four typical patterns:

From the summary you can select a statement and use Explain to see its execution plan, or jump to the ABAP location to find the line it came from. Sorted by total time, the summary usually shows that most of the program's database time is concentrated in a handful of statements; start with the top one.

Measuring the ABAP Side with SAT

If CPU time dominates, the place to measure is the ABAP code. SAT (ABAP Runtime Analysis), the successor of SE30, shows how much time the program spends in which processing block and on which statement.

1. In SAT, enter the transaction, program or function module.
2. Choose a measurement variant. The default variant aggregates per call position and keeps the trace file small. To see the call hierarchy you need a variant without aggregation (aggregation: none); this grows the file quickly, so keep the measurement short.
3. Run the measurement. You can measure a dialog program directly, and capture another user's run or a background job with a scheduled measurement.
4. In the evaluation, sort the hit list by net time.

Gross time is the time of a method plus everything it calls; net time is the time spent inside that method alone. A method with high gross time only tells you "the problem is somewhere below me"; a line with high net time is the problem itself. Typical findings are keyless READ TABLE or LOOP ... WHERE on a large standard table, nested loops and tables sorted again and again inside a loop.

If you work in ADT, you can take the same measurement from Eclipse with the ABAP Profiler; the hit list, call tree and database accesses open in views linked to the source code. The approach is the same when a Fiori app or OData service is slow: because the request arrives over HTTP, you activate the measurement with a user filter and capture that request.

The Bottlenecks Seen Most Often in the Field

The examples below use classic SD tables because they exist in almost every system. The patterns are independent of the tables; in ABAP Cloud the same logic is built on released CDS views instead of tables.

1. SELECT inside a loop

This is the most common and most expensive pattern. The code is readable, fast with small data and slow in production:

" One database round trip per order: 5,000 orders = 5,000 SELECTs
SELECT vbeln, kunnr, netwr, waerk
  FROM vbak
  WHERE erdat IN @s_erdat
  INTO TABLE @DATA(orders).

LOOP AT orders INTO DATA(order).
  SELECT SINGLE name1 FROM kna1
    WHERE kunnr = @order-kunnr
    INTO @DATA(customer_name).
  " ... fill the output row
ENDLOOP.

Each SELECT SINGLE may be fast; the problem is that the network and database interface cost of each round trip is paid thousands of times. If the data is in the same database, the cleanest fix is a JOIN:

SELECT vbak~vbeln, vbak~kunnr, vbak~netwr, vbak~waerk,
       kna1~name1
  FROM vbak
  INNER JOIN kna1 ON kna1~kunnr = vbak~kunnr
  WHERE vbak~erdat IN @s_erdat
  INTO TABLE @DATA(orders).

2. The two traps of FOR ALL ENTRIES

If the driving data comes from another source (a BAPI result, a file, an earlier calculation), a JOIN isn't possible. In that case the keys are read in one go with FOR ALL ENTRIES, and the result goes into a hashed table for fast access inside the loop:

TYPES: BEGIN OF ty_customer,
         kunnr TYPE kunnr,
         name1 TYPE name1_gp,
       END OF ty_customer.
DATA customers TYPE HASHED TABLE OF ty_customer WITH UNIQUE KEY kunnr.

" Remove duplicates from the driver table
DATA(customer_keys) = orders.
SORT customer_keys BY kunnr.
DELETE ADJACENT DUPLICATES FROM customer_keys COMPARING kunnr.

" Empty driver table = WHERE clause ignored = whole table read
IF customer_keys IS NOT INITIAL.
  SELECT kunnr, name1
    FROM kna1
    FOR ALL ENTRIES IN @customer_keys
    WHERE kunnr = @customer_keys-kunnr
    INTO TABLE @customers.
ENDIF.

LOOP AT orders INTO DATA(order).
  ASSIGN customers[ kunnr = order-kunnr ] TO FIELD-SYMBOL(<customer>).
  IF sy-subrc = 0.
    " <customer>-name1 is available
  ENDIF.
ENDLOOP.

3. Data that is read and thrown away

The second big pattern is reading more columns and rows than needed from the database and doing the filtering and aggregation in ABAP:

" All columns and all items are moved to the application server
SELECT * FROM vbap
  WHERE vbeln IN @s_vbeln
  INTO TABLE @DATA(items).

" Filtering happens in ABAP, not in the database
DELETE items WHERE abgru IS NOT INITIAL.

" So does aggregation: summed per currency in a loop
LOOP AT items INTO DATA(item).
  ...
ENDLOOP.

The same result comes from a single statement with filtering and aggregation done in the database; only one row per currency reaches the application server:

SELECT waerk,
       SUM( netwr ) AS net_total,
       COUNT( * )   AS item_count
  FROM vbap
  WHERE vbeln IN @s_vbeln
    AND abgru = @space
  GROUP BY waerk
  INTO TABLE @DATA(totals).

The selection screen belongs here too. A report whose date range can be left empty will, one day, be run with an empty date. Making selective fields such as a date or an organisational unit mandatory on reports against large tables is the cheapest performance measure there is.

4. The wrong internal table type

Most lines with high net time in SAT are internal table lookups. A key lookup on a standard table is a full scan; multiplied by the outer loop, the cost grows fast:

" items: STANDARD TABLE without a key
LOOP AT headers INTO DATA(header).
  " The whole item table is scanned from start to end for each header
  LOOP AT items INTO DATA(item) WHERE vbeln = header-vbeln.
    " ...
  ENDLOOP.
ENDLOOP.

If the table still needs to be processed in its original order elsewhere, adding a secondary key is enough; there's no need to change its type:

TYPES: BEGIN OF ty_item,
         vbeln TYPE vbeln_va,
         posnr TYPE posnr_va,
         matnr TYPE matnr,
         netwr TYPE netwr_ap,
         waerk TYPE waerk,
       END OF ty_item.
TYPES ty_items TYPE STANDARD TABLE OF ty_item
               WITH EMPTY KEY
               WITH NON-UNIQUE SORTED KEY by_order COMPONENTS vbeln.
DATA items TYPE ty_items.

LOOP AT headers INTO DATA(header).
  LOOP AT items INTO DATA(item) USING KEY by_order WHERE vbeln = header-vbeln.
    " ...
  ENDLOOP.
ENDLOOP.

I cover table type choice in detail in the Clean ABAP guide (Turkish) and modern table expressions in the modern ABAP syntax article.

5. Reading the same data again and again

A statement with a high identical ratio in ST05 shows that the program keeps asking the database the same question. Very often the read is buried inside a method or function module called for every row, and changing the call structure isn't easy. In that case, putting the read behind a small cache class solves the problem without touching the callers:

CLASS lcl_material_texts DEFINITION.
  PUBLIC SECTION.
    METHODS get
      IMPORTING material      TYPE matnr
      RETURNING VALUE(result) TYPE maktx.
  PRIVATE SECTION.
    TYPES: BEGIN OF ty_text,
             matnr TYPE matnr,
             maktx TYPE maktx,
           END OF ty_text.
    DATA texts TYPE HASHED TABLE OF ty_text WITH UNIQUE KEY matnr.
ENDCLASS.

CLASS lcl_material_texts IMPLEMENTATION.
  METHOD get.
    ASSIGN texts[ matnr = material ] TO FIELD-SYMBOL(<text>).
    IF sy-subrc = 0.
      result = <text>-maktx.
      RETURN.
    ENDIF.

    SELECT SINGLE maktx FROM makt
      WHERE matnr = @material
        AND spras = @sy-langu
      INTO @result.

    " Remember materials that weren't found too; don't ask again
    INSERT VALUE #( matnr = material maktx = result ) INTO TABLE texts.
  ENDMETHOD.
ENDCLASS.

A cache has two costs: memory and freshness. In a background job working through millions of distinct keys, a cache can grow without limit; with frequently changing data, the program sees the old value for as long as it runs. It is safe for data that doesn't change within a run, such as master data texts.

For small tables that are read often and rarely changed (most customising tables), another option is table buffering in the technical settings in SE11. Buffers are synchronised across application servers with a delay, so tables that change frequently or need immediate consistency should not be buffered. Some SQL constructs bypass the buffer; confirm that the buffer is really used with the ST05 table buffer trace.

6. Writing row by row

The same N+1 problem exists on the write side. Instead of sending changes from an internal table to the database one by one, use an array operation:

" Row by row: one database round trip per record
LOOP AT changes INTO DATA(change).
  UPDATE zsd_delivery_log FROM @change.
ENDLOOP.

" In bulk: a single statement
UPDATE zsd_delivery_log FROM TABLE @changes.

7. Volumes that don't fit in memory

If a background job has to process hundreds of thousands of rows, loading them all into an internal table at once strains memory and makes the first row wait until the last one has been read. Split the read into packages:

DATA item_package TYPE STANDARD TABLE OF ty_item WITH EMPTY KEY.

SELECT vbeln, posnr, matnr, netwr, waerk
  FROM vbap
  WHERE erdat IN @s_erdat
  INTO TABLE @item_package
  PACKAGE SIZE 10000.

  process_items( item_package ).
  " No COMMIT WORK here: it closes the database cursor and the program dumps
ENDSELECT.

If you need to write and commit inside the package loop, use OPEN CURSOR WITH HOLD with FETCH NEXT CURSOR ... PACKAGE SIZE. Parallel processing (spreading work packages across several work processes) is the next step, not the first: running inefficient SQL in parallel doesn't solve the problem, it just puts more concurrent load on the database.

What Changed with HANA, and What Didn't?

A sentence often heard in teams moving to S/4HANA: "We have HANA now, performance won't be a problem." It is partly true; HANA makes individual queries much faster. But some rules don't change, and some become even more important:

• A database round trip is still expensive. The network and interface cost of every statement is paid however fast the query is. SELECT inside a loop is the most common bottleneck on HANA too.
• SELECT * is more expensive. In column store each column is stored separately; every unnecessary column is read and assembled separately. Selecting only the fields you need matters more than ever.
• Move calculation to the database. Doing filtering, aggregation and joins in the database with ABAP SQL or CDS (code pushdown) uses HANA's real strength. For complex logic that ABAP SQL or CDS can't express there is AMDP; because it is harder to debug and test, treat it as the last option.
• Indexes are needed less, not never. Column-store tables need secondary indexes far less often than classic databases. Still, a non-selective WHERE clause stays slow on a large table.

You'll find report examples that aggregate in the database with CDS in the ACDOCA article, and scanning custom code with ATC during a migration in the S/4HANA migration guide.

Measuring in Production: SQL Monitor

Some problems only show up in production: data volume, concurrent users and month-end load can't be reproduced on a test system. That is where SQL Monitor (SQLM) comes in. SQL Monitor is designed to run in production with low overhead and collects, for every SQL statement, the execution count, total and average time and the records returned, together with the program line and the entry point (transaction, report, RFC, URL, background job) it came from.

• Activate SQLM with an end date and keep it running long enough to cover the period that matters; for month-end problems, pick a range that includes month end.
• Sort the results by total time. A single slow statement and a fast statement executed millions of times can create the same overall load.
• SWLT (SQL Performance Tuning Worklist) combines SQL Monitor data with static code check findings and answers "which custom code should I fix first?" as a prioritised list.

Where to Look First, by Symptom

Why external calls slow down the screen, and how to move them into a separate step, is covered in the SAP integration scenarios article.

After the Fix: Verification

A program that is fast but produces wrong results is worse than one that is slow but correct. Every performance fix should close with two questions:

To stop the same patterns from coming back, add the ATC (Code Inspector) performance checks to your development process. Patterns such as database access inside loops, reads that bypass the buffer and problematic SELECT * usage can be caught statically. Static checks don't replace measurement, but they keep known mistakes out of production.

Common Mistakes

Conclusion

ABAP performance analysis comes down to a few questions asked in the right order: Where does the time go (STAD)? If it's in the database, which statement (ST05)? If it's in ABAP, which line (SAT)? Does the problem only exist in production (SQL Monitor)? Once these are answered, the fix is often surprisingly small: a JOIN, a hashed table, a secondary key, an aggregation moved to the database.

What really makes the difference isn't the fix itself but the measurement before and after it. You can find the scope I offer for slow reports, long-running background jobs and custom code adaptation after an S/4HANA migration on the SAP development services page.