Slow SELECT FOR ALL ENTRIES

Question

The below SELECT runs with the internal table GIT_KUNNR_TAB containing 2.291.000 lines with unique clients (kunnr) and takes 16 minutes to complete.

  select kunnr umsks umskz gjahr belnr buzei bschl shkzg dmbtr bldat
           zfbdt zbd1t zbd2t zbd3t rebzg rebzj rebzz rebzt
        into corresponding fields of table git_oi_tab
      from bsid
        for all entries in git_kunnr_tab
      where bukrs  =  p_bukrs
        and kunnr  =  git_kunnr_tab-kunnr 
        and umsks  = ' '
        and augdt  =   clear_augdt
        and budat  le  p_key 
        and blart  in  s_blart
        and xref3  in  s_xref3.

BSID contains in total 20.000.000 records and for the 2.291.000 unique clients it gets 445.000 records from BSID.

Most of the time there are even more lines in GIT_KUNNR_TAB.

Is there any quicker selection?

For any performance question, you should better attach the execution plan (ST05) which tells what the database does to optimize the reading. It's specific to each system. Note that FOR ALL ENTRIES is an ABAP construct which splits the SELECT into many SELECT, by splitting GIT_KUNNR_TAB into groups of 5 lines (value of 5 modifiable in profile parameters rsdb/max_[in_]blocking_factor or hint max_[in_]blocking_factor) — Sandra Rossi
@SandraRossi 5 is only true for Oracle as a default, it is 50 on DB2, and 100 on HANA. — András
@András the value may even vary based on database, SAP version, and WHERE (currently on HANA, it's 50 or 100 depending on the WHERE) — Sandra Rossi
@SandraRossi, I know, but I did not want to list all cases and exceptions in a comment — András

András András · Accepted Answer · 2019-04-22T13:38:09

Drop the FOR ALL ENTRIES part

Most likely the rest of the WHERE condition is selective enough. You get back more records than necessary, but much quicker.

As git_kunnr_tab is unique, you can turn it into a HASHED table, and filter git_oi_tab with that on the application server.

SELECT kunnr umsks umskz gjahr belnr buzei bschl shkzg dmbtr bldat
       zfbdt zbd1t zbd2t zbd3t rebzg rebzj rebzz rebzt
    INTO corresponding fields of table git_oi_tab
    FROM bsid
    WHERE bukrs = p_bukrs 
      AND umsks = ' ' 
      AND augdt = clear_augdt 
      AND budat le p_key 
      AND blart in s_blart 
      AND xref3 in s_xref3.

DATA: lt_kunnr_tab TYPE HASHED TABLE of <type of git_kunnr_tab>
          WITH UNIQE KEY kunnr.
lt_kunnr_tab = git_kunnr_tab.
LOOP AT git_oi_tab ASSIGNING FIELD-SYMBOL(<fs_oi>).
  READ TABLE lt_kunnr_tab TRANSPORTING NO FIELDS
      WITH KEY kunnr = <fs_oi>-kunnr.
    IF sy-subrc <> 0
      DELETE git_oi_tab.
    ENDIF.
  ENDIF.
ENDLOOP.
FREE lt_kunnr_tab.

This is not a general solution

If the FAE driver table contains more than 20% of the rows of the target table, dropping it completely is mostly beneficial for speed.
If it has less rows, FAE is the better solution.

Be careful however, dropping FAE can significantly increase the memory consumption of the resulting internal table!

FOR ALL ENTRIES vs Range table

You can see many places in the internet that Range tables are faster than FAE. This is true in some very specific cases:

Only one field is used from the FAE driver table¹
There are more rows in the driver table than FAE sends down in one batch
By default the batch size is 5 in Oracle, 50 in DB2, 100 in HANA
There are not so many rows in the Range thable that it causes a dump
The maximum length is 1 048 576 bytes (note 1002491)

Range tables can be faster than FAE because it sends down all the filtering conditions in one query. This is of course dangerous, as the size of a query is limited. If it exceeds the set limit, you get a dump.

However, using the hint MAX_IN_BLOCKING_FACTOR and MAX_BLOCKING_FACTOR you can give FAE all the benefits of range tables, without its downsides by increasing the batch size.

So only use Range tables with actual ranges, like between A and C, or not between G and J.

this one is not about speed, but functional correctness. Range tables treat fields independently, while FAE works with rows

Slow SELECT FOR ALL ENTRIES

2 Answers

Drop the FOR ALL ENTRIES part

This is not a general solution

FOR ALL ENTRIES vs Range table