debug-slow-sql

Debug Slow SQL / OData

Find the root cause of a slow ABAP SQL or Fiori-Elements OData request and propose a fix — driving ARC-1's diagnostics first (GUI-free), then escalating to SAP GUI / Fiori apps only when the deeper signal needs them. The goal is not "it's slow" but why: which statement, what it scans, and what to change.

Use this when the user reports a slow report/transaction, a slow OData/Fiori list, a long-running CDS view, a timeout/dump under load, or "this query got slow after …".

---

Inputs (ask only for what's missing)

• What is slow — one of: an OData URL (copy from the browser Network tab), a CDS view / DDLS name, an ABAP program / class / report, a transaction code, or a table + access pattern.
• How slow / how often — a single slow call vs. slow-under-load vs. intermittent. (Routes you to per-request vs. aggregate analysis.)
• Reproducible? — can the user re-trigger it on demand (needed to arm a live trace)? On which system (DEV/QA/PROD) and as which SAP user?
• Recent change? — new code, new data volume, a transport, an index drop. Narrows the search fast.

If you only have a vague "X is slow", get the OData URL or the object name first — everything below keys off it.

---

The diagnostic ladder — stop at the rung that explains it

Work top-down. Each rung is cheaper than the next and usually tells you whether to descend.

Reachability — don't promise a plan you can't reach. Rungs 3–4 (profiler, ST05) need SAP_ALLOW_WRITES to arm and S_ADT_RES (ACTVT 01 and 02) to read — and don't exist on NW 7.50. If arming returns writes disabled or a 403, say so and stop at rung 2: odata_perf + cds_sql + SAPQuery already pin a DB-bound root cause GUI-free. Tier-3–4 (the exact statement + execution plan) then depends on Basis/config, so hand the user the precise ST05/SAT steps below instead of pretending you reached the plan.

0. Orient (no execution)

• SAPContext(action="deps", type="<type>", name=…) / SAPRead(type="DDLS", name=…) — read the CDS/ABAP source. Eyeball it for the usual suspects before measuring: LIKE '%term%' (leading wildcard = no index), SELECT … FROM with no WHERE on a key, SELECT *, nested SELECT in a LOOP (N+1), client-side filtering, missing FOR ALL ENTRIES pre-check, calculated fields forcing a full scan.
• SAPContext(action="impact", type="DDLS", name=…) — the CDS stack (projection → base views → tables). The slow view is often a thin projection over a heavy base.
• Find the generator, not just the literal SQL. A slow LIKE '%…%'/scan is often generated — by a search help, SADL/RAP, or a framework — not hand-written, so a grep/where-used for the literal SELECT comes up empty. Trace the generator instead: for a value-help / type-ahead screen, the search help (DD30L.SELMETHOD + FUZZY_SEARCH, DD32S fields) and its DSH/SADL classes (CL_DSH_*, the F4→WHERE conversion) via SAPSearch/SAPNavigate/SAPRead. For an OData service, find its implementation: a V4/RAP binding → SAPRead(type="SRVB", name=…) → the service definition → the CDS view (then use cds_sql, rung 2). The binding (SRVB) name often differs from the URL's service path — if SRVB 404s, SAPSearch the service name to find the actual binding, or read the service definition (SRVD) directly for its expose … as entities. A classic SEGW / Gateway V2 service has no CDS → find the DPC class (SAPSearch "<SERVICE>_DPC*", read its *_get_entityset / *_get_entity / expand / resolve_navigation_path methods) and skip rung 2. (Don't confuse SE91/WBMESSAGES, which loads one message class in memory and searches with CS, with a search-help path that issues a real DB LIKE.)