The use of FOR ALL ENTRIES in Open SQL of ABAP has been used for very long time. And it is helpful for report data from different table.
Nevertheless, FOR ALL ENTRIES has two fallback.
1. It should check whether the SQL condition internal table is initial before check. Or else, ABAP will have very big performance issue since it will read all the data from one table.
2. If SQL condition internal table has too many entries, the performance is also very bad since ABAP will convert the FOR ALL ENTRIES.
One interesting new feature from ABAP 7.52 can fix these two fallback.
I have check the performance of these different ways of SQL. Three ways we compare are as below.
- FOR ALL ENTRIES
- Open SQL use internal table as data source.
SELECT item~rbukrs, item~gjahr, item~belnr FROM acdoca AS item INNER JOIN @lt_bkpf AS head
ON item~rldnr = '0L'
AND item~rbukrs = head~bukrs
AND item~gjahr = head~gjahr
AND item~belnr = head~gjahr
INTO TABLE @lt_acdoca BYPASSING BUFFER.
- Open SQL use internal table as data source, but the internal table is sorted table
TYPES tt_bkpf_sort TYPE SORTED TABLE OF ts_bkpf WITH UNIQUE KEY bukrs gjahr belnr.
DATA lt_bkpf_sort TYPE tt_bkpf_sort.
SORT lt_bkpf BY bukrs gjahr belnr.
lt_bkpf_sort = CORRESPONDING #( lt_bkpf ).
SELECT item~rbukrs, item~gjahr, item~belnr
FROM acdoca AS item INNER JOIN @lt_bkpf_sort AS head
ON item~rldnr = '0L'
AND item~rbukrs = head~bukrs
AND item~gjahr = head~gjahr
AND item~belnr = head~gjahr
INTO TABLE @data(lt_acdoca) BYPASSING BUFFER.
Based on S4HANA 1709. Three ways running time is as below.
From this chart, we can have conclusions as below:
1. While SQL condition size not big, the running times do not have big difference.
2. While SQL condition size is bigger ( > 10 000 ), FOR ALL ENTRIES will use more time than other two SQL. It will have worse performance.
3. While the SQL size grows, the running time of FOR ALL ENTRIES will grow greatly, its performance will be much worse.
Since FOR ALL ENTRIES has much difference, we can compare the other solutions.
From this chart, we can get several conclusions.
1. the running time of Open SQL will also grows together with size of internal table of Open SQL data source.
2. Although there is not big difference between these two solution, actually the internal table with sorted key is better.
3. Nevertheless, extended check ( SLIN ) will require the data source of open internal table with certain key fields.
From these testing, we can make a conclusion. If you are programming in higher version of ABAP 7.52, higher than 1709, you can use alternative solution than FOR ALL ENTRIES in report logic.
For reference, the performance testing program.
REPORT <Report name>.
PARAMETERS p_rows TYPE i DEFAULT 1000.
START-OF-SELECTION.
SELECT * FROM bkpf UP TO @p_rows ROWS INTO TABLE @DATA(lt_bkpf).
*option 3 from sortale tables
TYPES: BEGIN OF ts_bkpf,
bukrs TYPE bkpf-bukrs,
gjahr TYPE bkpf-gjahr,
belnr TYPE bkpf-belnr,
END OF ts_bkpf
t_bkpf_sort TYPE SORTED TABLE OF ts_bkpf WITH UNIQUE KEY bukrs gjahr belnr.
DATA lt_bkpf_sort TYPE tt_bkpf_sort.
SORT lt_bkpf BY bukrs gjahr belnr.
lt_bkpf_sort = CORRESPONDING #( lt_bkpf ).
GET RUN TIME FIELD data(lv_start).
SELECT item~rbukrs, item~gjahr, item~belnr
FROM acdoca AS item INNER JOIN @lt_bkpf_sort AS head
ON item~rldnr = '0L'
AND item~rbukrs = head~bukrs
AND item~gjahr = head~gja .
TYPES thr
AND item~belnr = head~gjahr
INTO TABLE @data(lt_acdoca) BYPASSING BUFFER.
GET RUN TIME FIELD data(lv_end).
data(lv_dura) = lv_end - lv_start.
WRITE: / 'time for sorted interal table as Open SQL data source', lv_dura.
" Option 2 from internal table
GET RUN TIME FIELD lv_start.
SELECT item~rbukrs, item~gjahr, item~belnr FROM acdoca AS item INNER JOIN @lt_bkpf AS head
ON item~rldnr = '0L'
AND item~rbukrs = head~bukrs
AND item~gjahr = head~gjahr
AND item~belnr = head~gjahr
INTO TABLE @lt_acdoca BYPASSING BUFFER.
GET RUN TIME FIELD lv_end.
lv_dura = lv_end - lv_start.
WRITE: / 'time for interal table as Open SQL data source', lv_dura.
" opiton 1 FOR ALL entries
IF lt_bkpf IS NOT INITIAL.
GET RUN TIME FIELD lv_start.
SELECT rbukrs, gjahr, belnr FROM acdoca
INTO TABLE @lt_acdoca BYPASSING BUFFER
FOR ALL ENTRIES IN @lt_bkpf
WHERE rldnr = '0L'
AND rbukrs = @lt_bkpf-bukrs
AND gjahr = @lt_bkpf-gjahr
AND belnr = @lt_bkpf-belnr
.
GET RUN TIME FIELD lv_end.
ENDIF.
lv_dura = lv_end - lv_start.
WRITE: / 'time for all entries', lv_dura.
Hi.
ReplyDeletePlease correct the program as I see you are using BELNR = GJAHR on the inner join selects. Please correct that and run as I see Inner join takes more time than for all entries.
Thanks for short demo on the for all entries vs Inner join. But please check and post back with your results again. The first two selects were not fetching any data. Therefore the run time for inner joins are short.
ReplyDelete