The ST05 Playbook: Killing N+1 Queries and Full Table Scans in ABAP

Series: ABAP Performance Tuning in S/4HANA | Part 2 of 7 | Author: Manish Khanna

A report that fetches 50,000 sales orders runs for 8 minutes. You open ST05 and see the same table — VBAP — appearing 49,847 times in the trace. One SELECT per order header. That’s the N+1 problem, and it’s the most common performance defect in ABAP codebases. ST05 finds it in under five minutes.

This post walks through ST05 step by step, then covers the four patterns that account for 90% of database performance problems.

Running an ST05 Trace: Step by Step

Step 1 — Open ST05

Enter transaction ST05. The main screen has four trace types. For almost all performance work, select SQL Trace.

Before activating, click Trace Options (wrench icon) and configure:

Option Setting✅Restrict to own user✅Always enable in shared systemsDuration limit60–120 seconds for interactive programsMaximum records100,000 (sufficient for most programs)Trace RFC callsEnable if RFC performance is suspect

Step 2 — Activate, Run, Deactivate

Click Activate Trace (F5). The button turns red.

⚠️Warning: Do not navigate away from ST05 while the trace is active. Open a separate SAP GUI session to run the program you’re tracing.

In the new session: execute the transaction or program. Do only what you need to measure — don’t browse around. Return to ST05 and click Deactivate Trace (F6).

Step 3 — Display and Filter

Click Display Trace (F7). Apply these filters before hitting Execute:

Duration ≥ 10 ms — filters out fast, trivially cached callsStatement type: SELECT — start here; add INSERT/UPDATE later if needed

Step 4 — Read the Output

Each row is one database call. The columns that matter:

Column What to Look ForDurationTime in microseconds — high values firstObjectThe database tableOpREOPEN = new SELECT call; FETCH = row retrievalRecRows returned — high counts need explanationRC1403 = no data found (harmless); anything else investigate

Double-click any row to see the full WHERE clause, which index was used, and the Explain Plan button.

The Four Patterns That Matter

Pattern 1 — SELECT in a Loop (N+1 Problem)

How to spot it: The same table appears hundreds or thousands of times in the trace. Each call returns 0 or 1 rows. You’ll see repeated REOPEN operations.

This is always catastrophic at scale. A program that loops 100 times in dev loops 100,000 times in production.

” ❌ Bad: SELECT inside LOOP — 1 DB call per order
LOOP AT lt_orders INTO ls_order.
SELECT SINGLE * FROM vbap INTO ls_item
WHERE vbeln = ls_order-vbeln.
” process ls_item
ENDLOOP.

⚠️Warning: Always check IS NOT INITIAL before FOR ALL ENTRIES. An empty driver table causes a full table scan — you’ve just replaced one problem with a worse one.

Pattern 2 — Full Table Scan

How to spot it: The Rec column shows thousands or millions of rows returned. Duration is high. The Explain Plan shows FULL scan — no index used.

Click the row → Explain button. If you see no index, you need one.

” ❌ Bad: ERDAT is not a leading key field — no index
SELECT * FROM vbak WHERE erdat = @sy-datum.

” ✅ Good: filter on indexed leading key fields first
SELECT vbeln, erdat, auart
FROM vbak
INTO TABLE @DATA(lt_orders)
WHERE mandt = @sy-mandt
AND vkorg = @lv_vkorg
AND erdat = @sy-datum.

Use SE11 to check existing secondary indexes before creating new ones. Adding a redundant index on a write-heavy table costs more than it saves.

Pattern 3 — Large Result Sets with Late Filtering

How to spot it: Rec column shows 50,000 rows returned. The program uses 200 of them. The filter happens in ABAP, not the database.

Every row crosses the DB-to-ABAP boundary. For large tables, this is enormous — especially on HANA where network I/O between app server and DB is still real.

” ❌ Bad: fetch 50,000 rows, delete 49,800 in ABAP
SELECT * FROM vbak INTO TABLE @DATA(lt_all).
DELETE lt_all WHERE auart <> ‘TA’.

” ✅ Good: filter at the database — return only what you need
SELECT vbeln, auart, erdat
FROM vbak
INTO TABLE @DATA(lt_filtered)
WHERE auart = ‘TA’
AND erdat >= @lv_from_date.

