ABAP Developmentbeginner

Performance Part 2: The Database-First Rule

Key takeaway

Apply the database-first rule: read only needed rows and columns, use full keys and indexes, avoid repeated identical reads, aggregate in the database, use buffering where appropriate, and understand why database round trips dominate most ABAP runtimes.

Explanation

📌 KEY POINTS
✅ Most ABAP performance problems are database access: too many round trips, too many rows, too many columns, missing index use.
✅ Five rules: few round trips, few rows (good WHERE), few columns (field list instead of SELECT *), use indexes/keys, let the DB aggregate.
✅ Table buffering (SE11 technical settings) makes reads of small, rarely changed tables (customizing) nearly free — but bypass/unbuffered statements break it.
⚠️ SELECT * on wide tables (e.g. VBAP, MARA with hundreds of fields) moves large amounts of unneeded data — especially costly in loops and on HANA with column storage.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 1 measured. Now the principles that fix most findings.

━━━━━━━━━━━━━━━━━━━━
▶ 1. ROUND TRIPS

Each SELECT is a round trip to the database; thousands of small SELECTs cost more than one large one.
Read data in sets (Parts 4, 5, 7), not per record.\n\n━━━━━━━━━━━━━━━━━━━━\n▶ 2. ROWS\n• WHERE with key fields and selective conditions; ranges from the selection screen.
Avoid reading everything and filtering in ABAP (CHECK/DELETE after SELECT).

━━━━━━━━━━━━━━━━━━━━
▶ 3. COLUMNS

Field list with only needed fields; INTO CORRESPONDING FIELDS OF or a matching structure.

━━━━━━━━━━━━━━━━━━━━
▶ 4. INDEXES AND KEYS

Primary key or secondary index must match the WHERE condition (Part 10).
SELECT SINGLE with full key is the cheapest single read.

━━━━━━━━━━━━━━━━━━━━
▶ 5. AGGREGATION IN THE DATABASE

COUNT, SUM, MAX, MIN, GROUP BY instead of reading all rows and summing in ABAP.
Existence check: SELECT SINGLE field … UP TO 1 ROWS instead of reading full rows.

━━━━━━━━━━━━━━━━━━━━
▶ 6. BUFFERING

Customizing tables (e.g. T001, TVAK) often buffered; reads are served from the application server.
Buffer is bypassed by: BYPASSING BUFFER, aggregate functions, JOINs (on non-view), ORDER BY other than key, IS NULL, native SQL.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Find a SELECT * in your code and replace it with a field list.
Replace an ABAP summation loop with SELECT SUM … GROUP BY.
Check in ST05 that buffered tables no longer appear as DB accesses.

✅ CHECKPOINT: you apply the database-first rules to every data access you write.

Code example

ABAP Code

REPORT zperf_db_first. TYPES: BEGIN OF ty_sum,         kunnr TYPE kunnr,         netwr TYPE netwr,       END OF ty_sum. TYPES: BEGIN OF ty_head,         vbeln TYPE vbeln_va,         erdat TYPE erdat,         kunnr TYPE kunnr,       END OF ty_head. DATA: lt_sum  TYPE STANDARD TABLE OF ty_sum,      ls_sum  TYPE ty_sum,      lt_head TYPE STANDARD TABLE OF ty_head,      lv_exists TYPE vbeln_va. SELECT-OPTIONS s_erdat FOR sy-datum. START-OF-SELECTION.* ===== BAD: all columns, sum in ABAP =====*  SELECT * FROM vbak INTO TABLE lt_vbak WHERE erdat IN s_erdat.*  LOOP AT lt_vbak ... sum per customer ... ENDLOOP. * ===== GOOD: only needed columns =====  SELECT vbeln erdat kunnr FROM vbak    INTO TABLE lt_head    WHERE erdat IN s_erdat. * ===== GOOD: aggregation in the database =====  SELECT kunnr SUM( netwr ) FROM vbak    INTO TABLE lt_sum    WHERE erdat IN s_erdat    GROUP BY kunnr.   LOOP AT lt_sum INTO ls_sum.    WRITE: / ls_sum-kunnr, ls_sum-netwr.  ENDLOOP. * ===== GOOD: existence check =====  SELECT SINGLE vbeln FROM vbak INTO lv_exists WHERE erdat IN s_erdat.  IF sy-subrc = 0.    WRITE: / 'At least one order exists'.  ENDIF.* Note: SUM over NETWR in mixed currencies needs grouping by currency (WAERK) too.

Real project scenario

A customer revenue report read all VBAK and VBAP rows with SELECT * for a year and summed them in ABAP. It moved 12 GB of data per run and took 40 minutes.
Replacing it with a field-list SELECT and SUM … GROUP BY kunnr, waerk returned a few thousand rows directly from the database; runtime dropped to 45 seconds.

Common mistakes

SELECT * with few used fields.
Filtering in ABAP instead of WHERE.
Summing in ABAP instead of the database.
Bypassing buffers unintentionally.
Summing amounts across currencies.

Best practices

Field lists and selective WHERE.
Aggregate in the database.
Existence checks with minimal reads.
Use buffering for customizing tables.
Group by currency/unit where needed.

Interview angle

Questions: "What are the golden rules of ABAP database access?", "When is table buffering bypassed?", "Why avoid SELECT *?"
Strong answer: few round trips, few rows, few columns, use keys/indexes, aggregate in DB; buffering for small rarely changed tables, bypassed by BYPASSING BUFFER, aggregates, joins, ORDER BY non-key, IS NULL, native SQL; SELECT * transfers unneeded data, especially costly on wide tables and HANA.

Practice this lesson

Quick check

1. A report reads 900,000 rows and sums them in ABAP. What is the best improvement?

2. Which is the best way to check if any row exists?

3. Buffered tables should ideally be:

Interview questions

1. Explain buffered vs non-buffered DB tables and when buffering hurts more than it helps.

Answer it in your own words first, then compare.

2. A report reads 900,000 order rows with SELECT * and then filters and sums them in ABAP. How do you improve it?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.