If you've been writing ABAP for more than a few years, you probably have muscle memory around database access patterns that made perfect sense on Oracle or DB2. On HANA, some of those patterns are fine. Others will quietly destroy your application's performance in ways that only show up under production load. This article walks through 12 concrete ABAP SQL optimization patterns I've used on real S/4HANA systems — the kind of changes that turn a 45-second report into a 4-second one.
Before we start: if you haven't set up proper SQL tracing infrastructure, read about using ST05, SAT, and SQLM to find bottlenecks first. Optimizing without measurement is guesswork.
Why ABAP SQL Optimization on HANA Is Different
HANA stores data in columnar format in memory. That sounds like a silver bullet, but it only helps if your SQL is written to take advantage of it. The classic anti-patterns — fetching everything into an internal table and filtering in ABAP, doing SELECT inside a loop, avoiding aggregations — these punish you harder on HANA than they ever did on row-store databases, because HANA is optimized for doing heavy lifting inside the database engine, not in the application server.
The rule of thumb: push work down to HANA, not up to ABAP.
Pattern 1: Use SELECT with FIELDS Instead of SELECT *
This one is obvious but routinely ignored. Every column you fetch gets serialized, transferred over the network, and deserialized into your internal table. On a columnar store, reading only 3 columns from a 200-column table is dramatically cheaper than reading all 200.
" BAD - fetches all 200 columns
SELECT * FROM vbak INTO TABLE @DATA(lt_orders)
WHERE erdat = @lv_date.
" GOOD - fetch only what you need
SELECT vbeln, kunnr, netwr, waerk
FROM vbak
INTO TABLE @DATA(lt_orders)
WHERE erdat = @lv_date.On tables like BSEG with 300+ fields, the difference in runtime and memory consumption is enormous.
Pattern 2: Push Aggregations Into SQL
Stop fetching line items into ABAP and summing them with LOOP AT. HANA's columnar engine is built for aggregation — it can sum 10 million rows faster than your application server can loop over 10,000.
" BAD - aggregation in ABAP
SELECT vbeln, netwr FROM vbap
INTO TABLE @DATA(lt_items)
WHERE matnr = @lv_material.
DATA(lv_total) = REDUCE decfloat34(
INIT s = 0
FOR ls IN lt_items
NEXT s = s + ls-netwr ).
" GOOD - aggregation in SQL
SELECT SUM( netwr ) AS total_value
FROM vbap
INTO @DATA(lv_total)
WHERE matnr = @lv_material.Pattern 3: Avoid SELECT Inside Loops — Use JOINs
The SELECT-inside-loop is the single most common performance killer I see in ABAP code reviews. Every iteration fires a separate database round trip. On a 10,000-row loop that's 10,000 database calls.
" BAD - N+1 query pattern
LOOP AT lt_orders INTO DATA(ls_order).
SELECT SINGLE kunnr, name1
FROM kna1
INTO @DATA(ls_customer)
WHERE kunnr = @ls_order-kunnr.
ENDLOOP.
" GOOD - single JOIN
SELECT o~vbeln, o~kunnr, c~name1
FROM vbak AS o
INNER JOIN kna1 AS c ON c~kunnr = o~kunnr
INTO TABLE @DATA(lt_result)
WHERE o~erdat = @lv_date.Pattern 4: Use FOR ALL ENTRIES Correctly — or Replace It
FOR ALL ENTRIES can be useful, but it has traps: if your driver table is empty, it returns all records. It also deduplicates internally, which can cause subtle bugs. On HANA, a well-written JOIN or a subquery often outperforms FOR ALL ENTRIES.
" If you MUST use FOR ALL ENTRIES
IF lt_orders IS NOT INITIAL. " Always check!
SELECT vbeln, posnr, matnr, netwr
FROM vbap
INTO TABLE @DATA(lt_items)
FOR ALL ENTRIES IN @lt_orders
WHERE vbeln = @lt_orders-vbeln.
ENDIF.
" Consider a subquery instead
SELECT vbeln, posnr, matnr, netwr
FROM vbap
INTO TABLE @DATA(lt_items)
WHERE vbeln IN (
SELECT vbeln FROM vbak
WHERE erdat = @lv_date
AND vkorg = @lv_org
).Pattern 5: Filter Early With WHERE, Not Late in ABAP
Every row you pull into ABAP that you later filter out with a CHECK or IF in a loop is wasted I/O, network, and memory. Move those conditions into the WHERE clause.
" BAD - filter happens in ABAP after full table read
SELECT * FROM vbak INTO TABLE @DATA(lt_all).
LOOP AT lt_all INTO DATA(ls_order)
WHERE auart = 'ZOR' AND vkorg = '1000'.
" process
ENDLOOP.
" GOOD - filter at database level
SELECT vbeln, kunnr, netwr
FROM vbak
INTO TABLE @DATA(lt_filtered)
WHERE auart = 'ZOR'
AND vkorg = '1000'.Pattern 6: Leverage HANA-Specific SQL Functions
Modern Open SQL (ABAP 7.50+) exposes many HANA functions directly. Instead of fetching a date field and doing date math in ABAP, do it in the query.
SELECT vbeln,
erdat,
DAYS_BETWEEN( erdat, @sy-datum ) AS age_days
FROM vbak
INTO TABLE @DATA(lt_result)
WHERE DAYS_BETWEEN( erdat, @sy-datum ) <= 30.Functions like COALESCE, CASE, SUBSTRING, and UPPER are all available. Use them instead of post-processing in ABAP.
Pattern 7: Use DISTINCT Carefully
DISTINCT forces a sort and deduplication pass. On HANA it's fast, but it's not free. If you're using DISTINCT to compensate for a missing GROUP BY or a JOIN that's producing duplicates, fix the root cause instead.
" Is DISTINCT hiding a join problem?
SELECT DISTINCT a~kunnr
FROM vbak AS a
INNER JOIN vbap AS b ON b~vbeln = a~vbeln
INTO TABLE @DATA(lt_customers)
WHERE b~matnr = @lv_material.
" Better: use EXISTS or a subquery
SELECT kunnr
FROM vbak
INTO TABLE @DATA(lt_customers)
WHERE vbeln IN (
SELECT vbeln FROM vbap WHERE matnr = @lv_material
).Pattern 8: Use PACKAGE SIZE for Large Data Volumes
When you genuinely need to process millions of rows, don't fetch them all into one giant internal table. Use PACKAGE SIZE to process in chunks. This keeps memory consumption flat and lets you start processing while the database is still reading.
SELECT vbeln, kunnr, netwr
FROM vbak
INTO TABLE @DATA(lt_package) PACKAGE SIZE 10000
WHERE erdat BETWEEN @lv_from AND @lv_to.
" Process lt_package here
LOOP AT lt_package INTO DATA(ls_row).
" your logic
ENDLOOP.
ENDSELECT.Pattern 9: Sort in SQL, Not in ABAP
If you need sorted output, let the database engine do it. SORT lt_table BY field after a SELECT is an extra pass over data you already fetched. Add ORDER BY to your SELECT instead — or better, rely on a CDS view's defined order.
" BAD
SELECT vbeln, erdat, netwr FROM vbak
INTO TABLE @DATA(lt_orders)
WHERE vkorg = '1000'.
SORT lt_orders BY erdat DESCENDING.
" GOOD
SELECT vbeln, erdat, netwr FROM vbak
INTO TABLE @DATA(lt_orders)
WHERE vkorg = '1000'
ORDER BY erdat DESCENDING.Pattern 10: Use Window Functions for Ranking and Running Totals
This is one of the most underused features in modern Open SQL. Window functions let you compute rankings, running totals, and lead/lag comparisons entirely in SQL — no ABAP loop gymnastics needed.
" Rank customers by total order value
SELECT kunnr,
SUM( netwr ) AS total_value,
RANK() OVER( ORDER BY SUM( netwr ) DESCENDING ) AS rnk
FROM vbak
INTO TABLE @DATA(lt_ranked)
WHERE vkorg = '1000'
GROUP BY kunnr
HAVING SUM( netwr ) > 0
ORDER BY rnk.Pattern 11: Avoid Implicit Client Handling — Be Explicit
ABAP Open SQL handles client automatically, which is convenient but can work against you in multi-client systems or when you need to optimize cross-client queries. More importantly, know when you're accidentally doing a full-table scan by omitting client in a raw ADBC call.
For standard Open SQL this is handled for you, but be careful with AMDP methods and native SQL where you must handle client explicitly. Missing the client column filter in an AMDP can silently scan all clients.
Pattern 12: Use CDS Views for Complex, Reusable Query Logic
This is the architectural pattern that ties everything together. Complex joins, aggregations, and business logic that you're currently duplicating across multiple ABAP programs belong in a CDS view. The CDS engine on HANA is optimized in ways that runtime-generated Open SQL sometimes isn't.
" Instead of this complex inline SQL in 5 different programs:
SELECT a~vbeln, a~kunnr, SUM( b~netwr ) AS order_value
FROM vbak AS a
INNER JOIN vbap AS b ON b~vbeln = a~vbeln
INTO TABLE @DATA(lt_result)
WHERE a~vkorg = '1000'
GROUP BY a~vbeln, a~kunnr.
" Define a CDS view I_SalesOrderValueByCustomer
" then simply:
SELECT vbeln, kunnr, order_value
FROM i_salesordervaluebycustomer
INTO TABLE @DATA(lt_result)
WHERE vkorg = '1000'.If you want to go deeper on where CDS query patterns go wrong, the article on CDS views performance anti-patterns covers the mistakes I see most often in code reviews.
Putting It All Together: A Realistic Refactoring
Let me show you what these patterns look like when applied together. Here's a typical legacy report opening query:
" BEFORE - classic legacy pattern
SELECT * FROM vbak INTO TABLE @DATA(lt_orders).
LOOP AT lt_orders INTO DATA(ls_order)
WHERE erdat >= lv_from AND erdat <= lv_to.
SELECT * FROM vbap INTO TABLE @DATA(lt_items)
WHERE vbeln = ls_order-vbeln.
LOOP AT lt_items INTO DATA(ls_item).
lv_total = lv_total + ls_item-netwr.
ENDLOOP.
ENDLOOP." AFTER - HANA-optimized
SELECT a~vbeln,
a~kunnr,
a~erdat,
SUM( b~netwr ) AS order_value,
COUNT( * ) AS item_count
FROM vbak AS a
INNER JOIN vbap AS b ON b~vbeln = a~vbeln
INTO TABLE @DATA(lt_result)
WHERE a~erdat BETWEEN @lv_from AND @lv_to
GROUP BY a~vbeln, a~kunnr, a~erdat
ORDER BY a~erdat DESCENDING.Same business result. One database call instead of N+1. Aggregation done by HANA, not ABAP. Only the columns you actually need.
Measuring the Impact
None of this matters if you can't measure it. Use ST05 to capture SQL traces before and after your changes. Look at execution count, total duration, and rows transferred — not just runtime. A query that runs in 100ms but transfers 2 million rows is still a problem waiting to happen.
For production monitoring, SQLM gives you aggregated SQL statistics across all users. Combined with the performance tooling covered in the ST05/SAT/SQLM bottleneck guide, you have everything you need to find and fix the worst offenders systematically.
If your queries are exposed through CDS views, also check the CDS buffering and query tuning series for view-level optimization strategies, and review the CDS annotations cheatsheet for performance-relevant annotation options.
Final Thoughts
HANA is genuinely fast, but it rewards SQL that's written with its columnar, in-memory architecture in mind. These 12 patterns aren't exotic — they're the practical baseline every ABAP developer should be applying by default in any S/4HANA system.
Start with patterns 1, 2, and 3. They cover 80% of the performance wins I've seen in real projects. Then layer in the rest as you identify specific bottlenecks through your profiling tools. Measure before, measure after, and let the numbers tell the story.
