Now accepting guest editorial from SAP, Oracle, Workday and ServiceNow practitioners.Pitch a guide
The Sign-Off Table

Lesson 2 of 5

Anti-patterns

10 min

Listen to this lesson

0:00 / 7:59

Most ABAP performance problems are not exotic. They are a handful of patterns that were tolerable on small volumes and become painful as data grows. After a system conversion they surface all at once, because volumes, data models and timing change together. Learn to recognize them on sight in a code review.

Database access anti-patterns

Anti-patternWhy it hurtsBetter
SELECT inside LOOPOne database round trip per row; thousands of tiny statementsOne SELECT with a join, a subquery, or a join with the internal table
Nested SELECT ... ENDSELECT loopsInner accesses multiply by the outer rowsA join, or read both sets once and combine in memory with keyed tables
SELECT * when you need three fieldsTransfers every column; on a column store, reads columns you never useName the columns or use a CDS view that exposes only what is needed
Filtering in ABAP after a broad SELECTTransfers rows only to throw them awayPut the condition in WHERE or HAVING
Summing in a LOOPTransfers detail rows to compute one numberSUM, COUNT, AVG with GROUP BY in the database
FOR ALL ENTRIES with an empty driver tableIf the internal table is empty, all rows are readCheck the table first, or use a join or subquery instead
Reading the same data repeatedlyRepeated identical statementsRead once, keep in a keyed internal table or use table buffering
EML statements in a loopMany single accesses through the RAP frameworkOne EML read for all keys, then one modify request

Common database access anti-patterns and their fixes, following SAP's ABAP performance notes.

  1. LOOP AT open_items120,000 open order items for fleet customers
  2. SELECT SINGLE pricing for this itemOne statement and one round trip per item: 120,000 statements
  3. SELECT customer master for this itemAnother 120,000 statements, mostly for the same 40 customers
  4. Sum in ABAPDetail rows travel only to be added up
  5. ResultOver three hours in CMQ, almost all of it waiting on round trips
Anatomy of the original fleet rebate run: the classic SELECT-in-LOOP pattern, twice.
The Sign-Off Table · thesignofftable.com/academy/sap-abp-302

Internal table anti-patterns

Once data is in memory, the table category decides how fast you can find things. A standard table searched with a key that is not its primary key is scanned line by line. On a few hundred lines that is invisible; on 100,000 lines inside another loop it is minutes. SAP's performance notes give clear guidance.

  • Use sorted or hashed tables, or secondary keys, for large tables you read by key many times. Hashed tables give constant access time but need the full key; sorted tables also work with a left-aligned partial key.
  • Avoid BINARY SEARCH on standard tables in new code; it needs a costly SORT and silently fails if the sort order is wrong. Use secondary table keys instead.
  • Restrict loops with WHERE conditions on key fields rather than looping over everything and checking with IF.
  • Avoid nested loops over two large standard tables; the cost is the product of their sizes.
Choose the internal table category by how the table is read; secondary keys replace BINARY SEARCH on standard tables in new code.
The Sign-Off Table · thesignofftable.com/academy/sap-abp-302
HarmlessSmall table, one pass: no tuning needed
Watch the transferMany rows, one pass: select only needed columns, aggregate in the database
Watch the loopFew rows, many lookups each: keyed internal tables
Fix firstMany rows, many accesses each: SELECT in LOOP, nested loops, linear searches
Where anti-patterns hurt most: the product of rows processed and accesses per row.
The Sign-Off Table · thesignofftable.com/academy/sap-abp-302

Patterns that changed with SAP S/4HANA

Some code was fine on ECC and is now slow or wrong because the data model changed during conversion. Old aggregate or index tables were removed or replaced by compatibility views, pricing conditions moved from table KONV to PRCD_ELEMENTS, and long material numbers changed field lengths. SAP's custom code checks in ATC flag many of these, and some quick fixes in ADT resolve frequent findings. Code that still reads through a compatibility view may work but perform worse than reading the new structure directly; check the SAP Note referenced by the finding.

Data model changes such as KONV to PRCD_ELEMENTS can make converted code slow or wrong, and the SAP S/4HANA checks in ATC point to them.
The Sign-Off Table · thesignofftable.com/academy/sap-abp-302

What would you do?

In a code review, Kenji sees: <code>SELECT * FROM zcmc_core_agr INTO TABLE @agreements.</code> followed by a LOOP with IF status = 'OPEN' AND customer_group = 'FL', summing deposit amounts. The table has 2.4 million rows. What is the best fix?

Key points
  • SELECT in LOOP and nested SELECT loops multiply round trips; replace them with joins or set-based reads.
  • Name columns, filter in WHERE, aggregate in the database; watch FOR ALL ENTRIES with empty tables.
  • Use sorted, hashed or secondary keys for large internal tables read by key.
  • Conversion changes data models; follow ATC findings and add ORDER BY where order matters.

Check your understanding · not graded

Lesson check

  1. 1. What happens when FOR ALL ENTRIES is used with an empty internal table?

  2. 2. Why is SELECT * an anti-pattern when you need three fields?

  3. 3. Which internal table technique does SAP recommend instead of BINARY SEARCH on standard tables?

  4. 4. Why can a SELECT without ORDER BY behave differently after moving to SAP HANA?