💡Tip: Also check for SELECT * fetching all 80 columns when you only need 3. Specify your field list explicitly — every column you don’t need is data transfer you’re paying for.

Pattern 4 — Unnecessary Buffer Bypass

How to spot it: A table that is set as buffered in SE11 (Technical Settings → Buffering) appears in the SQL trace. It should never be there — buffered tables are served from the ABAP shared memory buffer, not the database.

Common causes:

SELECT … BYPASSING BUFFER in the codeClient-independent table accessed without the client field in WHERETable type mismatch causing implicit bypass

Fix: Remove BYPASSING BUFFER unless you have a documented reason (parallel processes writing to the table mid-read). Ensure client field appears in WHERE for client-dependent tables.

Reading the Summary View

After looking at individual calls, click Summarize in the toolbar. This groups all calls by table and shows:

Total time per tableTotal call count per tableAverage duration per call

If one table accounts for 70% of your total DB time — that’s your target. Everything else is noise for now.

When ST05 Shows Nothing Expensive

If your trace runs clean — fast calls, low row counts, indexes used — the database is not the problem. Move to SAT (Part 3). The bottleneck is in ABAP logic, not SQL.

💡Tip: For S/4HANA landscapes with CDS views, also check the generated SQL in ST05. A CDS view with 7 stacked layers can produce a 400-line SQL statement that the optimizer can’t handle efficiently. See Part 5 for CDS-specific analysis.

Key Takeaways

ST05 is your first tool — always. Database problems have the highest ROI fix.N+1 SELECT (same table, hundreds of REOPEN) is the most common critical findingFOR ALL ENTRIES requires an IS NOT INITIAL guard — no exceptionsFull table scans need secondary indexes — check SE11 first before creating new onesFetch only the columns and rows you need — push filters to the database

Next in this series: SAT + ST12: Profiling ABAP CPU and Memory the Right Way — finding CPU hotspots with SAT and tracking down OO memory leaks with ST12.

Series Home Page

The ST05 Playbook: Killing N+1 Queries and Full Table Scans in ABAPSeries: ABAP Performance Tuning in S/4HANA | Part 2 of 7 | Author: Manish KhannaA report that fetches 50,000 sales orders runs for 8 minutes. You open ST05 and see the same table — VBAP — appearing 49,847 times in the trace. One SELECT per order header. That’s the N+1 problem, and it’s the most common performance defect in ABAP codebases. ST05 finds it in under five minutes.This post walks through ST05 step by step, then covers the four patterns that account for 90% of database performance problems. Running an ST05 Trace: Step by StepStep 1 — Open ST05Enter transaction ST05. The main screen has four trace types. For almost all performance work, select SQL Trace.Before activating, click Trace Options (wrench icon) and configure: Option Setting✅Restrict to own user✅Always enable in shared systemsDuration limit60–120 seconds for interactive programsMaximum records100,000 (sufficient for most programs)Trace RFC callsEnable if RFC performance is suspectStep 2 — Activate, Run, DeactivateClick Activate Trace (F5). The button turns red.⚠️Warning: Do not navigate away from ST05 while the trace is active. Open a separate SAP GUI session to run the program you’re tracing.In the new session: execute the transaction or program. Do only what you need to measure — don’t browse around. Return to ST05 and click Deactivate Trace (F6).Step 3 — Display and FilterClick Display Trace (F7). Apply these filters before hitting Execute:Duration ≥ 10 ms — filters out fast, trivially cached callsStatement type: SELECT — start here; add INSERT/UPDATE later if neededStep 4 — Read the OutputEach row is one database call. The columns that matter: Column What to Look ForDurationTime in microseconds — high values firstObjectThe database tableOpREOPEN = new SELECT call; FETCH = row retrievalRecRows returned — high counts need explanationRC1403 = no data found (harmless); anything else investigateDouble-click any row to see the full WHERE clause, which index was used, and the Explain Plan button.The Four Patterns That MatterPattern 1 — SELECT in a Loop (N+1 Problem)How to spot it: The same table appears hundreds or thousands of times in the trace. Each call returns 0 or 1 rows. You’ll see repeated REOPEN operations.This is always catastrophic at scale. A program that loops 100 times in dev loops 100,000 times in production.” ❌ Bad: SELECT inside LOOP — 1 DB call per order
LOOP AT lt_orders INTO ls_order.
SELECT SINGLE * FROM vbap INTO ls_item
WHERE vbeln = ls_order-vbeln.
” process ls_item
ENDLOOP.

