Internal Tables Part 9: Nested LOOP Optimization and Parallel Cursor

📌 KEY POINTS
✅ LOOP inside LOOP over two standard tables = rows1 × rows2 comparisons — minutes become hours.
✅ Fix 1: inner table SORTED by the join key → LOOP … WHERE is optimized.
✅ Fix 2: one matching row per key → HASHED inner table + READ TABLE.
✅ Fix 3 (classic): parallel cursor — find the start with BINARY SEARCH, loop FROM that index, EXIT when the key changes.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 8 covered loop patterns. When two tables are combined in memory (headers and items, flights and bookings), the nested loop is where performance is won or lost.

━━━━━━━━━━━━━━━━━━━━
▶ 1. THE PROBLEM
LOOP AT gt_flight INTO gs_flight. " 10,000 rows
LOOP AT gt_book INTO gs_book WHERE carrid = gs_flight-carrid AND connid = gs_flight-connid AND fldate = gs_flight-fldate. " 500,000 rows

ENDLOOP.

ENDLOOP.

With standard tables, the inner WHERE scans all 500,000 rows for each of the 10,000 flights: 5 billion comparisons.

━━━━━━━━━━━━━━━━━━━━
▶ 2. FIX 1 — SORTED INNER TABLE
Declare gt_book as SORTED TABLE … WITH NON-UNIQUE KEY carrid connid fldate. The inner LOOP WHERE on the leading key fields now uses binary search to find the group — typically the simplest, cleanest fix.

━━━━━━━━━━━━━━━━━━━━
▶ 3. FIX 2 — HASHED LOOKUP
If the inner table has at most one match per key (e.g. flight → airline name), use a HASHED table with UNIQUE KEY and READ TABLE … WITH TABLE KEY instead of an inner loop.

━━━━━━━━━━━━━━━━━━━━
▶ 4. FIX 3 — PARALLEL CURSOR (CLASSIC STANDARD TABLES)
When you must keep standard tables (existing code):

• SORT both tables by the join key.
• For each outer row: READ TABLE inner WITH KEY … BINARY SEARCH TRANSPORTING NO FIELDS → sy-tabix = first matching row.
• LOOP AT inner FROM lv_index; EXIT when the key differs.

Each inner row is visited only for its own group.

━━━━━━━━━━━━━━━━━━━━
▶ 5. BETTER STILL: THE DATABASE
If both tables come from the database, a JOIN or CDS view often avoids the in-memory join entirely (Open SQL topic).

━━━━━━━━━━━━━━━━━━━━
▶ 6. MEASURE
Use SAT (runtime analysis) or GET RUN TIME with realistic volumes. Small test data hides nested-loop problems completely.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example with larger UP TO values: compare the nested standard loop, the sorted-table loop and the parallel cursor. The runtime difference grows quadratically.

✅ CHECKPOINT: you never leave a nested loop over two large standard tables in your code.

A freight settlement job matched 40,000 shipments against 1.2 million cost lines with a nested loop over standard tables. It ran for nine hours and was often cancelled by the batch window.
Changing the cost table to a sorted table keyed by shipment number (inner LOOP WHERE) brought runtime down to under five minutes. In another older program that could not be restructured, the parallel cursor technique achieved a similar improvement.

• Nested loops over two large standard tables.
• Parallel cursor without sorting both tables by the same key.
• Forgetting EXIT when the key changes (loops to the end).
• Testing only with small data.
• Doing in-memory joins that the database could do.

• Sorted inner tables with LOOP WHERE on leading key fields.
• Hashed tables for one-to-one lookups.
• Parallel cursor for existing standard-table code.
• Push joins to the database where possible.
• Measure with SAT and realistic volumes.

Questions: "How do you optimize nested loops?", "Explain the parallel cursor technique.", "Why does a sorted inner table help?"
Strong answer: nested loops over standard tables cost rows × rows; use a sorted inner table so LOOP WHERE on leading key fields uses binary search, hashed tables for 1:1 lookups, or the parallel cursor: sort both tables, find the first match with READ … BINARY SEARCH, loop FROM sy-tabix and EXIT when the key changes; better, join in the database.