CDS Views Part 10: Performance and Troubleshooting

📌 KEY POINTS
✅ A CDS view stack is compiled into one SQL statement; HANA optimizes the whole stack — but only what it can see and simplify.
✅ Most slow CDS views come from a few patterns: functions or casts in join conditions, to-many paths, calculations/conversions on all rows before filtering, and deep stacks with joins nobody uses.
✅ Tools: ADT "Show SQL CREATE Statement", Dependency Analyzer, ST05 SQL trace, SQL Monitor (SQLM), and HANA PlanViz for the execution plan.
⚠️ Pushdown is not automatically fast: moving a bad algorithm into the database still makes a bad algorithm — just harder to debug.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–9 built a full CDS model. This final part keeps it fast in production.

━━━━━━━━━━━━━━━━━━━━
▶ 1. HOW A STACK EXECUTES

• Consumer SELECT + all views below = one statement.
• Unused associations are not joined (join on demand).
• Joins in the view definition are always part of the statement unless HANA can prove they are removable (join pruning needs correct cardinality and no field usage).

━━━━━━━━━━━━━━━━━━━━
▶ 2. EXPENSIVE PATTERNS

• Functions/casts in ON conditions (concat, cast on key fields) — no index use.
• To-many paths in field lists — row explosion, then aggregation on top.
• currency_conversion or unit_conversion on millions of rows before filtering.
• CASE and calculated fields used in WHERE by consumers — filter cannot be pushed to the base table index.
• Deep stacks (10+ levels) with repeated joins to the same table.
• UNION views where one branch reads a huge table without filters.

━━━━━━━━━━━━━━━━━━━━
▶ 3. DESIGN RULES

• Filter early: selective fields available as plain columns so consumer WHERE reaches the base table.
• Aggregate before converting where business allows; convert on the reduced set.
• Join on key fields with equal types; declare honest cardinality.
• Keep consumption views thin; avoid consumption-on-consumption.
• Select only needed fields in consumers — unused fields from unused joins may be pruned.

━━━━━━━━━━━━━━━━━━━━
▶ 4. ANALYSIS TOOLS

• ADT: open the view → Show SQL CREATE Statement to see the generated SQL.
• ADT Dependency Analyzer: shows the full view stack, join count and complexity.
• ST05: SQL trace of the consumer, duration and records per statement; Explain for the plan.
• SQLM (SQL Monitor): which statements cost the most in production over time.
• HANA PlanViz: operator-level plan — where rows explode or filters are applied late.

━━━━━━━━━━━━━━━━━━━━
▶ 5. TROUBLESHOOTING WORKFLOW

1. Reproduce with the exact consumer SELECT and data volume.
2. Trace with ST05; note duration and row counts.
3. Check the stack in the Dependency Analyzer.
4. Look for the patterns above; test by removing one join/calculation at a time.
5. Re-measure; compare row counts and totals after each change.

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

1. Take a slow custom view and open its Dependency Analyzer.
2. Run the consumer with ST05 active; record runtime.
3. Move a currency conversion after aggregation, or replace a function-based join with a key join.
4. Re-trace and compare.

✅ CHECKPOINT: you can find and fix the typical causes of slow CDS views with measurements, not guesses.

A group-currency sales dashboard took 90 seconds to open. ST05 showed one statement reading 30 million VBAK/VBAP rows; PlanViz showed currency conversion running on every row before the filter on sales organization was applied, because the filter field was a CASE-derived value.
The team exposed sales organization as a plain column so the filter reached the base table, aggregated per currency before conversion, and removed two joins that no consumer used. The dashboard opened in 3 seconds, with totals reconciled against the old version.

• Assuming "it's CDS, so it's fast" without measuring.
• Currency conversion on every row before filtering or aggregation.
• Consumers filtering on calculated fields instead of base columns.
• Functions or casts in join conditions.
• Tuning without comparing row counts and totals afterwards.

• Measure first: ST05, SQLM, PlanViz.
• Keep filter fields as plain columns; filter early.
• Aggregate before converting where the business rule allows.
• Review stacks with the Dependency Analyzer; remove unused joins.
• After every tuning change, verify results against the original.

Questions: "A CDS-based Fiori app is slow — how do you analyse it?", "Which CDS patterns hurt performance?", "Does pushdown always improve performance?"
Strong answer: reproduce, trace with ST05, check the stack in the Dependency Analyzer and the generated SQL, use PlanViz for the plan and SQLM for production hotspots. Typical causes: functions in joins, to-many paths, conversions on all rows, filters on calculated fields, deep stacks and unused joins. Pushdown helps only when the logic is set-based and filters early; a bad design in the database stays slow.