🚀 SAP ABAP Mock Interview – Question 4/50

 

What is the performance issue?

The main issue is SELECT inside LOOP, commonly known as the SELECT N+1 Problem.

Here's why it's expensive:

• First, one query retrieves all records from VBAP.
• Then, for every record in the internal table, SAP performs another database call to MAKT.

Example:

• 10,000 VBAP records
• 1 SELECT on VBAP
• 10,000 SELECT SINGLE statements on MAKT
• Total = 10,001 database calls

Since database access is one of the most expensive operations in ABAP, this pattern can severely impact performance.

2️⃣ What is this anti-pattern called?

It is commonly known as:

• ✅ SELECT inside LOOP
• ✅ N+1 Query Problem (or SELECT N+1 Problem)

This is one of the most frequently asked ABAP interview questions.

3️⃣ How would you optimize it?

My first choice would always be to check whether the requirement can be solved using a JOIN.

In S/4HANA, SAP recommends pushing data-intensive processing to the database whenever possible.

SELECT
    a~vbeln,
    a~posnr,
    a~matnr,
    b~maktx
FROM vbap AS a
INNER JOIN makt AS b
ON b~matnr = a~matnr
AND b~spras = @sy-langu
WHERE a~vbeln IN @s_vbeln
INTO TABLE @DATA(lt_result).

Advantages

✅ Only one database round trip

✅ HANA optimized

✅ Less ABAP memory

✅ Cleaner code

✅ Easier maintenance

4️⃣ Give at least two optimized solutions

✅ Solution 1 — INNER JOIN (Preferred)

Best whenever the required data exists in related tables.

✅ Solution 2 — FOR ALL ENTRIES

SELECT vbeln posnr matnr
FROM vbap
INTO TABLE @DATA(lt_vbap)
WHERE vbeln IN @s_vbeln.

IF lt_vbap IS NOT INITIAL.

  SELECT matnr maktx
  FROM makt
  INTO TABLE @DATA(lt_makt)
  FOR ALL ENTRIES IN @lt_vbap
  WHERE matnr = @lt_vbap-matnr
    AND spras = @sy-langu.

ENDIF.

Then either:

• Store lt_makt in a hashed table and perform O(1) lookups, or
• Sort it once and use BINARY SEARCH.

Senior tip: A hashed table is generally preferable here because you're performing key-based lookups and don't need sorting.

✅ Solution 3 — Use a Hashed Internal Table

DATA lt_makt TYPE HASHED TABLE OF makt
             WITH UNIQUE KEY matnr spras.

Then simply:

READ TABLE lt_makt
WITH TABLE KEY
     matnr = <fs_vbap>-matnr
     spras = sy-langu
ASSIGNING <fs_makt>.

No sorting.

No binary search.

Constant-time lookup.

✅ Solution 4 — CDS View (S/4HANA)

In modern ABAP, this logic can also be modeled in a CDS View.

Benefits include:

• Pushes joins to the database
• Better readability
• Reusable across applications
• Optimized by the HANA optimizer

For new developments in S/4HANA, CDS is often the preferred architectural approach.

🎯 Senior-Level Interview Insight

Before writing any code, ask yourself:

"Can this be solved in a single SQL statement?"

If yes, use a JOIN.

If not, consider:

• FOR ALL ENTRIES
• CDS Views
• AMDP (for complex calculations)
• Internal table buffering (hashed/sorted tables)

The goal is always to minimize database round trips.

💡 Bonus Question

Suppose this condition is accidentally removed:

AND b~spras = @sy-langu

What could happen?

Answer

Since MAKT stores material descriptions in multiple languages, each material may have multiple entries.

Without filtering on language:

• The JOIN returns multiple rows per material
• Duplicate VBAP records appear
• Result set becomes much larger than expected
• Reports show duplicate line items
• Performance degrades due to unnecessary data retrieval

This is a common source of bugs in SAP reports.

⭐ Interview Follow-up

A senior interviewer might also ask:

"Would you always replace SELECT SINGLE inside a loop with a JOIN?"

A strong answer is:

"Not always. If the table is fully buffered and the number of loop iterations is very small, SELECT SINGLE may be acceptable. However, for large datasets or non-buffered tables, a JOIN or FOR ALL ENTRIES is generally the better choice. The decision should be based on table buffering, expected data volume, and performance analysis (e.g., using ST05 or SAT), rather than following a rule blindly."

This balanced answer demonstrates practical experience rather than relying on a blanket rule.