ABAP Developmentintermediate

Performance Part 4: SELECT Inside LOOP — The Classic Performance Killer

Key takeaway

Recognise and fix SELECT inside LOOP: why thousands of single reads are slow, how it appears in ST05, rewriting with one set-based read (FOR ALL ENTRIES or JOIN) plus a sorted/hashed lookup table, handling SELECT SINGLE in loops, and hidden cases inside function modules and methods called per row.

Explanation

📌 KEY POINTS
✅ A SELECT (or SELECT SINGLE) inside a LOOP executes once per row — 50,000 rows = 50,000 database round trips.
✅ Fix pattern: collect keys → one set-based read before the loop (FOR ALL ENTRIES or JOIN) → store in a sorted/hashed table → READ TABLE inside the loop.
✅ ST05 shows it as one structure-identical statement with a very high execution count.
⚠️ Hidden versions: a function module, method or FORM called inside the loop that itself does a SELECT — the code looks clean but behaves the same.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 3 found top offenders. The most frequent one is a SELECT in a loop.

━━━━━━━━━━━━━━━━━━━━
▶ 1. WHY IT IS SLOW
Each execution has fixed overhead (network, parsing/cursor, DB call) — even a 0.5 ms read costs 25 seconds for 50,000 rows, and real reads are often slower.

━━━━━━━━━━━━━━━━━━━━
▶ 2. RECOGNISING IT

Code: SELECT inside LOOP/DO/WHILE or SELECT…ENDSELECT with nested SELECT.
ST05: statement with executions ≈ number of rows; often also value-identical (same key read repeatedly).
SAT: many calls of the same FM/method with DB time.

━━━━━━━━━━━━━━━━━━━━
▶ 3. FIX PATTERN

Collect the keys from the driving table (SORT + DELETE ADJACENT DUPLICATES).
One read: FOR ALL ENTRIES (Part 5) or JOIN (Part 7).
Put the result into a HASHED (unique key) or SORTED table.
In the loop: READ TABLE … WITH TABLE KEY (or BINARY SEARCH on sorted standard table).

━━━━━━━━━━━━━━━━━━━━
▶ 4. SELECT SINGLE IN LOOPS

Same problem; plus often repeated for the same key (value-identical) → at least buffer results in a hashed table; better read all upfront.
Buffered tables (customizing) are cheap — the problem mainly affects application tables.

━━━━━━━━━━━━━━━━━━━━
▶ 5. HIDDEN SELECTS

FMs like reading texts or master data per row; BAPIs called per row; methods in loops.
Look for "mass" versions of FMs or read data upfront.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Run the code example's BAD version with ST05 and note executions.
Rewrite to the GOOD version and trace again.
Compare runtime and results.

✅ CHECKPOINT: you can find and eliminate SELECT inside LOOP patterns.

Code example

ABAP Code

REPORT zperf_select_in_loop. TYPES: BEGIN OF ty_item,         vbeln TYPE vbeln_va,         posnr TYPE posnr_va,         matnr TYPE matnr,       END OF ty_item,       BEGIN OF ty_mat,         matnr TYPE matnr,         matkl TYPE matkl,       END OF ty_mat. DATA: lt_items TYPE STANDARD TABLE OF ty_item,      ls_item  TYPE ty_item,      lt_keys  TYPE STANDARD TABLE OF ty_item,      lt_mat   TYPE HASHED TABLE OF ty_mat WITH UNIQUE KEY matnr,      ls_mat   TYPE ty_mat,      lv_matkl TYPE matkl. SELECT-OPTIONS s_vbeln FOR ls_item-vbeln. START-OF-SELECTION.  SELECT vbeln posnr matnr FROM vbap INTO TABLE lt_items WHERE vbeln IN s_vbeln. * ===== BAD: one database call per item =====*  LOOP AT lt_items INTO ls_item.*    SELECT SINGLE matkl FROM mara INTO lv_matkl WHERE matnr = ls_item-matnr.*  ENDLOOP. * ===== GOOD Step 1: distinct keys =====  lt_keys = lt_items.  SORT lt_keys BY matnr.  DELETE ADJACENT DUPLICATES FROM lt_keys COMPARING matnr. * ===== GOOD Step 2: one set-based read (check for empty table!) =====  IF lt_keys IS NOT INITIAL.    SELECT matnr matkl FROM mara      INTO TABLE lt_mat      FOR ALL ENTRIES IN lt_keys      WHERE matnr = lt_keys-matnr.  ENDIF. * ===== GOOD Step 3: fast lookup in the loop =====  LOOP AT lt_items INTO ls_item.    CLEAR lv_matkl.    READ TABLE lt_mat INTO ls_mat WITH TABLE KEY matnr = ls_item-matnr.    IF sy-subrc = 0.      lv_matkl = ls_mat-matkl.    ENDIF.    WRITE: / ls_item-vbeln, ls_item-posnr, ls_item-matnr, lv_matkl.  ENDLOOP.

Real project scenario

An order backlog report read customer names, material descriptions and plant data with three SELECT SINGLEs per item. With 80,000 items it ran 45 minutes; ST05 showed 240,000 executions.
The rewrite read all three datasets upfront with FOR ALL ENTRIES into hashed tables. Runtime dropped to 2 minutes with identical output.

Common mistakes

SELECT/SELECT SINGLE in loops.
FMs with SELECTs called per row.
FOR ALL ENTRIES without empty-table check.
Lookups in standard tables without binary search.
Not removing duplicate keys before reading.

Best practices

Read set-based before the loop.
Hashed/sorted lookup tables.
Distinct keys first.
Look for mass-enabled FMs/APIs.
Verify with ST05 execution counts.

Interview angle

Questions: "Why is SELECT in a loop bad?", "How do you fix it?", "How do you detect it?"
Strong answer: one round trip per row; ST05 high executions/identical statements; fix by collecting distinct keys, one FOR ALL ENTRIES/JOIN read, hashed/sorted table, READ TABLE WITH TABLE KEY in loop; check empty driver table; watch hidden selects in FMs/methods.

Practice this lesson

Quick check

1. ST05 shows one SELECT SINGLE on MARA executed 60,000 times. Best fix?

2. What is a common problem with SELECT inside LOOP?

3. Which is a classic ABAP performance anti-pattern?

Interview questions

1. A sales order enhancement added one MARA SELECT per item, and order saves became slow for large orders. How do you fix it?

Answer it in your own words first, then compare.

2. Why is SELECT inside LOOP considered bad for performance?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.