Performance Part 8: HANA Pushdown with Open SQL, CDS and AMDP

📌 KEY POINTS
✅ Code-to-data: let HANA filter, join, aggregate and calculate, and return only the result — instead of transferring raw rows to ABAP.
✅ Levels of pushdown: 1) good Open SQL (joins, aggregates, CASE, expressions), 2) CDS views (reusable, with associations and calculations), 3) AMDP (SQLScript procedures/table functions) for complex logic.
✅ Pushdown pays off for large volumes and set operations; simple single-record access gains little.
⚠️ Don't push everything into AMDP — SQLScript is harder to debug, maintain and transport-test; use the simplest level that solves the problem.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 2–7 reduced round trips and data. On HANA, you can go further: compute in the database.

━━━━━━━━━━━━━━━━━━━━
▶ 1. WHY PUSHDOWN

• HANA is in-memory and columnar: aggregations and scans over many rows are fast.
• Transferring millions of rows to the application server and summing in ABAP wastes time and memory.

━━━━━━━━━━━━━━━━━━━━
▶ 2. OPEN SQL PUSHDOWN

• Aggregates with GROUP BY and HAVING, JOINs, subqueries, CASE expressions, arithmetic (depending on release).
• Classic syntax still works for SUM/COUNT/GROUP BY; newer syntax offers more expressions.

━━━━━━━━━━━━━━━━━━━━
▶ 3. CDS VIEWS

• Defined in ADT (Eclipse): SELECT with joins/associations, calculated fields, aggregations, parameters.
• Reusable by reports, OData, analytics; consumed in ABAP like a table (SELECT FROM zcds_view).
• Annotations for authorization (DCL), analytics, UI.\n\n━━━━━━━━━━━━━━━━━━━━\n▶ 4. AMDP\n• ABAP class with methods implemented in SQLScript (BY DATABASE PROCEDURE / FUNCTION FOR HDB).
• For complex multi-step set logic not expressible in CDS/Open SQL.
• Debuggable with the AMDP debugger in ADT.

━━━━━━━━━━━━━━━━━━━━
▶ 5. WHEN NOT TO PUSH DOWN

• Small volumes, row-by-row business logic (BAPIs, validations with messages), logic that needs ABAP APIs.
• Readability for the team — a clear ABAP loop over 500 rows is fine.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

1. Replace an ABAP loop that sums per customer with SELECT … SUM … GROUP BY.
2. Create a simple CDS view joining order header and items with a calculated field (ADT).
3. Compare runtime of the ABAP and pushdown versions with realistic data.

✅ CHECKPOINT: you know the pushdown levels and choose the simplest that meets the need.

A customer profitability report on S/4HANA read 30 million line items into ABAP and aggregated them in nested loops, running 2 hours and often failing with memory dumps.
A CDS view aggregating by customer, product group and period returned 40,000 rows directly; the ABAP report only formatted them. Runtime dropped to 3 minutes. An AMDP proposed by one developer was not needed — CDS was enough and easier to maintain.

• Transferring raw rows to aggregate in ABAP.
• AMDP where Open SQL/CDS would do.
• Pushing message-heavy business logic into SQL.
• Ignoring authorization in CDS (DCL).
• No measurement before/after.

• Aggregate and filter in the database.
• Prefer Open SQL, then CDS, then AMDP.
• Reuse CDS models across reports and OData.
• Keep business validations in ABAP.
• Measure with realistic volumes.

Questions: "What is code pushdown?", "CDS vs AMDP?", "When would you not push down?"
Strong answer: code-to-data on HANA; levels: Open SQL (joins, aggregates, expressions), CDS views (reusable, associations, DCL), AMDP (SQLScript for complex set logic); pushdown for large set operations, not for row-wise business logic or small volumes; choose simplest level, measure.