CDS Associations Part 5: Troubleshooting Duplicates, Missing Data and Slow Queries

📌 KEY POINTS
✅ Too many rows → a to-many path or wrong cardinality.
✅ Missing rows → an inner join path, a filter on the target in WHERE, or access control.
✅ Slow → unnecessary joins, to-many paths in lists, functions in ON conditions.
💡 Always compare row counts with and without each path before changing code.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–4 built associations correctly. In real projects you inherit views that return strange results. This lesson is the troubleshooting method.

━━━━━━━━━━━━━━━━━━━━
▶ 1. SYMPTOM: TOO MANY ROWS (DUPLICATES)
Check in this order:

1. Is any to-many path used in the element list or WHERE? Remove it temporarily and compare counts.
2. Is a to-one association really to-one in the data? Count target rows per key (code example, query A).
3. Is a text or validity association missing its language/date condition?

Fix: filtered path with "1:", aggregation view, or add the missing condition. Do not add DISTINCT to hide it.

━━━━━━━━━━━━━━━━━━━━
▶ 2. SYMPTOM: MISSING ROWS

1. Is a path declared as [inner]? Inner paths drop source rows without a target.
2. Is there a WHERE condition on a target field? "WHERE _Customer.Country = 'DE'" excludes rows without a customer (NULL is not 'DE') — that may be correct or not.
3. Is the ON condition too strict (wrong key, client, language, type mismatch with leading zeros)?
4. Is CDS access control filtering the rows for this user? Test with a privileged read or another user (CDS Views Part 4).

━━━━━━━━━━━━━━━━━━━━
▶ 3. SYMPTOM: SLOW QUERY

1. Which paths are actually used by the consumer? Associations cost nothing unless used — check the consumer query and OData $expand/$select.
2. Are to-many paths in list views creating huge intermediate results?
3. Are there functions or calculations in ON conditions (e.g. CONCAT, SUBSTRING) that prevent efficient joins?
4. Is cardinality correct so the optimizer can prune joins?

Measure with ST05 and the explain plan; look at the generated SQL.

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

• ADT Data Preview with SQL console — run count queries quickly.
• Show SQL CREATE Statement — see the generated joins.
• ST05 SQL trace — see the statement a real consumer sends.
• Dependency Analyzer in ADT — see the view stack and all joined sources.

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

1. Reproduce with the exact consumer query.
2. Count rows with and without each suspicious path.
3. Check cardinality with the count queries in the code example.
4. Fix the model, re-run the counts, then measure runtime.

✅ CHECKPOINT: you have a method for every association defect. Next topic: CDS Annotations.

Users reported that a customer-order report "lost" about 300 orders. The developer suspected a database problem. Following the method, Query C showed exactly 300 orders without a customer master record — created by a migration with an old customer number format. The view used _Customer[inner].CustomerName, so those orders were dropped.
The fix was twofold: the path went back to the default LEFT OUTER semantics so the orders appeared with an empty customer name, and the migration team corrected the customer numbers. The investigation took 30 minutes instead of days because the team counted before changing code.

• Adding DISTINCT to hide duplicates.
• Changing code before reproducing and counting.
• Forgetting that WHERE conditions on target fields exclude rows without a target.
• Blaming associations for rows filtered by access control.
• Leading-zero or type mismatches in ON conditions.
• Measuring with SELECT * instead of the real consumer query.

• Classify the symptom first: too many, missing, or slow.
• Count with and without each path; verify cardinality with GROUP BY … HAVING.
• Check join type, target filters, ON conditions and DCL separately.
• Use the SQL console, SQL CREATE statement, ST05 and the dependency analyzer.
• Re-count and re-measure after every fix.

Questions: "A CDS view returns duplicates — how do you analyze it?", "Why could rows be missing?", "How do you prove cardinality is wrong?"
Strong answer: classify the symptom; for duplicates look for to-many paths, broken to-one cardinality (GROUP BY … HAVING COUNT > 1) and missing language/date conditions; for missing rows check [inner] paths, WHERE on target fields, strict ON conditions and access control (compare with privileged access); for slowness check used paths, to-many in lists and functions in ON. Always reproduce with the consumer query and count before and after.