Open SQL Part 9: HANA-Friendly SQL and Code Pushdown

📌 KEY POINTS
✅ On HANA the biggest wins are fewer round trips and less data transferred — not micro-optimizing ABAP.
✅ Push work to the database: filtering, joins, aggregation, calculations with SQL expressions.
✅ Classic rules still apply: no SELECT in loops, field lists instead of *, FAE checks.
💡 Logic reused by several programs belongs in a CDS view.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–8 taught correct and measured SQL. After a move to S/4HANA or Suite on HANA, the same code often runs faster — but classic patterns that move data to ABAP waste most of HANA's strength.

━━━━━━━━━━━━━━━━━━━━
▶ 1. WHAT CHANGES WITH HANA

• Column store: reading few columns of many rows is very fast; SELECT * of wide tables is costly.
• In-memory aggregation and joins are very fast.
• Many secondary indexes of the old database are unnecessary; some statements need a different approach.
• The expensive part is the round trip and transfer between application server and database.

━━━━━━━━━━━━━━━━━━━━
▶ 2. CODE PUSHDOWN IN ABAP SQL (7.40 SP05+ STRICT SYNTAX)

• Calculations: price * seatsocc AS revenue.
• CASE: CASE WHEN seatsocc >= seatsmax THEN 'FULL' ELSE 'OPEN' END AS status.
• CAST, COALESCE, string functions (CONCAT, SUBSTRING, UPPER), date functions (DATS_DAYS_BETWEEN), arithmetic functions (ROUND, DIV).
• Aggregations with GROUP BY/HAVING (Part 6).
• Subqueries: WHERE EXISTS ( SELECT … ), IN ( SELECT … ).
• Common table expressions (WITH) in newer releases.

━━━━━━━━━━━━━━━━━━━━
▶ 3. CLASSIC ANTI-PATTERNS TO REMOVE

• SELECT … ENDSELECT with processing per row → INTO TABLE.
• SELECT inside LOOP → JOIN/FAE + internal table.
• Nested SELECTs → JOIN.
• Reading everything and filtering/aggregating in ABAP → WHERE/GROUP BY.
• Unnecessary sorting in ABAP of sorted DB results.

━━━━━━━━━━━━━━━━━━━━
▶ 4. WHEN TO USE CDS VIEWS
If the same join/calculation is needed in several programs, services or analytics, model it once as a CDS view entity (see RAP/CDS module) and select from it. For HANA-specific procedural logic, AMDP exists — but only as an exception.

━━━━━━━━━━━━━━━━━━━━
▶ 5. WHAT STAYS IN ABAP
Complex business rules, calls to BAPIs/APIs, authorization checks, messages, and UI — pushdown is for data-intensive work, not for all logic.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Compare the classic and pushed-down versions in the code example with ST05: statements, rows transferred, runtime.

✅ CHECKPOINT: you move data-intensive work to the database and keep business logic readable in ABAP.

After a migration to S/4HANA, a margin report was only slightly faster than before. ST05 showed it read 3 million document lines with SELECT * and computed margins, statuses and totals in ABAP loops.
Rewriting the core as one SELECT with CASE expressions, arithmetic and GROUP BY — later moved into a CDS view reused by an analytical app — reduced runtime from 25 minutes to 40 seconds and removed 600 lines of ABAP.

• Assuming HANA makes any code fast automatically.
• SELECT * on wide tables in column store.
• Computing and aggregating in ABAP what SQL can do.
• Pushing complex business rules into unreadable SQL.
• Copy-pasting the same join/calculation into many programs instead of a CDS view.

• Minimize round trips and transferred data.
• Use SQL expressions, aggregation and subqueries in strict syntax.
• Remove SELECT-in-loop and nested SELECT patterns.
• Model reusable logic as CDS views.
• Keep business rules and API calls in readable ABAP.

Questions: "What is code pushdown?", "What changes for ABAP SQL on HANA?", "Which SQL features support pushdown?"
Strong answer: code pushdown moves data-intensive operations (filter, join, aggregate, calculate) to the database to reduce transfer and round trips; on HANA column store and in-memory processing make this very effective while SELECT * and row-by-row processing waste it; ABAP SQL supports CASE, CAST, arithmetic, string/date functions, subqueries and aggregates; reusable logic goes to CDS views; business logic stays in ABAP.