Performance & SQL on HANA

• QuestionWhat is "Five rules that fix 80% of slow SQL"?Tap to revealAnswerPush aggregation down, project only the fields you need, treat FOR ALL ENTRIES with care, key your internal tables, and drop ENDSELECT.
• QuestionIn "Five rules that fix 80% of slow SQL", explain:Tap to revealAnswerPush aggregation down — count/sum/group on the DB, not in an ABAP loop.
• QuestionIn "Five rules that fix 80% of slow SQL", explain:Tap to revealAnswerProject narrowly — no SELECT *, no INTO CORRESPONDING when you can name fields.
• QuestionIn "Five rules that fix 80% of slow SQL", explain:Tap to revealAnswerFOR ALL ENTRIES is not free: fine under ~10 entries, prefer a CDS join for big drivers.
• QuestionIn "Five rules that fix 80% of slow SQL", explain:Tap to revealAnswerRead internal tables by key with SORTED/HASHED, not STANDARD + BINARY SEARCH.
• QuestionAnalogy for "Five rules that fix 80% of slow SQL":Tap to revealAnswerDon't haul the whole warehouse to your desk to count boxes — ask the warehouse for the count, and only the items you actually need.
• QuestionWhat is "The diagnostics toolkit"?Tap to revealAnswerEach question has a tool: ST05 for SQL, SAT for CPU, SQLM/SWLT for production hotspots, DBACOCKPIT/PLANVIZ for plans, SCMON/SCOV/SUSG for usage and coverage.
• QuestionIn "The diagnostics toolkit", explain:Tap to revealAnswerST05 — SQL trace: what SQL ran, how often, how long; first stop for slow SQL and lock waits.
• QuestionIn "The diagnostics toolkit", explain:Tap to revealAnswerSAT — runtime/ABAP trace (replaces SE30) for a CPU profile of an ABAP path.
• QuestionIn "The diagnostics toolkit", explain:Tap to revealAnswerSQLM — SQL Monitor aggregates statements on production; SWLT joins SQLM with Code Inspector findings.
• QuestionIn "The diagnostics toolkit", explain:Tap to revealAnswerDBACOCKPIT — HANA SQL plan cache; PLANVIZ — per-statement plan tree.
• QuestionAnalogy for "The diagnostics toolkit":Tap to revealAnswerA mechanic's diagnostic bay — a different gauge for fuel, timing and compression; you read the right gauge for the symptom.
• QuestionWhat is "Declarative, projected, joined"?Tap to revealAnswerReplace a SELECT SINGLE in a loop with FOR ALL ENTRIES, and ideally a single INNER JOIN or CDS view — one set-based read instead of N round trips.
• QuestionIn "Declarative, projected, joined", explain:Tap to revealAnswerAnti-pattern: SELECT SINGLE inside LOOP AT — one round trip per row.
• QuestionIn "Declarative, projected, joined", explain:Tap to revealAnswerBetter: FOR ALL ENTRIES — one statement, but you merge and de-dup yourself.
• QuestionIn "Declarative, projected, joined", explain:Tap to revealAnswerBest: a single INNER JOIN, or a CDS view consumed by one SELECT.
• QuestionIn "Declarative, projected, joined", explain:Tap to revealAnswerName the columns and let the join run on HANA where the data lives.
• QuestionAnalogy for "Declarative, projected, joined":Tap to revealAnswerDon't phone the warehouse once per item; send one list and get one answer back.
• QuestionWhat is "Hints & buffering"?Tap to revealAnswerPrefer rewrites over hints (hints outlive their reason); pick the right ABAP buffer mode; and remember FAE and BYPASSING BUFFER skip the buffer, which is not the HANA cache.
• QuestionIn "Hints & buffering", explain:Tap to revealAnswerHints go via `%_HINTS`, e.g. `%_HINTS HDB 'USE_HEX_PLAN'` on HANA.
• QuestionIn "Hints & buffering", explain:Tap to revealAnswerPrefer a statement rewrite over a hint — hints outlive their justification.
• QuestionIn "Hints & buffering", explain:Tap to revealAnswerBuffer modes: single-record (point lookups), generic (leading-key groups), full (tiny config tables).
• QuestionIn "Hints & buffering", explain:Tap to revealAnswerFOR ALL ENTRIES and BYPASSING BUFFER skip the ABAP buffer.
• QuestionAnalogy for "Hints & buffering":Tap to revealAnswerA hint is a sticky note overriding the GPS — handy once, but it stays stuck long after the road changed; the ABAP buffer and HANA cache are two different glove-box maps.
• QuestionWhat is "Rarely-documented performance pitfalls"?Tap to revealAnswerWatch the silent traps: FOR UPDATE takes write locks, SET f = f + 1 serializes the row (use a number range), dynamic Open SQL defeats reuse and ATC, and ACCEPTING DUPLICATE KEYS drops rows without error.
• QuestionIn "Rarely-documented performance pitfalls", explain:Tap to revealAnswerSELECT ... FOR UPDATE takes write locks on HANA — use only when you truly need the consistency guarantee.
• QuestionIn "Rarely-documented performance pitfalls", explain:Tap to revealAnswerUPDATE SET f = f + 1 serializes the row version chain; use a number-range object for counters.
• QuestionIn "Rarely-documented performance pitfalls", explain:Tap to revealAnswerDynamic Open SQL defeats optimizer plan reuse and static ATC checks.
• QuestionIn "Rarely-documented performance pitfalls", explain:Tap to revealAnswerINSERT ... ACCEPTING DUPLICATE KEYS silently drops duplicate rows — no error.
• QuestionAnalogy for "Rarely-documented performance pitfalls":Tap to revealAnswerThese are the potholes with no warning sign — fine if you know they're there, axle-breaking if you don't.

1. 1Summarise the core idea

   Analyse~10 min

   In your own words, summarise the central idea of "Five rules that fix 80% of slow SQL". Aim for one paragraph, no jargon you haven't earned.

   Done when: A non-expert could read your paragraph and explain it back to you.

   Re-read the source concept

2. 2Translate it to a real situation

   Apply~15 min

   Find a real situation from your own work / life where "Rarely-documented performance pitfalls" applies. Translate the idea — describe the situation, the move you'd make, and the alternative you considered first.

   Done when: You can name a specific situation (not a hypothetical), state your move, and explain the trade-off in one paragraph.

   Re-read the source concept

3. 3Teach it back

   Evaluate~5 min

   Pick someone (a colleague, a peer, a rubber duck) and explain the section to them in under 5 minutes. Use your own example, not the one from the lesson.

   Done when: Your listener can repeat the core idea + the trade-off back to you without prompting.

Why this matters. Reading + quizzing prove recall; applying it in a real situation is the only evidence of transfer. Treat at least one of these as non-negotiable before you move on.