CDS Views Part 3: Performance — Joins, Associations, Filters and Aggregation

📌 KEY POINTS
✅ Joins always run; associations join only when used.
✅ To-many paths in the select list multiply rows.
✅ Aggregate in CDS, always grouped by currency or unit.
⚠️ Measure with ST05 and the real consumer query, not SELECT *.

▶ WHERE WE ARE
Part 2 built ZI_SalesOrderItem. Now the question every project hits after go-live: why is this CDS-based list slow, and how do you design views that stay fast with millions of rows?

━━━━━━━━━━━━━━━━━━━━
▶ THE CORE IDEA: THE DATABASE DOES THE WORK
A CDS view is executed as SQL on SAP HANA. Performance depends on how much data the database must read, join and send back. Good design means: filter early, join only what is needed, aggregate in the database, and return only the columns and rows the consumer needs.

━━━━━━━━━━━━━━━━━━━━
▶ 1. JOIN VS ASSOCIATION

• A JOIN in the data source ("as select from zso_head inner join zso_item on ...") is always executed, even if the consumer never reads a joined field.
• An ASSOCIATION is only a declared relationship. It becomes a join only when a consumer actually uses it — via a path expression in a SELECT, in another view, or via OData $expand.

So model relationships as associations and let consumers decide. A list report that shows only order number and date never pays for the customer join.

━━━━━━━━━━━━━━━━━━━━
▶ 2. PATH EXPRESSIONS AND WHAT THEY GENERATE
"_Customer.CustomerName" in a view's element list, or "\_Customer-CustomerName" in ABAP SQL, generates a LEFT OUTER JOIN (by default) to the associated view.

• To-one associations ([0..1], [1..1]) are safe in the select list: one row stays one row.
• To-many associations ([0..*], [1..*]) in the select list or in WHERE multiply rows: an order with 5 items suddenly appears 5 times. This produces wrong counts and slow queries.
• Use filters on paths to make a to-many path to-one: "_Item[ ItemNumber = '000010' ].Material", or with "1:" cardinality hint when the filter guarantees one row.
• Declare cardinality correctly. Wrong cardinality lets the optimizer choose bad plans and hides duplicates.

━━━━━━━━━━━━━━━━━━━━
▶ 3. PUSH FILTERS DOWN

• Put selective conditions into the WHERE of the consumer's SELECT (or the consumption view), not in ABAP after the read.
• Avoid functions on filtered columns in join or WHERE conditions (e.g. comparing "substring( field, 1, 4 )"), because indexes and pruning cannot be used well.
• Use parameters when a value must be known inside the view before joins or aggregation (e.g. a key date).

━━━━━━━━━━━━━━━━━━━━
▶ 4. AGGREGATE IN CDS
Summing, counting and grouping belong in CDS with GROUP BY, not in ABAP loops. A view with "sum( NetAmount ) group by Customer, CurrencyCode" returns one row per customer instead of transferring every item.
Always group amounts by currency (and quantities by unit) — summing different currencies is a functional error, not only a performance one.

━━━━━━━━━━━━━━━━━━━━
▶ 5. LAYERING WITHOUT PENALTY
Stacking views (interface → composite → consumption) is fine; HANA flattens the SQL. What hurts: calculated fields on large tables used in joins, CASE expressions in join conditions, UNIONs of big sources without filters, and consumption views that expose dozens of unused to-many associations that UIs then $expand.

━━━━━━━━━━━━━━━━━━━━
▶ 6. HOW TO ANALYZE A SLOW VIEW

1. Reproduce with the real consumer query (the OData request or the ABAP SELECT), not "SELECT *".
2. In ADT: open the view → Show SQL CREATE Statement to see the generated SQL; use the Data Preview with SQL console for tests.
3. Record an SQL trace (ST05) while running the consumer, then look at executed statements, runtime and records.
4. On HANA, use the explain plan or PlanViz for the statement to see which joins and filters are applied.
5. Check row counts per join: unexpected multiplication usually points to a to-many path.
6. Fix the model (association instead of join, filtered path, aggregation, parameter), then measure again.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP (EXERCISE)
Build the aggregated view in the code example, compare its runtime with an ABAP loop that sums item amounts per customer, and use ST05 to see the difference in transferred rows.

━━━━━━━━━━━━━━━━━━━━
▶ WHAT YOU HAVE AFTER PART 3
Design rules and a repeatable analysis method for CDS performance. Part 4 covers who may see which rows: access control.

A Fiori list of open sales orders took 40 seconds and showed some orders several times. The consumption view contained "_Item.Material" and "_Item.NetAmount" in its element list to show "a material" and "an amount" per order. Both were to-many paths: each order was returned once per item, and the list then deduplicated in the UI.
The fix: the list view kept only header fields plus a to-one aggregated value from a separate view (sum of item net amount per order and currency), and item details moved to the object page, read via $expand only when an order is opened. The SQL trace showed the returned rows dropping from about 2.4 million to 60,000, and the list loaded in under two seconds.

• Using hard joins in basic views "just in case" instead of associations.
• Placing to-many path expressions in the select list or WHERE, multiplying rows.
• Declaring wrong cardinality (e.g. [0..1] for a relation that is really to-many).
• Summing amounts across currencies or quantities across units.
• Reading everything into ABAP and filtering or aggregating in loops.
• Functions or CASE expressions in join conditions on large tables.
• Testing performance with "SELECT *" and tiny data instead of the real consumer query and volumes.

• Model relationships as associations; let consumers decide what to join.
• Use to-one paths or filtered paths in element lists; keep to-many data for $expand or separate views.
• Aggregate in CDS with GROUP BY, always including currency or unit.
• Put selective filters into the consumer's WHERE or into parameters.
• Measure with ST05 and the explain plan using production-like volumes.
• Keep consumption views lean: only the fields and associations the app needs.

Common questions: "What is the difference between a join and an association in CDS?", "Why can a path expression produce duplicates?", "How do you analyze a slow CDS view?"
Strong answer: a join is always executed; an association is joined only when used, via path expression or $expand. To-many paths in the select list or WHERE multiply rows; use to-one or filtered paths and correct cardinality. Aggregate in CDS grouped by currency or unit. For analysis: reproduce the consumer query, check the generated SQL, record an SQL trace (ST05), inspect the explain plan or PlanViz, and compare row counts per join. A concrete before/after number makes the answer convincing.