” ✅ Good: single SELECT fetches everything
IF lt_orders IS NOT INITIAL.
SELECT vbeln, posnr, matnr, kwmeng
FROM vbap
INTO TABLE @DATA(lt_items)
FOR ALL ENTRIES IN @lt_orders
WHERE vbeln = @lt_orders-vbeln.
ENDIF.⚠️Warning: Always check IS NOT INITIAL before FOR ALL ENTRIES. An empty driver table causes a full table scan — you’ve just replaced one problem with a worse one.Pattern 2 — Full Table ScanHow to spot it: The Rec column shows thousands or millions of rows returned. Duration is high. The Explain Plan shows FULL scan — no index used.Click the row → Explain button. If you see no index, you need one.” ❌ Bad: ERDAT is not a leading key field — no index
SELECT * FROM vbak WHERE erdat = @sy-datum.

” ✅ Good: filter on indexed leading key fields first
SELECT vbeln, erdat, auart
FROM vbak
INTO TABLE @DATA(lt_orders)
WHERE mandt = @sy-mandt
AND vkorg = @lv_vkorg
AND erdat = @sy-datum.Use SE11 to check existing secondary indexes before creating new ones. Adding a redundant index on a write-heavy table costs more than it saves.Pattern 3 — Large Result Sets with Late FilteringHow to spot it: Rec column shows 50,000 rows returned. The program uses 200 of them. The filter happens in ABAP, not the database.Every row crosses the DB-to-ABAP boundary. For large tables, this is enormous — especially on HANA where network I/O between app server and DB is still real.” ❌ Bad: fetch 50,000 rows, delete 49,800 in ABAP
SELECT * FROM vbak INTO TABLE @DATA(lt_all).
DELETE lt_all WHERE auart <> ‘TA’.

” ✅ Good: filter at the database — return only what you need
SELECT vbeln, auart, erdat
FROM vbak
INTO TABLE @DATA(lt_filtered)
WHERE auart = ‘TA’
AND erdat >= @lv_from_date.💡Tip: Also check for SELECT * fetching all 80 columns when you only need 3. Specify your field list explicitly — every column you don’t need is data transfer you’re paying for.Pattern 4 — Unnecessary Buffer BypassHow to spot it: A table that is set as buffered in SE11 (Technical Settings → Buffering) appears in the SQL trace. It should never be there — buffered tables are served from the ABAP shared memory buffer, not the database.Common causes:SELECT … BYPASSING BUFFER in the codeClient-independent table accessed without the client field in WHERETable type mismatch causing implicit bypassFix: Remove BYPASSING BUFFER unless you have a documented reason (parallel processes writing to the table mid-read). Ensure client field appears in WHERE for client-dependent tables.Reading the Summary ViewAfter looking at individual calls, click Summarize in the toolbar. This groups all calls by table and shows:Total time per tableTotal call count per tableAverage duration per callIf one table accounts for 70% of your total DB time — that’s your target. Everything else is noise for now.When ST05 Shows Nothing ExpensiveIf your trace runs clean — fast calls, low row counts, indexes used — the database is not the problem. Move to SAT (Part 3). The bottleneck is in ABAP logic, not SQL.💡Tip: For S/4HANA landscapes with CDS views, also check the generated SQL in ST05. A CDS view with 7 stacked layers can produce a 400-line SQL statement that the optimizer can’t handle efficiently. See Part 5 for CDS-specific analysis.Key TakeawaysST05 is your first tool — always. Database problems have the highest ROI fix.N+1 SELECT (same table, hundreds of REOPEN) is the most common critical findingFOR ALL ENTRIES requires an IS NOT INITIAL guard — no exceptionsFull table scans need secondary indexes — check SE11 first before creating new onesFetch only the columns and rows you need — push filters to the databaseNext in this series: SAT + ST12: Profiling ABAP CPU and Memory the Right Way — finding CPU hotspots with SAT and tracking down OO memory leaks with ST12.Series Home Page Read More Technology Blog Posts by Members articles

#SAP

#SAPTechnologyblog