Open SQL

Lessons in this topic

• SELECT SINGLE vs UP TO 1 ROWSUnderstand the difference between reading a unique record and reading any first matching record.
• Open SQL — writing fast, HANA-friendly queriesGolden rules for Open SQL performance: field lists, WHERE, joins vs FOR ALL ENTRIES, package processing, and HANA code pushdown.
• FOR ALL ENTRIES — Correct UseWhen to use, what to guard, and modern alternatives.
• Aggregates, GROUP BY and HAVINGUse database aggregation instead of fetching all rows and calculating totals in ABAP.
• Open SQL Performance Analysis with ST05, SAT and SQL MonitorLearn how to prove and fix Open SQL performance issues using SAP analysis tools.
• HANA-Friendly Open SQL and Code PushdownUnderstand how Open SQL should be written in S/4HANA to use database power safely.
• SELECT Fields, WHERE Conditions and the Cost of SELECT *Learn why field selection and WHERE conditions are the first rules of Open SQL performance.
• JOINs in Open SQL — When to Use and When to AvoidLearn practical join usage, cardinality risk and how joins compare with internal table processing.
• PACKAGE SIZE and Large Data ProcessingProcess large datasets safely without loading everything into memory at once.
• Open SQL in Real Interfaces, Reports and OData ServicesLearn how Open SQL design changes depending on whether you build a report, interface or OData service.

Interview questions covered

• A SELECT statement into an internal table takes 30 seconds. After analyzing the code, you realize the WHERE clause doesn't use any indexed fields, and a full table scan is occurring. How would you refactor this, and what tools would you use to verify improvement?
• Design a robust error handling and monitoring strategy for a production Open SQL batch job that processes 10 million rows. Include recovery, logging, and performance tracking.
• Compare CDS Views vs. Open SQL for data retrieval in SAP S/4HANA. When would you recommend each approach?
• You are tasked with fetching 500,000 sales orders and their line items for a month-end batch. Describe your approach to avoid memory issues and database timeouts, including PACKAGE SIZE, batching, and commit strategy.
• You are debugging a SELECT query using ST05 (SQL Trace). Walk through the key metrics you would examine to diagnose performance issues and identify the root cause.
• Explain the GROUP BY and HAVING clauses in Open SQL. Provide an example that counts orders by customer and filters for customers with > 10 orders.
• Explain the relationship between index field order and WHERE-clause selectivity in Open SQL. Does the written order of conditions in the WHERE clause matter?
• Describe the ORDER BY clause in Open SQL. What is the default sort order, and how do you specify descending order for specific fields? What are the performance implications?
• You are designing a high-performance batch job to export 2 million sales orders to a data warehouse. Walk through your Open SQL strategy, including PACKAGE SIZE, indexing, and monitoring.
• Write an Open SQL SELECT INTO TABLE statement that fetches material master header data from MARA for a list of materials. Include proper field selection, WHERE clause, and no-data handling.
• Explain the difference between SELECT SINGLE and SELECT ... UP TO 1 ROWS in Open SQL. When would you use each, and what are the performance and determinism implications?
• A SELECT statement returns duplicate rows due to a JOIN with a table that has multiple matches. How would you identify this issue and resolve it?
• Explain aggregate functions in Open SQL (SUM, COUNT, AVG, MAX, MIN) and how they work with GROUP BY. Include how to handle NULL values.
• A SELECT query returns results, but you notice unexpected NULL values in certain fields. How would you handle NULL values in Open SQL, and what are the implications for business logic?
• A performance report queries sales data across 10 SAP tables. Explain your approach to optimize this using Open SQL, including when you would consider a CDS View or materialized aggregates.
• You notice that a SELECT statement with an IN operator and a large list (5000 values) is slow. Explain the performance issue and propose alternatives.
• Explain the concept of database pushdown in S/4HANA and how it influences your Open SQL design decisions.
• A report uses multiple SELECT statements inside a loop, fetching related data for each order. Redesign this using a single query with JOINs and explain the performance benefit.
• Your SELECT statement uses FOR ALL ENTRIES but the driver table is sometimes empty during execution. How does this affect the query result, and how would you code defensively?
• Describe how client handling (MANDT) works in Open SQL. When is client filtering automatic, and when must it be handled explicitly?

Related topics