ABAPintermediate
Open SQL & Database Performance in ABAP
Writing efficient Open SQL statements against the SAP database layer and avoiding classic ABAP performance anti-patterns.
Database access is where the majority of real-world ABAP performance problems live, and interviewers lean on this topic heavily because a candidate who can spot a SELECT inside a LOOP will save a client real money and downtime, whereas one who can't will eventually bring a production system to its knees with a report that times out.
Selecting inside a loop is like walking to the store separately for every single ingredient in a recipe; a joined or FOR ALL ENTRIES query is making one shopping list and buying everything in a single trip.
Key Concepts
1
Modern Open SQL (7.40+) supports inline declarations, JOINs, CASE expressions, and aggregate functions directly in the SELECT, which lets much more filtering and computation happen on the database rather than in ABAP - exactly where it belongs, since the database engine (especially HANA) is vastly more efficient at set-based operations than row-by-row ABAP loops. The classic anti-pattern interviewers probe for is SELECT ... FROM ... INTO ... WHERE key = itab-field nested inside LOOP AT itab, which turns into one database round trip per row; the fix is either a JOIN, a single SELECT ... FOR ALL ENTRIES IN itab, or (on HANA) pushing the logic into a CDS view.
SELECT ... FROM ... INTO ... WHERE key = itab-fieldLOOP AT itabSELECT ... FOR ALL ENTRIES IN itab
2
FOR ALL ENTRIES deserves special mention: it implicitly adds DISTINCT-like deduplication behavior at the database level and silently returns all rows if the driver table is empty unless guarded with an IF itab IS NOT INITIAL check first - a very commonly cited gotcha. Candidates should also know SELECT SINGLE for one-row fetches, the difference between client-dependent and client-independent tables, and how secondary indexes on Z-tables can turn a full table scan into an index range scan.
FOR ALL ENTRIESDISTINCTIF itab IS NOT INITIALSELECT SINGLE
3
On S/4HANA, a senior-level answer brings up code pushdown - moving business logic into CDS views or AMDP (ABAP Managed Database Procedures) so heavy aggregation runs inside HANA instead of pulling millions of rows into the ABAP application server just to sum them there, which is both a performance and a licensing/memory consideration.