Clean Core Architecture Guide #49

SAP Clean Core CDS Query Performance & SQL Plan Analysis Guide

⏱️ 16 min read 🏢 Enterprise Architectural Blueprint ⚡ Updated for S/4HANA 2023 & HANA 2.0 SPS07

1. The Four Deadly CDS Performance Anti-Patterns

Even on cutting-edge SAP HANA hardware, inefficient CDS view modeling will cripple user response times. The following four anti-patterns represent 90% of real-world production performance degradations:

Anti-Pattern	Underlying Mechanism	Symptom in ADT Plan Visualizer	Remediation
HANA Row-Engine Fallback	Complex calculations, string concats, or non-deterministic functions in JOIN conditions	Red node: HEX fallback to Row Engine	Move calculations into projection fields; join only on raw indexed columns
Unintentional Full Joins	Declaring associations with [1..*] without filtering, auto-evaluated as Left Outer Join	Massive intermediate row explosion (Cartesian multiplier)	Use [0..1] or [1..1] cardinality; expose lazily instead of selecting in view
Missing Filter Pushdown	Filtering in ABAP application server memory (LOOP AT ... WHERE) instead of CDS WHERE	High network I/O; millions of rows transferred across RFC/network bus	Pushdown WHERE clauses and mandatory input parameters to CDS entity
Repeated Text Table Joins	Joining master data text tables (MAKT, BUT000, T001W) repeatedly in composite views	Severe memory fragmentation in HANA Cache	Use @ObjectModel.text.association for lazy client-side OData resolution

2. Cardinality & Lazy Association Pruning

In modern CDS View Entities, associations represent potential relationships. Unlike classic DDIC database views, the SAP HANA optimizer will completely eliminate the join from the generated SQL statement if the client query does not explicitly request fields from the target table.

However, this join elimination only works if the association is defined with correct cardinality:

// CORRECT: Join is pruned if client does not project _Customer fields
association [0..1] to I_Customer as _Customer
  on $projection.SoldToParty = _Customer.Customer;

// DANGEROUS: Forcing [1..*] forces HANA to evaluate cardinality to prevent data loss!
association [1..*] to I_SalesOrderItem as _Items
  on $projection.SalesOrder = _Items.SalesOrder;

3. Analyzing Execution Plans with ADT SQL Plan Visualizer

When tuning a slow CDS view in Eclipse ADT, never guess. Use the SQL Plan Visualizer:

1. In Eclipse ADT, right-click the CDS View Entity -> Open With -> SQL Console.
2. Write your target test query and click Execute & Open Plan Visualizer (Ctrl+Shift+Alt+P).
3. Examine the Critical Path (Red Highlighted Nodes):
   • Table Scan: Indicates no matching index on the filtered column. Consider creating a CDS secondary index on custom tables or filtering on partitioned columns (e.g. BUKRS, GJAHR).
   • Buffer Size Spikes: Look for intermediate row counts that exceed the final result set by more than 100x. This indicates a missing join condition or join order miscalculation.

4. Advanced Performance Annotations

Clean Core CDS View Entities support dedicated performance governance annotations:

@Environment.sql.passValue: true
@ObjectModel.usageType: {
  serviceQuality: #A,       // #A: Fast OLTP/Fiori, #B: Operational, #C: Analytical
  sizeCategory: #XL,        // #XL: > 10M rows, #L: 1M-10M, #S: < 100k
  dataClass: #TRANSACTIONAL
}

Summary Architectural Checklist

• ✅ 100% Columnar Execution: Zero Row-Engine fallbacks in SQL Plan Visualizer.
• ✅ Accurate Cardinality: All associations marked [0..1] or [1..1] for join pruning.
• ✅ Lazy Text Resolution: Master data texts exposed via annotations, not explicit inner joins.
• ✅ No Functions in ON Conditions: String concatenations and date arithmetic moved to projection list.