Open SQL and Performance Interview Questions
SELECT, joins, host variables, buffering, indexes, traces and practical optimization scenarios.
Interview strategy: never answer a performance question with “this syntax is always faster.” Explain data volume, selectivity, database execution, network transfer and measurement. First make the result correct, then measure, then optimize.
Quick visual: the efficient query path
Selective WHERE conditions
Avoid unnecessary columns
JOIN and aggregation
ST05, SAT, SQLM and ATC
Core interview questions with answers
1. What is ABAP SQL (Open SQL)?
ABAP SQL is the database-independent SQL interface integrated into ABAP. It works with ABAP Dictionary entities and ABAP data objects, supports automatic type conversion and provides platform services such as client handling where applicable. Modern documentation uses the term ABAP SQL; interviewers may still say Open SQL.
2. Why do modern ABAP SQL statements use @?
The escape character @ identifies ABAP host variables and host expressions inside an SQL statement. It clearly separates ABAP-side values from database columns and is required with strict modern syntax in relevant positions.
3. Why should SELECT * usually be avoided?
It transfers every column even when only a few are needed. That increases memory, network volume and coupling to the table definition. Select the required fields explicitly. An exception can be a genuinely complete row with a justified access path; measure rather than repeating a rule blindly.
4. What makes a WHERE condition efficient?
Use selective predicates, compatible data types and conditions that can be evaluated by the database. Restrict rows as early as possible. Avoid fetching a broad data set and filtering it later in ABAP when the database can perform that restriction safely.
5. SELECT SINGLE versus UP TO 1 ROWS?
SELECT SINGLE communicates that one row is required, ideally through a full key or another unique access. UP TO 1 ROWS limits a potentially multi-row result and can be combined with ORDER BY when a deterministic “first” row is required. Without a defined order, do not assume which matching row will be returned.
6. Which joins should an ABAP developer know?
An INNER JOIN returns matching rows from both sources. A LEFT OUTER JOIN keeps all rows from the left source and supplies null-derived values for unmatched right-side rows. Join conditions belong in ON; result restrictions normally belong in WHERE. Be careful because a WHERE condition on the right side can effectively remove unmatched outer-join rows.
7. Why is SELECT inside LOOP a common performance issue?
It can create one database round trip per loop iteration. Replace it with a set-oriented read using a join, association, suitable FOR ALL ENTRIES, or a bulk key range. Then perform efficient in-memory access using a suitable internal-table key.
SELECT h~vbeln,
h~erdat,
i~posnr,
i~matnr,
i~kwmeng
FROM vbak AS h
INNER JOIN vbap AS i
ON i~vbeln = h~vbeln
WHERE h~auart = @p_auart
AND h~erdat IN @s_erdat
INTO TABLE @DATA(lt_items).
IF sy-subrc = 0.
SORT lt_items BY vbeln posnr.
ENDIF.8. JOIN versus FOR ALL ENTRIES?
A join lets the database combine related data in one set operation and is often the first choice when sources and semantics fit. FOR ALL ENTRIES can be useful when keys already exist in an internal table or a join is not practical. Always ensure the driving table is not initial, keep its key set minimal and inspect duplicates and generated SQL behavior.
9. Why use aggregate functions and GROUP BY?
Functions such as SUM, COUNT, MIN and MAX reduce data at the database. With GROUP BY, the database returns only the grouped result instead of transferring detail rows for ABAP-side aggregation.
10. What does INTO CORRESPONDING FIELDS do?
It assigns selected columns to identically named target components. It improves resilience when source and target field order differ, but the target shape must still be intentional. Explicit aliases make mappings clear and avoid silent omissions.
11. How does table buffering affect SQL design?
Buffering can reduce database access for suitable, mostly read-oriented customizing or master data. However, some statements bypass buffering, including many joins and database-side calculations. Never enable buffering only to hide an inefficient query; confirm data-change frequency, consistency requirements and supported access patterns.
12. When does a secondary index help?
An index can help selective, frequent predicates whose leading columns match access patterns. It also consumes storage and adds maintenance cost to inserts, updates and deletes. Check existing indexes and real execution plans before proposing a new one.
13. What should you know about client handling?
For client-dependent Dictionary entities, ABAP SQL normally applies implicit current-client handling. Avoid unnecessary explicit client fields. Cross-client access is exceptional, security-sensitive and should follow the platform’s supported syntax and authorization model.
14. Is database row order guaranteed?
No. Without ORDER BY, the row order is undefined and can change with plans, indexes or database versions. Request ordering only when the business logic needs it, because sorting also has a cost.
15. Which tools are used to analyze SQL performance?
ST05 traces SQL and helps inspect statements, duration and access paths. SAT measures ABAP runtime and call hierarchy. SQL Monitor (SQLM) collects productive SQL usage over time. ATC detects many static quality and performance risks. Use evidence from the appropriate tool before changing code.
16. What is code pushdown?
Code pushdown moves data-intensive filtering, joining and calculation closer to the database using ABAP SQL, CDS or, only when justified, AMDP. Push down set operations—not unstable business orchestration. In ABAP Cloud, prefer released APIs and supported language elements.
17. Does a successful SELECT prove authorization?
No. Database access and business authorization are different concerns. The application must perform the required authorization checks or consume an API that enforces them. Never expose data merely because a table can be queried.
18. How are null values handled in outer joins?
An unmatched outer-join expression can produce a database null value. When moved to many ABAP targets it becomes the type’s initial value, so “no match” may be indistinguishable from a real initial value. Use suitable null indicators or functions such as COALESCE when that distinction matters.
Real interview scenarios
Scenario A: Report takes 40 seconds after data growth
Capture a reproducible case, measure with SAT and ST05, identify the expensive SQL, check rows examined versus returned, predicates and indexes, then change one bottleneck at a time. Re-run the same measurement and document the improvement.
Scenario B: 10,000 sales orders and their items
A SELECT inside the order loop risks thousands of round trips. Prefer one set-based join or a guarded bulk read. Return only required columns and dates, then use a sorted or hashed table key for repeated ABAP-side access.
Scenario C: FOR ALL ENTRIES returns unexpectedly many rows
First check whether the driving internal table was initial. Then remove duplicate keys, restrict selected columns, verify all required predicates and remember that duplicates in the final result can be affected by statement semantics.
Scenario D: A LEFT JOIN behaves like an INNER JOIN
Inspect filters on columns of the right-hand source. A restrictive WHERE predicate can eliminate the null-extended rows. Move relationship conditions to ON and express optional filtering without destroying the intended outer-join semantics.
Common interview mistakes
- Saying one construct is always faster without measuring.
- Using SELECT * for convenience in large productive reads.
- Forgetting the empty-table guard before FOR ALL ENTRIES.
- Assuming database row order without ORDER BY.
- Creating indexes without checking workload and write cost.
- Treating code pushdown as a reason to move every business rule to the database.
Hands-on exercise
Build a report that lists sales orders and items for an order type and date range. Version 1 may use a SELECT inside LOOP. Version 2 must use a set-oriented join, explicit columns and host variables. Compare the SQL trace, runtime and transferred row count. Explain which change made the biggest difference and why.
Rapid revision checklist
- Filter early and select only required columns.
- Prefer set operations over repeated round trips.
- Use @ for host variables in modern ABAP SQL.
- Guard FOR ALL ENTRIES with a non-initial driving table.
- Do not assume order without ORDER BY.
- Measure with ST05, SAT, SQLM and ATC before optimizing.
Official SAP references
Next: Part 5 — ABAP Objects Interview Questions
0 Comments