Performance of Nested Loops

[Former Member]

This BLOG is an expanded version of a post that I made to the ABAP forum a while ago. What I wanted to show was that the effect that when performance tuning, the effect of nested loops can be far worse than poorly constructed SELECT statements.

I wrote a small program which illustrates this point. I’ve posted the code below, but in a nutshell, what it does is select a number of FI document headers and line items from BKPF and BSEG. The select statement from BSEG is very inefficient. I did it that way to prove a point. It then reads all records from both tables using a nested loop. Then it reads all records from both tables using a much more efficient method. Finally, it reads all records from both tables using a different method which may be even more efficient, but may be somewhat more difficult to program. It keeps track of the number of records and time taken for each operation.

I ran the program twice in our QA environment – once selecting a small amount of data and again selecting a moderate amount. The outputs are:

First run -

Time for unindexed select : 00:06:09
 Number of BKPF entries: 5,863
 Number of BSEG entries: 17,683

Time for nested loop : 00:00:53
 Number of BKPF reads : 5,863
 Number of BSEG reads : 17,683

Time for indexed loop : 00:00:00
 Number of BKPF reads : 5,863
 Number of BSEG reads : 17,683

Time for parallel cursor : 00:00:00
 Number of BKPF reads : 5,863
 Number of BSEG reads : 17,683

Second run –

Time for unindexed select : 00:21:07
 Number of BKPF entries: 55,777
 Number of BSEG entries: 205,285

Time for nested loop : 02:16:21
 Number of BKPF reads : 55,777
 Number of BSEG reads : 205,285

Time for indexed loop : 00:00:01
 Number of BKPF reads : 55,777
 Number of BSEG reads : 205,285

Time for parallel cursor : 00:00:01
 Number of BKPF reads : 55,777
 Number of BSEG reads : 205,285

So what can we conclude? In the first case, a gain in time of almost a minute is not much, but in a dialogue program, it would be worthwhile. But in the second case the other method gains over two hours. This would allow a program that has to run in the background to run in the foreground. The most striking thing to me though, is the fact that the nested loop takes substantially longer than an extremely inefficient select statement.

In both cases, the loop using the parallel cursor method did not produce a substantial savings over the loop using a binary search followed by an indexed read; however, if you run this and extract a very large amount of data, it does run more quickly. In the program I have provided, the parallel cursor method does appear to be somewhat easier to program, but I have found that if the outer loop does not contain all of the records in the inner loop, then programming complexity is increased and I don't think it warrants the extra effort. The binary search method is easy and runs quickly.

The select screen for the program is quite standard. The amount of data returned is determined by whatever the user enters and the programmer really has no control over it. So I’m suggesting that if you can’t guarantee that the amount of data is small, then you really ought to use the indexed read method.

Also note that the first select statement returns 17,683 rows from BSEG and took 06:09 to run and the second one returns 205,285 rows and takes 21:07. The second one retrieves almost 11.5 times as much data but takes only about 3.5 times as long to execute. The two runs of the program were on separate evenings when there shouldn’t be any load, so buffering and workload shouldn’t be issues.

So, my conclusion is: when tuning a program that you know will return a small amount of data, tune the select statement and don’t worry too much about loops; however, if the program may return a large amount of data, avoid nested loops.

And a final thought: once you get a program tuned to a certain point, it doesn't make a lot of sense to try to spend a lot of time trying to reduce execution time by a small amount. (More on this later)

Code follows:

