# ABAP FOR ALL ENTRIES vs SELECT in a LOOP: Runtime Calculator

> Source: https://netray.co/tools/abap-for-all-entries-vs-nested-select-calculator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Model the database cost of a SELECT inside a LOOP against FOR ALL ENTRIES and see how the gap grows with the driver table size.

## Category

sap

## Erp

SAP ECC / S/4HANA

## Audience

- ABAP developer
- SAP technical consultant
- Performance analyst

## Tool

**Kind:** calculator

**Inputs:**

#### Driver table rows

**Key:** outer_rows

**Type:** number

**Default:** 2000

**Min:** 1

**Step:** 100

**Unit:** rows

**Help:** Rows in the internal table you loop over, for example open sales order items.

#### Cost of one single-row SELECT

**Key:** inner_ms

**Type:** number

**Default:** 1.2

**Min:** 0

**Step:** 0.1

**Unit:** ms

**Help:** Database time plus network round trip for one indexed read. Take it from ST05 or SAT.

#### Rows per FOR ALL ENTRIES block

**Key:** block_size

**Type:** number

**Default:** 50

**Min:** 1

**Step:** 10

**Unit:** rows

**Help:** The driver table is split into blocks before it reaches the database. The size depends on your profile and database settings.

#### Cost per block statement

**Key:** block_ms

**Type:** number

**Default:** 6

**Min:** 0

**Step:** 0.5

**Unit:** ms

**Help:** Parse, execute and round trip for one generated statement.

#### Cost per returned row

**Key:** row_ms

**Type:** number

**Default:** 0.04

**Min:** 0

**Step:** 0.01

**Unit:** ms

**Help:** Transfer and move into the result table.

**Outputs:**

#### Nested SELECT total

**Key:** nested_ms

**Expr:** outer_rows * inner_ms

**Format:** number

**Description:** One round trip per driver row.

#### FOR ALL ENTRIES blocks

**Key:** blocks

**Expr:** ceil(outer_rows / block_size)

**Format:** integer

**Description:** Number of statements sent to the database.

#### FOR ALL ENTRIES total

**Key:** fae_ms

**Expr:** blocks * block_ms + outer_rows * row_ms

**Format:** number

#### Nested SELECT runtime

**Key:** nested_s

**Expr:** nested_ms / 1000

**Format:** decimal

**Primary:** true

**Description:** Seconds.

#### FOR ALL ENTRIES runtime

**Key:** fae_s

**Expr:** fae_ms / 1000

**Format:** decimal

**Primary:** true

**Description:** Seconds.

#### Speed-up factor

**Key:** speedup

**Expr:** if(gt(fae_ms, 0), nested_ms / fae_ms, 0)

**Format:** decimal

**Description:** How many times faster the set-based read is.

#### Seconds saved per run

**Key:** saved_s

**Expr:** nested_s - fae_s

**Format:** decimal

**Chart:**

#### Runtime in seconds as the driver table grows

**Type:** line

**X:** outer_rows

**From:** 100

**To:** 10000

**Steps:** 30

**Series:**

- nested_s
- fae_s

**Disclaimer:** A planning model with linear costs. Real runtimes depend on buffering, indexes, database load and the generated SQL. Confirm with SAT and ST05 on your system.

## Content

**Intro:** A SELECT SINGLE inside a LOOP is one of the oldest ABAP performance mistakes, and one of the easiest to measure. This calculator compares the round-trip cost of per-row reads with a blocked FOR ALL ENTRIES read so you can justify the rewrite with numbers.

**Sections:**

#### How to use the calculator

**Body:** Run your report with a trace in SAT or ST05 and note the average time of one single-row SELECT and the number of executions. Enter the driver row count, the per-statement cost and the cost of a block statement. The result shows both runtimes, the speed-up and a sweep chart so you can see where the curves separate. Small driver tables often show little difference, which is a valid finding: not every loop needs rewriting. Large driver tables show a steep nested line because every row pays a full round trip.

**Bullets:**

- Take statement timings from a real trace, not guesses
- Use the chart to find the row count where it starts to matter
- Re-test with production-sized data, not a 20 row test client

#### The model behind the numbers

**Body:** Nested reads cost rows multiplied by the per-statement time. A FOR ALL ENTRIES read costs the number of blocks multiplied by the per-block time, plus a small per-row transfer cost. The block count is the driver rows divided by the block size, rounded up. This is why the gain grows roughly with the block size: you replace many round trips with few. On HANA, a JOIN or a CDS view pushed down to the database often beats both, because the join happens next to the data and only the result travels.

**Bullets:**

- Nested cost = rows x time per statement
- FAE cost = ceil(rows / block size) x block time + rows x row time
- A JOIN avoids the driver table entirely when both tables are on the same database

#### Rules that keep FOR ALL ENTRIES correct

**Body:** FOR ALL ENTRIES silently removes duplicate rows from the result, so include the key fields or the SELECT list will lose data. If the driver table is empty, the WHERE clause is ignored and the whole table is read, which has caused real production incidents, so always check the driver table first. Sort the driver table and delete adjacent duplicates before the read, since duplicates add blocks without adding rows. Select only the columns you need, and never use FOR ALL ENTRIES against a driver table that can hold hundreds of thousands of rows without checking memory.

**Bullets:**

- Guard with IF driver_table IS NOT INITIAL
- Include key fields so duplicates are not collapsed
- Delete duplicates in the driver table first
- Prefer a JOIN or CDS view when the data sits in one database

**Cta:** If you maintain hundreds of custom reports, finding the slow loops is the real work. Netray can help scan custom code for these patterns and prioritise the rewrites.

**Faq:**

#### Is FOR ALL ENTRIES always faster than SELECT in a LOOP?

**Answer:** Not always. With a handful of driver rows the difference is negligible, and a buffered table read inside a loop can be fine. The gain appears when the driver table is large and each read goes to the database. Measure with a trace before rewriting.

#### What happens if the driver table is empty?

**Answer:** The WHERE condition that references the driver table is dropped and the SELECT can return every row of the table. Always test that the driver table has entries before the statement runs.

#### Should I use a JOIN instead?

**Answer:** Often yes. If the data lives in the same database, a JOIN or a CDS view lets the database do the matching and avoids moving the driver table. FOR ALL ENTRIES is best when the driver data comes from somewhere else, such as an earlier application step.

#### Where do I get the per-statement time?

**Answer:** Use transaction ST05 (SQL trace) or SAT (runtime analysis). Both show the number of executions and the duration per statement, which are the figures this calculator needs.

## Search terms

abap select in loop performance, for all entries vs nested select, abap for all entries performance, abap database round trips, abap performance calculator, abap loop select alternative, abap runtime estimate, sap abap performance tuning

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json