When developing SAP reports (programs) in the ABAP language, Open SQL queries are used to access the database. The syntax is very similar to SQL, but there are some differences. One of these differences is the possibility of using the FOR ALL ENTRIES IN construct. This construct is used in SELECT queries up to the WHERE statement. After it, an internal table with data is specified, the fields of which can be used in the WHERE operator as sampling conditions.

In this article I want to talk about the intricacies of this construction: what happens at the database level, query optimization, and database-hints .

---

Very often, programmers when writing code on ABAP use this construct. It is convenient, it saves time in development, but not everyone thinks about how it works. And one day, due to the increased volume of data, there comes a moment when the written program begins to “slow down”. Most often, a problem occurs in database queries, and the developer begins to optimize them: adds indexes, removes queries from loops, etc. But he almost never pays attention to the FOR ALL ENTRIES IN construct, since he believes that there is nothing to optimize in it.

What happens at the DB level

Let's analyze the work of this construction using a simple example. From the BKPF table, select 1000 rows in the LT_BKPF internal table, and then from the BSIS table, select the data using the FOR ALL ENTRIES IN LT_BKPF construction. A similar example is given in the official SAP help.
')

report z_test. "   " data: begin of ls_bkpf, bukrs type bkpf-bukrs, belnr type bkpf-belnr, end of ls_bkpf. data: lt_bkpf like table of ls_bkpf. data: lt_bsis like table of bsis. "     bkpf       lt_bkpf " select bukrs belnr up to 1000 rows from bkpf into corresponding fields of table lt_bkpf where gjahr = '2013'. check lines( lt_bkpf ) > 0. "     BSIS.  FOR ALL ENTRIES IN    LT_BKPF " select * from bsis into corresponding fields of table lt_bsis for all entries in lt_bkpf where bsis~bukrs = lt_bkpf-bukrs and bsis~belnr = lt_bkpf-belnr and bsis~gjahr = '2013'. 

To analyze the operation of the structure, we will use transaction ST05 - tracing SQL queries.

In one mode, run ST05 and turn on tracing (Fig. 1):


Fig. 1 Run tracing

In another mode we execute our program. After that, in ST05, turn off tracing and output the result (Fig. 2, Fig. 3, Fig. 4) by setting the filter on the BKPF and BSIS tables to weed out the garbage we do not need:


Fig. 2 Disable tracing


Fig. 3 Output trace result


Fig. 4 trace result

We see that we have completed one query and returned 1000 rows to the BKPF table - everything is fine here. But to the BSIS table, we have completed 100 queries, and even each of them contains 10 queries combined through the UNION ALL SELECT construct. This, in essence, means that one Open SQL query with FOR ALL ENTRIES IN turned into 1000 separate database queries during execution. If it is generic, how many entries are in the internal FOR ALL ENTRIES IN table, there will be as many separate queries to the database. It is clear that with a large amount of data it will all work very slowly.

We optimize

To increase performance, we will have to slightly complicate the code of our program:

 report z_test. "   " data: begin of ls_bkpf, bukrs type bkpf-bukrs, belnr type bkpf-belnr, end of ls_bkpf. data: lt_bkpf like table of ls_bkpf. data: lt_bkpf_tmp like lt_bkpf. field-symbols: <wa_bkpf> like ls_bkpf. data: lt_bsis like table of bsis. data: begin of ls_bukrs, bukrs type bukrs, end of ls_bukrs. data: lt_bukrs like table of ls_bukrs. "     BKPF       LT_BKPF " select bukrs belnr up to 1000 rows from bkpf into corresponding fields of table lt_bkpf where gjahr = '2013'. check lines( lt_bkpf ) > 0. "        LT_BUKRS " loop at lt_bkpf assigning <wa_bkpf>. ls_bukrs-bukrs = <wa_bkpf>-bukrs. collect ls_bukrs into lt_bukrs. endloop. "      " loop at lt_bukrs into ls_bukrs. "   LT_BKPF ,         LT_BKPF_TMP " clear lt_bkpf_tmp. loop at lt_bkpf assigning <wa_bkpf> where bukrs = ls_bukrs-bukrs. append <wa_bkpf> to lt_bkpf_tmp. endloop. "     BSIS.  FOR ALL ENTRIES IN    LT_BKPF_TMP " select * from bsis appending corresponding fields of table lt_bsis for all entries in lt_bkpf_tmp where bsis~bukrs = ls_bukrs-bukrs and bsis~belnr = lt_bkpf_tmp-belnr and bsis~gjahr = '2013'. endloop. 

