Open SQL — writing fast, HANA-friendly queries

Explanation

Open SQL is the abstraction over the database you should use for 99% of reads and writes. The trap is that syntactically correct code can still be catastrophically slow. Ten rules cover most of the tuning surface: 1. **Select only what you need.** Never SELECT *. Column-store databases like HANA read one column at a time — extra columns are wasted I/O. 2. **Use WHERE on indexed fields.** Check DB02 / SE11 indexes; add a secondary index only after profiling with ST05. 3. **Never SELECT inside LOOP.** Replace with JOIN or FOR ALL ENTRIES; N round-trips is the #1 performance killer in ABAP. 4. **Guard FOR ALL ENTRIES.** Empty driver = WHERE dropped = full-table read. Also SORT + DELETE ADJACENT DUPLICATES on the driver. 5. **Prefer aggregates on the DB.** SUM / COUNT / MAX in SQL, not in ABAP after the fact. 6. **Use array operations.** INSERT / UPDATE / MODIFY ... FROM TABLE is far faster than row-by-row. 7. **Package processing for big volumes.** Use SELECT ... PACKAGE SIZE, cursors, or DB commits every N records to bound memory. 8. **Push logic into CDS on HANA.** Joins, aggregations, and CASE belong in CDS — the DB does them in-memory columnar. 9. **Avoid ORDER BY unless you truly need it.** Sorting on the DB or in ABAP is a cost. 10. **Use SELECT SINGLE with a fully-qualified key**, otherwise you may get an arbitrary row.

Code example

" 1) Bad: SELECT inside LOOPLOOP AT lt_orders INTO DATA(ls_o).  SELECT SINGLE name1 FROM kna1 INTO ls_o-name1 WHERE kunnr = ls_o-kunnr.ENDLOOP. " 2) Good: batch read with FAE (properly guarded)IF lt_orders IS NOT INITIAL.  DATA lt_key TYPE STANDARD TABLE OF kunnr.  lt_key = VALUE #( FOR o IN lt_orders ( o-kunnr ) ).  SORT lt_key.  DELETE ADJACENT DUPLICATES FROM lt_key.   SELECT kunnr, name1 FROM kna1    FOR ALL ENTRIES IN @lt_key    WHERE kunnr = @lt_key-table_line    INTO TABLE @DATA(lt_cust).ENDIF. " 3) Better on HANA: JOIN + column listSELECT o~vbeln, o~kunnr, c~name1  FROM vbak AS o  INNER JOIN kna1 AS c ON c~kunnr = o~kunnr  INTO TABLE @DATA(lt_result)  WHERE o~erdat >= @lv_from. " 4) Package cursor for big volumesOPEN CURSOR WITH HOLD @DATA(lv_cursor) FOR  SELECT * FROM bkpf WHERE bukrs = @lv_bukrs.DO.  FETCH NEXT CURSOR @lv_cursor INTO TABLE @DATA(lt_pkg) PACKAGE SIZE 10000.  IF sy-subrc <> 0. EXIT. ENDIF.  " process lt_pkg ...ENDDO.CLOSE CURSOR @lv_cursor.

Real project scenario

A migration report on S/4HANA read 12M BKPF rows into memory and ran out of work-process memory nightly. The fix was two lines: switch to a cursor with PACKAGE SIZE 5000 and add a WHERE on GJAHR. Runtime went from OOM crash to 8 minutes. No indexes changed, no CDS added — just Open SQL discipline.

Common mistakes

- SELECT * on wide tables (BSEG, BKPF, MSEG) — kills HANA. - FOR ALL ENTRIES on an unguarded / unsorted driver. - Forgetting that FAE de-duplicates *per package*, so DISTINCT isn't guaranteed for the whole set. - COMMIT WORK inside a LOOP without package logic (kills logs, locks). - SELECT SINGLE without full primary key — returns an arbitrary row silently. - Using ORDER BY primary key expecting index optimisation on HANA (column store doesn't care about row order).

Best practices

- Always specify a field list, never SELECT *. - Guard every FOR ALL ENTRIES with IS NOT INITIAL + SORT + DELETE ADJACENT DUPLICATES. - Prefer JOIN over FAE on HANA when the sets are large. - Use aggregates and CASE on the DB. - Use PACKAGE SIZE for anything > 1M rows. - Measure with ST05 before and after every change. - Move heavy read logic into a CDS view when it will be reused.

Interview angle

Senior interviewers probe your instincts: "You wrote a JOIN and it's still slow — what do you check?" They want ST05, execution plan, buffering settings, whether the JOIN key is indexed, and whether the code could move into a CDS view. Mentioning code pushdown, ATC checks, and CDS unlocks the mid/senior band.