Performance tracing, SQL and HANA analysis — Root-cause case, remediation and assessment

Training simulation: All sample identifiers, logs, requests, transactions, values and cases are fictional. Tools and permissions vary by SAP release; perform any changes only in approved DEV/QA environments.

Performance tracing, SQL and HANA analysis — Root-cause case, remediation and assessment

Problem: An ABAP report is slow or data appears incorrect after query optimization.

Environment: ABAP Open SQL and CDS/HANA landscapes; SQL Monitor, SAT/ST05 and HANA plan access require authorized trace scope.

Incident dossier (fabricated, for education)

Synthetic ST05 extract:SELECT VBAK ... executions=1, duration=12msSELECT VBAP WHERE VBELN=? ... executions=1500, total=6600msApplication runtime=7200ms, DB=6900msOrder count=1500; item rows expected=7800 

Evidence observations

1. One item SELECT per order explains 1500 DB executions and substantial cumulative duration.
2. The single header lookup is not the bottleneck, even if its statement looks complex.
3. A set-based query can reduce round trips but may increase memory or change join semantics.

Investigate the three hypotheses

1. An N+1 access pattern causes most cumulative database time.
2. A missing indexed filter or join condition makes one SQL statement expensive.
3. The UI/rendering or network layer dominates despite fast DB statements.

Supported hypothesis number(s): 1.

Evidence-backed cause

The synthetic trace attributes 6600 of 6900 ms database time to an item SELECT executed once per header row. The repeated round trip is the primary performance bottleneck.

Safe remediation

Move the repeated item lookup into a correctly filtered bulk read or appropriate CDS query; validate authorization and data equivalence before measuring the improved runtime.

Six-step resolution runbook

1. Define the exact measured report variant, client, user and representative dataset; capture outputs and elapsed time.
2. Run a scoped ST05 SQL trace and SAT analysis; record top statements by cumulative duration and execution count.
3. Identify the repeated VBAP lookup inside the order loop and verify that the trace corresponds to the relevant code.
4. Design a set-based Open SQL retrieval or validated join that preserves item-level filtering and authorization checks.
5. Re-run on matching data with controlled cache/time windows; compare DB requests, total runtime and memory.
6. Diff resulting document keys, item counts and totals; keep a regression for customer/plant/authorization-filtered variants.

Regression evidence

• Positive test: Same 1500 headers and 7800 item rows returned with identical business totals.
• Negative test: A restricted-role user sees only permitted orders; the optimization doesn't leak client data.
• Cross-process regression: Repeated runs demonstrate reduced cumulative DB time without excessive memory use.

Assessment

Question: A report runs 1500 identical item queries inside a loop. What is a likely first optimization hypothesis?

A. Replace with set-oriented retrieval while confirming semantics.
B. Add WAIT UP TO 1 SECONDS to the loop.
C. Disable user authorization filtering to speed it up.

Correct: A. An N+1 access pattern usually deserves controlled bulk-query evaluation.

Safety boundary: Limit performance traces to approved users/time windows. Never disable authorization, SQL client filters or productive indexes without DBA/change review.

Synthetic support case: An ABAP report is slow or data appears incorrect after query optimization.

The synthetic trace attributes 6600 of 6900 ms database time to an item SELECT executed once per header row. The repeated round trip is the primary performance bottleneck.

• The most expensive individual SQL statement is not always the biggest total cost; frequency matters.
• A CDS association can duplicate or omit records when cardinality assumptions are wrong.
• A cache-warmed single run is not defensible before-and-after performance evidence.

Move the repeated item lookup into a correctly filtered bulk read or appropriate CDS query; validate authorization and data equivalence before measuring the improved runtime.

• Positive test: Same 1500 headers and 7800 item rows returned with identical business totals.
• Negative test: A restricted-role user sees only permitted orders; the optimization doesn't leak client data.
• Cross-process regression: Repeated runs demonstrate reduced cumulative DB time without excessive memory use.

Limit performance traces to approved users/time windows. Never disable authorization, SQL client filters or productive indexes without DBA/change review.

Performance debugging is about measurement: SQL trace (ST05), runtime analysis (SAT), identifying expensive statements, N+1 loops, missing indexes and large result sets — and proving improvement with numbers.