ABAP SQL Anti-Patterns That Kill Your HANA Performance

ABAP programs written before HANA often carry patterns that were acceptable on Oracle or DB2 but severely degrade HANA performance. Understanding why these patterns are harmful — not just that they are — is what allows you to identify them in code review and explain them to developers who push back.

SELECT *: Never Do It

SELECT * retrieves all columns from a table and transfers them across the database interface to the application server. On a wide table, this transfers many columns the program will never use. On HANA's column store, this is particularly wasteful: column store is designed around the assumption that queries will access a small number of columns from many rows. SELECT * forces HANA to read and transfer every column, negating the column-store advantage.

The fix is always the same: select only the fields you actually use. In modern ABAP (7.40+), use inline declarations to keep the field list close to the SELECT statement:

SELECT matnr, maktx FROM makt INTO TABLE @DATA(materials) WHERE spras = 'EN'.

This is not premature optimisation. It is the minimum correct way to write a database query.

SELECT Inside a Loop

The single most destructive SQL anti-pattern in ABAP is the SELECT statement inside a LOOP...ENDLOOP block. This generates one database round-trip per iteration — the N+1 query problem. A loop over 10,000 records with a SELECT inside generates 10,001 database calls. On a database server with even 1ms network latency, this is 10 seconds of waiting before any computation happens.

LOOP AT lt_orders INTO ls_order. SELECT SINGLE * FROM ekko INTO ls_header WHERE ebeln = ls_order-ebeln. " process header ENDLOOP.

This pattern is almost always replaceable with a single SELECT using FOR ALL ENTRIES or a JOIN. The refactored version runs one database call regardless of how many records are in the loop.

FOR ALL ENTRIES Pitfalls

FOR ALL ENTRIES is ABAP's mechanism for selecting rows from one table based on the contents of an internal table. Used correctly, it replaces the SELECT-in-loop pattern. Used incorrectly, it introduces its own problems.

The empty table trap: if the driving internal table is empty, FOR ALL ENTRIES returns ALL rows from the database table — no WHERE condition is applied. This is one of the most dangerous behaviours in ABAP SQL. Always check that the driving table is not empty before a FOR ALL ENTRIES statement:

IF lt_orders IS NOT INITIAL. SELECT ebeln, lifnr FROM ekko INTO TABLE @DATA(lt_headers) FOR ALL ENTRIES IN @lt_orders WHERE ebeln = @lt_orders-ebeln. ENDIF.

Duplicates in the driving table: FOR ALL ENTRIES implicitly deduplicates the driving table when building the database query, but this means the result set may not contain one row per entry in the driving table if the driving table has duplicates. Sort and deduplicate the driving table before using it in FOR ALL ENTRIES.

Performance on very large driving tables: FOR ALL ENTRIES translates to an IN clause or a series of OR conditions at the database level. Very large driving tables (tens of thousands of entries) can generate queries the optimizer handles poorly. For very large sets, consider a temporary database table or a JOIN instead.

Missing Indexes and Non-SARGable Conditions

HANA's column store can scan large tables efficiently without indexes, but this does not mean indexes are irrelevant. For OLTP-style point lookups — fetching a single record by key — an index is still significantly faster than a full column scan.

More importantly, non-SARGable WHERE conditions (conditions that cannot use an index) force full scans. The most common non-SARGable patterns in ABAP SQL are: applying functions to indexed columns in the WHERE clause (WHERE UPPER(matnr) = 'MATERIAL'), using LIKE with a leading wildcard (WHERE matnr LIKE '%MAT%'), and using OR across different columns (WHERE matnr = 'A' OR lifnr = 'B').

Review slow queries in ST05 or the HANA SQL analyzer for full table scans on large tables. If the WHERE condition is non-SARGable, the fix is usually to restructure the condition or to pre-compute the comparison value in the application layer.

Implicit Client Handling

ABAP's database interface automatically adds a client (MANDT) condition to most queries, using the current logon client. This implicit client handling is correct for normal application queries but causes problems in two scenarios.

First, if you use native SQL (EXEC SQL) or AMDP, the implicit client handling is not applied. Your query runs without a MANDT filter and will return data from all clients. This is almost never intentional and is a serious data isolation issue in multi-client systems.

Second, if you use the CLIENT SPECIFIED addition to bypass implicit client handling, ensure the explicit MANDT condition in your WHERE clause is correct. Missing or incorrect explicit client conditions are a common source of cross-client data leakage.

HANA Pushdown and AMDP

HANA SQLScript executed directly on the database server is significantly faster for set-based operations than equivalent ABAP logic on the application server. The ABAP application server processes data one row at a time; HANA processes data as sets. For aggregations, complex joins, and analytical calculations over large datasets, the difference can be orders of magnitude.

ABAP Managed Database Procedures (AMDP) allow you to write SQLScript methods that execute on HANA and are called from ABAP. This is the preferred mechanism for performance-critical calculations that benefit from HANA pushdown.

When evaluating whether to use AMDP: if your ABAP logic reads a large dataset, loops over it, and produces an aggregated result, that logic is probably a good AMDP candidate. If it reads a small dataset and performs complex business logic with many conditional branches, ABAP is more maintainable.

Using the SQL Trace

Transaction ST05 is the primary tool for identifying SQL problems in a running ABAP program. Enable the SQL trace, run the program, disable the trace, and analyse the result.

What to look for: high-duration individual statements (indicates missing index or inefficient condition), very high statement counts (indicates SELECT-in-loop), and large rows-returned values for statements that should be selective (indicates missing WHERE condition or non-SARGable condition).

The HANA SQL analyzer provides deeper analysis: execution plans, column store vs row store operations, and HANA-specific statistics not available in ST05. For persistent performance problems, run the analysis in the HANA cockpit or via SQL: EXPLAIN PLAN FOR your-query.

> Editorial note: ABAP SQL behaviour on HANA continues to evolve. Features like AMDP and CDS views have expanded what is possible in database-layer processing. This guide applies to standard ABAP Open SQL; ABAP CDS view optimisation is a separate topic.