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 table1
- 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
execution plan
(ST05) which tells what the database does to optimize the reading. It's specific to each system. Note thatFOR ALL ENTRIES
is an ABAP construct which splits the SELECT into many SELECT, by splittingGIT_KUNNR_TAB
into groups of 5 lines (value of 5 modifiable in profile parametersrsdb/max_[in_]blocking_factor
or hintmax_[in_]blocking_factor
) – Sandra Rossi