report ztest_nested_loop. data: bkpf type bkpf, bseg type bseg. select-options: s_bukrs for bseg-bukrs memory id buk obligatory, s_gjahr for bseg-gjahr memory id gjr obligatory, s_lifnr for bseg-lifnr memory id lif obligatory. data: bkpf_tab type standard table of bkpf, bkpf_lin like line of bkpf_tab, bseg_tab type standard table of bseg, bseg_lin like line of bseg_tab. data: start_time type sy-uzeit, end_time type sy-uzeit, difference type sy-uzeit, bkpf_entries type sy-tabix, bseg_entries type sy-tabix, bkpf_reads type sy-tabix, bseg_reads type sy-tabix. start-of-selection. perform unindexed_select. perform nested_loop. perform indexed_loop. PERFORM parallel_cursor. *&---------------------------------------------------------------------* *& Form unindexed_select *&---------------------------------------------------------------------* form unindexed_select. get time field start_time. select * from bseg into table bseg_tab where bukrs in s_bukrs and gjahr in s_gjahr and lifnr in s_lifnr. if sy-subrc <> 0. message id '00' type 'E' number '001' with 'No entries selected'. endif. select * from bkpf into table bkpf_tab for all entries in bseg_tab where bukrs = bseg_tab-bukrs and belnr = bseg_tab-belnr and gjahr = bseg_tab-gjahr and bstat = space. clear bseg_tab. refresh bseg_tab. select * from bseg into table bseg_tab for all entries in bkpf_tab where bukrs = bkpf_tab-bukrs and belnr = bkpf_tab-belnr and gjahr = bkpf_tab-gjahr. get time field end_time. difference = end_time - start_time. describe table bkpf_tab lines bkpf_entries. describe table bseg_tab lines bseg_entries. write: /001 'Time for unindexed select:', difference, /005 'Number of BKPF entries:', bkpf_entries, /005 'Number of BSEG entries:', bseg_entries. skip 1. endform. " unindexed_select *&---------------------------------------------------------------------* *& Form nested_loop *&---------------------------------------------------------------------* form nested_loop. get time field start_time. loop at bkpf_tab into bkpf_lin. bkpf_reads = bkpf_reads + 1. loop at bseg_tab into bseg_lin where bukrs = bkpf_lin-bukrs and belnr = bkpf_lin-belnr and gjahr = bkpf_lin-gjahr. bseg_reads = bseg_reads + 1. endloop. endloop. get time field end_time. difference = end_time - start_time. write: /001 'Time for nested loop:', difference, /005 'Number of BKPF reads:', bkpf_reads, /005 'Number of BSEG reads:', bseg_reads. skip 1. endform. " nested_loop *&---------------------------------------------------------------------* *& Form indexed_loop *&---------------------------------------------------------------------* form indexed_loop. data: bkpf_index like sy-tabix, bseg_index like sy-tabix. clear: bkpf_reads, bseg_reads. get time field start_time. sort: bkpf_tab by bukrs belnr gjahr, bseg_tab by bukrs belnr gjahr. loop at bkpf_tab into bkpf_lin. read table bseg_tab into bseg_lin with key bukrs = bkpf_lin-bukrs belnr = bkpf_lin-belnr gjahr = bkpf_lin-gjahr binary search. bkpf_reads = bkpf_reads + 1. bseg_index = sy-tabix. while sy-subrc = 0. bseg_index = bseg_index + 1. bseg_reads = bseg_reads + 1. read table bseg_tab into bseg_lin index bseg_index. if bseg_lin-bukrs <> bkpf_lin-bukrs or bseg_lin-belnr <> bkpf_lin-belnr or bseg_lin-gjahr <> bkpf_lin-gjahr. sy-subrc = 99. else. endif. endwhile. endloop. get time field end_time. difference = end_time - start_time. write: /001 'Time for indexed loop:', difference, /005 'Number of BKPF reads:', bkpf_reads, /005 'Number of BSEG reads:', bseg_reads. skip 1. endform. " indexed_loop *&---------------------------------------------------------------------* *& Form parallel_cursor *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM parallel_cursor. DATA: bkpf_index LIKE sy-tabix, bseg_index LIKE sy-tabix. CLEAR: bkpf_reads, bseg_reads. GET TIME FIELD start_time. SORT: bkpf_tab BY bukrs belnr gjahr, bseg_tab BY bukrs belnr gjahr. bseg_index = 1. LOOP AT bkpf_tab INTO bkpf_lin. bkpf_reads = bkpf_reads + 1. LOOP AT bseg_tab INTO bseg_lin FROM bseg_index. IF bseg_lin-bukrs <> bkpf_lin-bukrs OR bseg_lin-belnr <> bkpf_lin-belnr OR bseg_lin-gjahr <> bkpf_lin-gjahr. bseg_index = sy-tabix. EXIT. ELSE. bseg_reads = bseg_reads + 1. ENDIF. ENDLOOP. ENDLOOP. GET TIME FIELD end_time. difference = end_time - start_time. WRITE: /001 'Time for parallel cursor :', difference, /005 'Number of BKPF reads :', bkpf_reads, /005 'Number of BSEG reads :', bseg_reads. ENDFORM. " parallel_cursor

More information can be found in SE30 (Tips and Tricks)