SELECT Inside LOOP: The Classic Performance Killer

Explanation

SELECT inside LOOP is one of the most common ABAP performance problems. If the loop has 50,000 records, the SELECT may run 50,000 times. Even if each SELECT is fast, the total database round-trips become expensive. The better pattern is to collect unique keys, fetch required data once using FOR ALL ENTRIES or JOIN, then use a hashed or sorted internal table for lookup. This pattern is common in SD, MM, FI and logistics reports.

Code example

* Bad pattern:* This SELECT runs once for every billing item. LOOP AT lt_vbrp INTO DATA(ls_vbrp). SELECT SINGLE name1 FROM kna1 INTO @DATA(lv_name1) WHERE kunnr = @ls_vbrp-kunnr.ENDLOOP. * Better pattern:* Step 1: Collect unique customers.DATA lt_kunnr TYPE SORTED TABLE OF kunnr WITH UNIQUE KEY table_line. LOOP AT lt_vbrp INTO ls_vbrp. INSERT ls_vbrp-kunnr INTO TABLE lt_kunnr.ENDLOOP. * Step 2: Read customer data once.IF lt_kunnr IS NOT INITIAL. SELECT kunnr, name1 FROM kna1 INTO TABLE @DATA(lt_kna1) FOR ALL ENTRIES IN @lt_kunnr WHERE kunnr = @lt_kunnr-table_line.ENDIF. * Step 3: Use hashed table for fast lookup.DATA lt_kna1_hash TYPE HASHED TABLE OF kna1 WITH UNIQUE KEY kunnr.lt_kna1_hash = CORRESPONDING #( lt_kna1 ).

Real project scenario

A monthly billing report read customer data using SELECT SINGLE KNA1 inside a VBRP loop. ST05 showed 2.1 million database calls. After collecting unique KUNNR values and reading KNA1 once, runtime dropped from 3 hours to 9 minutes.

Common mistakes

- Using SELECT SINGLE inside large loops. - Not collecting unique driver keys. - Using standard table lookup after bulk select. - Not checking driver table before FOR ALL ENTRIES.

Best practices

- Collect unique keys first. - Fetch data once. - Use hashed lookup for exact key access. - Measure before and after with ST05.

Interview angle

Interviewers often ask how to optimize SELECT inside LOOP. The expected answer is bulk fetch plus internal table lookup.