I will paint in steps what we have done:

1. Analyzed the internal table LT_BKPF and realized that the values ​​in the BUKRS column are basically the same.
2. Save all unique values ​​of the BUKRS column in the internal table LT_BUKRS.
3. Redone the query to the BSIS table:
   
   • For each unique BUKRS value, we perform a separate query in a cycle, after preparing the internal table LT_BKPF_TMP with the document numbers for the specific BU (BUKRS). This table is passed to FOR ALL ENTRIES IN instead of LT_BKPF;
   • INTO CORRESPONDING FIELDS was replaced with APPENDING CORRESPONDING FIELDS, in order not to overwrite the internal table LT_BSIS at each step, but to add data to it;
     
   • In the WHERE block, only one field was left from the LT_BKPF_TMP table.
   
   

Let's see the trace result after this code modification (Fig. 5, Fig. 6):


Fig. 5 Trace result after code optimization


Fig. 6 Tracing result after code optimization (detailed)

We see that we managed to get rid of UNION ALL SELECT and now to the BSIS table we have 100 queries with the IN operator (10 values ​​in the query) instead of 1000 queries combined via UNION ALL SELECT.

When I optimized a similar query, the achieved result was not enough for me, and I decided to figure out why exactly 10 values ​​were passed to the IN operator. It turned out that this is governed by the SAP global setting parameter max_in_blocking_factor and affects all queries, but using so-called database-hints, you can change this setting for a specific query immediately before executing this query. To do this, you need to add % _hints db2 '& max_in_blocking_factor 500 &' or % _hints oracle '& max_in_blocking_factor 500 &' in the query itself, depending on the DBMS:

 select * from bsis appending corresponding fields of table lt_bsis for all entries in lt_bkpf_tmp where bsis~bukrs = ls_bukrs-bukrs and bsis~belnr = lt_bkpf_tmp-belnr and bsis~gjahr = '2013' %_hints db2 '&max_in_blocking_factor 500&'. 

After adding database-hints, we have the following result (Fig. 7):


Fig. 7 Trace result after adding database-hints

It can be seen that now for the BSIS table we have only 2 queries with the IN operator (500 values ​​in the query) instead of 1000 queries.

Attention! The max_in_blocking_factor setting needs to be changed carefully: the higher the value, the more RAM is required to store the result. In each particular case, an individual approach is needed to choose a middle ground between productivity and resource consumption.

Is it worth the sheepskin tanning?

In most cases, many small queries will take longer to complete than one large query. Even on our artificial example, where the sample is carried out by indexed fields, we received almost fourfold gain in execution speed. A particular performance gain is obtained if non-index fields that are included in the WHERE block are selected in the sample. In my practice, there were reports, the operation time of which was reduced from 50 minutes to 30 seconds without significant code correction and the addition of new indices.

We summarize

1. When using the FOR ALL ENTRIES IN LOCAL_TABLE construct, try to make sure that in the WHERE clause only one field is used from the internal table LOCAL_TABLE .
2. Always check that the LOCAL_TABLE table is not empty, otherwise conditions with fields in this table will be ignored.
3. Use database-hints very carefully.
4. Optimizing queries in this way makes sense only if any column in the internal table LOCAL_TABLE contains many duplicate values, otherwise there is no point.
5. Premature optimization is the root of all evil (c) Donald Knut. It is necessary to optimize only when it is really required, but it is better to know ways in advance.