Posted: Sat Sep 22, 2007 12:33 pm Post subject: Performance of Nested Loops
Performance - what will kill you and what will leave you with only a flesh wound
Rob Burbank
Company: The University of Toronto
Posted on Nov. 16, 2006 12:23 PM in ABAP
In the Performance of Nested Loops, I showed that nested loops can give far worse performance than a poorly constructed SELECT statement. In Using an Index When You Don't Have all of the Fields, I showed some tricks that will allow you to use an index. In this blog I will look at some other performance tuning tips to see how they stack up against these two.
I wrote four programs to examine this. Each one provides statistics at the end to show how long portions of it took. Each one also includes a small report (that is commented out) that you can produce to ensure that it produces the same report as the other programs. Before you look at them, I should point out some important caveats:
I'm not concerned with making sure that range tables are not empty before doing a SELECT. I consider this to be a logic error and out of the scope of this blog. The same goes for internal tables used in SELECT ... FOR ALL ENTRIES.
I didn't consider badly constructed JOINS (joins on non key fields). I think this is just a subset of not using an index.
I didn't look at aggregate functions.
I didn't consider nested calls to RFC enabled function modules. This can actually be a serious problem, but it's one that is not mentioned in the forums, so although it can cause bad performance, it doesn't seem to occur very frequently.
Although there has been discussion in the forums about which is better - joins or FOR ALL ENTRIES, I'm not trying to pick a winner here either. My own testing (which may form a later blog) shows inconsistent results.
I also didn't consider READ statements that don't use a binary search. They are similar to LOOP/EXIT/ENDLOOP.
If you decide to run these programs, buffering will definitely be an issue. I ran all four programs overnight (one program per night) when there would be little other activity and without the effects of hardware buffering.
The first two programs may run for a very long time.
The first program disobeys a number of performance rules and is presented below:
data: bkpf type bkpf,
bseg type bseg,
lfa1 type lfa1,
ekko type ekko,
ekpo type ekpo.
select-options: s_lifnr for bseg-lifnr memory id lif obligatory.
types: begin of fi_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
lifnr type bseg-lifnr,
ebeln type bseg-ebeln,
ebelp type bseg-ebelp,
budat type bkpf-budat,
waers type bkpf-waers,
blart type bkpf-blart,
usnam type bkpf-usnam,
cpudt type bkpf-cpudt,
cputm type bkpf-cputm,
name1 type kna1-name1,
end of fi_tab.
types: begin of po_tab,
ebeln type ekpo-ebeln,
ebelp type ekpo-ebelp,
loekz type ekko-loekz,
lifnr type ekko-lifnr,
aedat type ekko-aedat,
ernam type ekko-ernam,
loekz1 type ekpo-loekz,
menge type ekpo-menge,
netwr type ekpo-netwr,
end of po_tab.
types: begin of merge_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
name1 type kna1-name1,
ebeln type ekko-ebeln,
lifnr type ekko-lifnr,
ernam type ekko-ernam,
waers type bkpf-waers,
curr(20),
end of merge_tab.
data: fi_int type table of fi_tab,
fi_wa type fi_tab,
po_int type table of po_tab,
po_wa type po_tab,
merge_int type table of merge_tab,
merge_wa type merge_tab,
copy_int type table of merge_tab,
copy_wa type merge_tab.
data: pgm_start type sy-uzeit,
pgm_end type sy-uzeit,
sel1_start type sy-uzeit,
sel1_end type sy-uzeit,
sel2_start type sy-uzeit,
sel2_end type sy-uzeit,
merge_start type sy-uzeit,
merge_end type sy-uzeit,
dif1 type i,
dif2 type i,
dif3 type i,
dif4 type i,
no_lines type i.
get time field pgm_end.
dif4 = pgm_end - pgm_start.
perform write_statistics.
top-of-page.
perform write_heading.
*&---------------------------------------------------------------------*
*& Form init_parm
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form init_parm .
endform. " init_parm
*&---------------------------------------------------------------------*
*& Form get_fi
*&---------------------------------------------------------------------*
* Possible sources of poor performance in this FORM:
* - Nested SELECTS
* - SELECT *
* - MOVE-CORRESPONDING
*----------------------------------------------------------------------*
form get_fi .
get time field sel1_start.
select *
from bseg
where gjahr > '2001'
and lifnr in s_lifnr
and koart = 'K'.
move-corresponding bseg to fi_wa.
select single *
from bkpf
where bukrs = bseg-bukrs
and belnr = bseg-belnr
and gjahr = bseg-gjahr.
if sy-subrc = 0.
move-corresponding bkpf to fi_wa.
endif.
select single *
from lfa1
where lifnr = bseg-lifnr.
if sy-subrc = 0.
move-corresponding lfa1 to fi_wa.
endif.
append fi_wa to fi_int.
endselect.
if sy-subrc <> 0.
message e001 with 'No FI data selected'.
endif.
sort fi_int by bukrs belnr gjahr.
get time field sel1_end.
dif1 = sel1_end - sel1_start.
endform. " get_fi
*&---------------------------------------------------------------------*
*& Form get_po
*&---------------------------------------------------------------------*
* Possible sources of poor performance in this FORM:
* - Nested SELECT
*----------------------------------------------------------------------*
form get_po .
get time field sel2_start.
select * from ekko
where lifnr in s_lifnr.
move-corresponding ekko to po_wa.
select * from ekpo
where ebeln = ekko-ebeln.
move-corresponding ekpo to po_wa.
po_wa-loekz1 = ekpo-loekz.
po_wa-aedat = ekko-aedat.
append po_wa to po_int.
endselect.
endselect.
if sy-subrc <> 0.
message e001 with 'No PO data selected'.
endif.
get time field sel2_end.
dif2 = sel2_end - sel2_start.
endform. " get_po
*&---------------------------------------------------------------------*
*& Form merge_data
*&---------------------------------------------------------------------*
* Possible sources of poor performance in this FORM:
* - Using LOOP AT <itab> without ASSIGNING
* - IF/ELSEIF
* - Nested LOOPs
*----------------------------------------------------------------------*
form merge_data .
sort: po_int by lifnr ernam aedat,
fi_int by lifnr usnam cpudt.
get time field merge_start.
loop at po_int into po_wa.
loop at fi_int into fi_wa where
lifnr = po_wa-lifnr and
usnam = po_wa-ernam and
cpudt = po_wa-aedat.
move-corresponding fi_wa to merge_wa.
move-corresponding po_wa to merge_wa.
append merge_wa to merge_int.
endloop.
endloop.
get time field merge_end.
dif3 = merge_end - merge_start.
endform. " merge_data
*&---------------------------------------------------------------------*
*& Form copy_itab
*&---------------------------------------------------------------------*
* Possible sources of poor performance in this FORM:
* - LOOP AT <itab> APPEND ENDLOOP
*----------------------------------------------------------------------*
form copy_itab .
loop at merge_int into merge_wa.
move-corresponding merge_wa to copy_wa.
append copy_wa to copy_int.
endloop.
delete adjacent duplicates from copy_int
comparing bukrs belnr gjahr ebeln.
endform. " copy_itab
*&---------------------------------------------------------------------*
*& Form write_heading
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_heading .
*&---------------------------------------------------------------------*
*& Form write_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_data .
loop at copy_int into copy_wa.
write: / copy_wa-bukrs under 'CoCd',
copy_wa-belnr under 'Doc No',
copy_wa-gjahr under 'FYr',
copy_wa-ebeln under 'PONo',
copy_wa-lifnr under 'Ven No',
copy_wa-name1 under 'Vendor Name',
copy_wa-ernam under 'UserID',
copy_wa-curr under 'Currency'.
endloop.
if sy-subrc = 0.
skip 1.
write: /001 'Number of rows selected: ', no_lines.
else.
write: /001 'No data selected'.
endif.
endform. " write_data
*&---------------------------------------------------------------------*
*& Form write_statistics
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_statistics .
describe table fi_int lines no_lines.
write: /001 'Number of FI records selected :', no_lines.
describe table po_int lines no_lines.
write: /001 'Number of PO records selected :', no_lines.
describe table copy_int lines no_lines.
write: /001 'Number of merged records :', no_lines.
skip 1.
write: /001 'Time for unoptimized select on FI data :',
dif1, 'seconds'.
write: /001 'Time for unoptimized select on PO data :',
dif2, 'seconds'.
write: /001 'Time for unoptimized merge of data :',
dif3, 'seconds'.
write: /001 'Time for unoptimized program :',
dif4, 'seconds'.
endform. " write_statistics
The second program uses performance tuning techniques that are commonly mentioned in the ABAP forum - such things as avoiding the SELECT/ENDSELECT construct; avoiding MOVE-CORRESPONDING; etc. I did not use every trick in the book, but I tried to use some of the more commonly mentioned techniques. Here is that program:
data: bkpf type bkpf,
bseg type bseg,
lfa1 type lfa1,
ekko type ekko,
ekpo type ekpo.
select-options: s_lifnr for bseg-lifnr memory id lif obligatory.
types: begin of fi_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
lifnr type bseg-lifnr,
ebeln type bseg-ebeln,
ebelp type bseg-ebelp,
budat type bkpf-budat,
waers type bkpf-waers,
blart type bkpf-blart,
usnam type bkpf-usnam,
cpudt type bkpf-cpudt,
cputm type bkpf-cputm,
name1 type kna1-name1,
end of fi_tab.
types: begin of po_tab,
ebeln type ekpo-ebeln,
ebelp type ekpo-ebelp,
loekz type ekko-loekz,
lifnr type ekko-lifnr,
aedat type ekko-aedat,
ernam type ekko-ernam,
loekz1 type ekpo-loekz,
menge type ekpo-menge,
netwr type ekpo-netwr,
end of po_tab.
types: begin of merge_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
name1 type kna1-name1,
ebeln type ekko-ebeln,
lifnr type ekko-lifnr,
ernam type ekko-ernam,
waers type bkpf-waers,
curr(20),
end of merge_tab.
types: begin of bseg_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
lifnr type bseg-lifnr,
ebeln type bseg-ebeln,
ebelp type bseg-ebelp,
end of bseg_tab.
types: begin of bkpf_tab,
bukrs type bkpf-bukrs,
belnr type bkpf-belnr,
gjahr type bkpf-gjahr,
budat type bkpf-budat,
waers type bkpf-waers,
blart type bkpf-blart,
usnam type bkpf-usnam,
cpudt type bkpf-cpudt,
cputm type bkpf-cputm,
end of bkpf_tab.
types: begin of lfa1_tab,
lifnr type kna1-lifnr,
name1 type kna1-name1,
end of lfa1_tab.
data: fi_int type table of fi_tab,
fi_wa type fi_tab,
po_int type table of po_tab,
po_wa type po_tab,
merge_int type table of merge_tab,
merge_wa type merge_tab,
copy_int type table of merge_tab,
copy_wa type merge_tab,
bseg_int type table of bseg_tab,
bseg_wa type bseg_tab,
bkpf_int type table of bkpf_tab,
bkpf_wa type bkpf_tab,
lfa1_int type table of lfa1_tab,
lfa1_wa type lfa1_tab.
field-symbols: <fs_bseg_int> type bseg_tab,
<fs_bkpf_int> type bkpf_tab,
<fs_lfa1_int> type lfa1_tab,
<fs_po_int> type po_tab,
<fs_fi_int> type fi_tab.
data: pgm_start type sy-uzeit,
pgm_end type sy-uzeit,
sel1_start type sy-uzeit,
sel1_end type sy-uzeit,
sel2_start type sy-uzeit,
sel2_end type sy-uzeit,
merge_start type sy-uzeit,
merge_end type sy-uzeit,
dif1 type i,
dif2 type i,
dif3 type i,
dif4 type i,
no_lines type i.
get time field pgm_end.
dif4 = pgm_end - pgm_start.
perform write_statistics.
top-of-page.
perform write_heading.
*&---------------------------------------------------------------------*
*& Form init_parm
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form init_parm .
endform. " init_parm
*&---------------------------------------------------------------------*
*& Form get_fi
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replacing nested SELECTs with SELECT INTO TABLE
* - Replacing SELECT * with SELECT <field list>
* - Using LOOP AT <itab> ASSIGNING
* - Use a BINARY SEARCH
*----------------------------------------------------------------------*
form get_fi .
get time field sel1_start.
select bukrs belnr gjahr buzei lifnr ebeln ebelp
from bseg
into table bseg_int
where gjahr > '2001'
and lifnr in s_lifnr
and koart = 'K'.
if sy-subrc <> 0.
message e001 with 'No FI data selected'.
endif.
select bukrs belnr gjahr budat waers blart usnam
cpudt cputm
from bkpf
into table bkpf_int
for all entries in bseg_int
where bukrs = bseg_int-bukrs
and belnr = bseg_int-belnr
and gjahr = bseg_int-gjahr.
select lifnr name1
from lfa1
into table lfa1_int
for all entries in bseg_int
where lifnr = bseg_int-lifnr.
sort: bseg_int by bukrs belnr gjahr buzei,
bkpf_int by bukrs belnr gjahr,
lfa1_int by lifnr.
loop at bseg_int assigning <fs_bseg_int>.
clear fi_wa.
move: <fs_bseg_int>-bukrs to fi_wa-bukrs,
<fs_bseg_int>-belnr to fi_wa-belnr,
<fs_bseg_int>-gjahr to fi_wa-gjahr,
<fs_bseg_int>-buzei to fi_wa-buzei,
<fs_bseg_int>-lifnr to fi_wa-lifnr,
<fs_bseg_int>-ebeln to fi_wa-ebeln,
<fs_bseg_int>-ebelp to fi_wa-ebelp.
read table bkpf_int with key
bukrs = <fs_bseg_int>-bukrs
belnr = <fs_bseg_int>-belnr
gjahr = <fs_bseg_int>-gjahr
assigning <fs_bkpf_int>
binary search.
if sy-subrc = 0.
move: <fs_bkpf_int>-budat to fi_wa-budat,
<fs_bkpf_int>-waers to fi_wa-waers,
<fs_bkpf_int>-blart to fi_wa-blart,
<fs_bkpf_int>-usnam to fi_wa-usnam,
<fs_bkpf_int>-cpudt to fi_wa-cpudt,
<fs_bkpf_int>-cputm to fi_wa-cputm.
endif.
read table lfa1_int with key
lifnr = <fs_bseg_int>-lifnr
assigning <fs_lfa1_int>
binary search.
if sy-subrc = 0.
move: <fs_lfa1_int>-name1 to fi_wa-name1.
endif.
append fi_wa to fi_int.
endloop.
sort fi_int by bukrs belnr gjahr.
get time field sel1_end.
dif1 = sel1_end - sel1_start.
endform. " get_fi
*&---------------------------------------------------------------------*
*& Form get_po
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replacing nested SELECT with a JOIN
*----------------------------------------------------------------------*
form get_po .
get time field sel2_start.
select ekko~ebeln ekpo~ebelp ekko~loekz ekko~lifnr ekko~aedat
ekko~ernam ekpo~loekz ekpo~menge ekpo~netwr
from ekko
join ekpo on ekpo~ebeln = ekko~ebeln
into table po_int
where lifnr in s_lifnr.
if sy-subrc <> 0.
message e001 with 'No PO data selected'.
endif.
get time field sel2_end.
dif2 = sel2_end - sel2_start.
endform. " get_po
*&---------------------------------------------------------------------*
*& Form merge_data
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Using LOOP AT <itab> ASSIGNING (first loop)
* - Using MODIFY TRANSPORTING (second loop)
* - Replace IF/ELSEIF with CASE
*----------------------------------------------------------------------*
form merge_data .
sort: po_int by lifnr ernam aedat,
fi_int by lifnr usnam cpudt.
get time field merge_start.
loop at po_int assigning <fs_po_int>.
loop at fi_int assigning <fs_fi_int> where
lifnr = <fs_po_int>-lifnr and
usnam = <fs_po_int>-ernam and
cpudt = <fs_po_int>-aedat.
move: <fs_po_int>-ebeln to merge_wa-ebeln,
<fs_po_int>-lifnr to merge_wa-lifnr,
<fs_po_int>-ernam to merge_wa-ernam.
move: <fs_fi_int>-bukrs to merge_wa-bukrs,
<fs_fi_int>-belnr to merge_wa-belnr,
<fs_fi_int>-gjahr to merge_wa-gjahr,
<fs_fi_int>-buzei to merge_wa-buzei,
<fs_fi_int>-name1 to merge_wa-name1,
<fs_fi_int>-waers to merge_wa-waers.
append merge_wa to merge_int.
endloop.
endloop.
loop at merge_int into merge_wa.
case merge_wa-waers.
when 'CAD'.
merge_wa-curr = 'Canadian dollars'.
when 'USD'.
merge_wa-curr = 'U. S. dollars'.
when 'GBP'.
merge_wa-curr = 'British pounds'.
when 'EUR'.
merge_wa-curr = 'Euros'.
when others.
merge_wa-curr = 'Other'.
endcase.
modify merge_int from merge_wa transporting curr.
endloop.
get time field merge_end.
dif3 = merge_end - merge_start.
endform. " merge_data
*&---------------------------------------------------------------------*
*& Form copy_itab
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replace LOOP AT <itab> APPEND ENDLOOP
* with <itab2> = <itab1>
*----------------------------------------------------------------------*
form copy_itab .
copy_int[] = merge_int[].
delete adjacent duplicates from copy_int
comparing bukrs belnr gjahr ebeln.
endform. " copy_itab
*&---------------------------------------------------------------------*
*& Form write_heading
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_heading .
*&---------------------------------------------------------------------*
*& Form write_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_data .
describe table copy_int lines no_lines.
loop at copy_int into copy_wa.
write: / copy_wa-bukrs under 'CoCd',
copy_wa-belnr under 'Doc No',
copy_wa-gjahr under 'FYr',
copy_wa-ebeln under 'PONo',
copy_wa-lifnr under 'Ven No',
copy_wa-name1 under 'Vendor Name',
copy_wa-ernam under 'UserID',
copy_wa-curr under 'Currency'.
endloop.
if sy-subrc = 0.
skip 1.
write: /001 'Number of rows selected: ', no_lines.
else.
write: /001 'No data selected'.
endif.
endform. " write_data
*&---------------------------------------------------------------------*
*& Form write_statistics
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_statistics .
describe table fi_int lines no_lines.
write: /001 'Number of FI records selected :', no_lines.
describe table po_int lines no_lines.
write: /001 'Number of PO records selected :', no_lines.
describe table copy_int lines no_lines.
write: /001 'Number of merged records :', no_lines.
skip 1.
write: /001 'Time for poorly optimized select on FI data :',
dif1, 'seconds'.
write: /001 'Time for poorly optimized select on PO data :',
dif2, 'seconds'.
write: /001 'Time for poorly optimized merge of data :',
dif3, 'seconds'.
write: /001 'Time for poorly optimized program :',
dif4, 'seconds'.
endform. " write_statistics
The third program uses just two techniques. I've jumped through a couple of hoops to make sure I use an index and I've replaced the nested loops with a single loop and binary reads. Here is the final program:
data: bkpf type bkpf,
bseg type bseg,
lfa1 type lfa1,
ekko type ekko,
ekpo type ekpo.
select-options: s_lifnr for bseg-lifnr memory id lif obligatory.
types: begin of fi_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
lifnr type bseg-lifnr,
ebeln type bseg-ebeln,
ebelp type bseg-ebelp,
budat type bkpf-budat,
waers type bkpf-waers,
blart type bkpf-blart,
usnam type bkpf-usnam,
cpudt type bkpf-cpudt,
cputm type bkpf-cputm,
name1 type kna1-name1,
end of fi_tab.
types: begin of po_tab,
ebeln type ekpo-ebeln,
ebelp type ekpo-ebelp,
loekz type ekko-loekz,
lifnr type ekko-lifnr,
aedat type ekko-aedat,
ernam type ekko-ernam,
loekz1 type ekpo-loekz,
menge type ekpo-menge,
netwr type ekpo-netwr,
end of po_tab.
types: begin of merge_tab,
bukrs type bseg-bukrs,
belnr type bseg-belnr,
gjahr type bseg-gjahr,
buzei type bseg-buzei,
name1 type kna1-name1,
ebeln type ekko-ebeln,
lifnr type ekko-lifnr,
ernam type ekko-ernam,
waers type bkpf-waers,
curr(20),
end of merge_tab.
data: fi_int type table of fi_tab,
fi_wa type fi_tab,
po_int type table of po_tab,
po_wa type po_tab,
merge_int type table of merge_tab,
merge_wa type merge_tab,
copy_int type table of merge_tab,
copy_wa type merge_tab,
bsik_wa type bsik,
bsak_wa type bsak,
bkpf_wa type bkpf,
lfa1_wa type lfa1,
ekko_wa type ekko,
ekpo_wa type ekpo.
data: pgm_start type sy-uzeit,
pgm_end type sy-uzeit,
sel1_start type sy-uzeit,
sel1_end type sy-uzeit,
sel2_start type sy-uzeit,
sel2_end type sy-uzeit,
merge_start type sy-uzeit,
merge_end type sy-uzeit,
dif1 type i,
dif2 type i,
dif3 type i,
dif4 type i,
no_lines type i,
fi_index type sy-tabix.
get time field pgm_end.
dif4 = pgm_end - pgm_start.
perform write_statistics.
top-of-page.
perform write_heading.
*&---------------------------------------------------------------------*
*& Form init_parm
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form init_parm .
endform. " init_parm
*&---------------------------------------------------------------------*
*& Form get_fi
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replacing SELECT against BSEG with two SELECTS against BSIK
* and BSAK in order to use an index
*----------------------------------------------------------------------*
form get_fi .
get time field sel1_start.
select *
from bsik
into bsik_wa
where lifnr in s_lifnr
and gjahr > '2001'.
move-corresponding bsik_wa to fi_wa.
select single *
from bkpf
into bkpf_wa
where bukrs = bsik_wa-bukrs
and belnr = bsik_wa-belnr
and gjahr = bsik_wa-gjahr.
if sy-subrc = 0.
move-corresponding bkpf_wa to fi_wa.
endif.
select single *
from lfa1
into lfa1_wa
where lifnr = bsik_wa-lifnr.
if sy-subrc = 0.
move-corresponding lfa1_wa to fi_wa.
endif.
append fi_wa to fi_int.
endselect.
select *
from bsak
into bsak_wa
where lifnr in s_lifnr
and gjahr > '2001'.
move-corresponding bsak_wa to fi_wa.
select single *
from bkpf
into bkpf_wa
where bukrs = bsak_wa-bukrs
and belnr = bsak_wa-belnr
and gjahr = bsak_wa-gjahr.
if sy-subrc = 0.
move-corresponding bkpf_wa to fi_wa.
endif.
select single *
from lfa1
into lfa1_wa
where lifnr = bsak_wa-lifnr.
if sy-subrc = 0.
move-corresponding lfa1_wa to fi_wa.
endif.
append fi_wa to fi_int.
endselect.
describe table fi_int lines no_lines.
if no_lines = 0.
message e001 with 'No FI data selected'.
endif.
sort fi_int by bukrs belnr gjahr.
get time field sel1_end.
dif1 = sel1_end - sel1_start.
endform. " get_fi
*&---------------------------------------------------------------------*
*& Form get_po
*&---------------------------------------------------------------------*
* This form is doing a SELECT that is using a secondary index
* - Leave it alone
*----------------------------------------------------------------------*
form get_po .
get time field sel2_start.
select * from ekko
into ekko_wa
where lifnr in s_lifnr.
move-corresponding ekko_wa to po_wa.
select * from ekpo
into ekpo_wa
where ebeln = ekko_wa-ebeln.
move-corresponding ekpo_wa to po_wa.
po_wa-loekz1 = ekpo_wa-loekz.
po_wa-aedat = ekko_wa-aedat.
append po_wa to po_int.
endselect.
endselect.
if sy-subrc <> 0.
message e001 with 'No PO data selected'.
endif.
get time field sel2_end.
dif2 = sel2_end - sel2_start.
endform. " get_po
*&---------------------------------------------------------------------*
*& Form merge_data
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Using BINARY SEARCH and INDEXed READ
*----------------------------------------------------------------------*
form merge_data .
sort: po_int by lifnr ernam aedat,
fi_int by lifnr usnam cpudt.
get time field merge_start.
loop at po_int into po_wa.
read table fi_int with key
lifnr = po_wa-lifnr
usnam = po_wa-ernam
cpudt = po_wa-aedat
binary search
into fi_wa.
fi_index = sy-tabix.
while sy-subrc = 0.
move-corresponding fi_wa to merge_wa.
move-corresponding po_wa to merge_wa.
append merge_wa to merge_int.
fi_index = fi_index + 1.
read table fi_int index fi_index
into fi_wa.
if fi_wa-lifnr <> po_wa-lifnr or
fi_wa-usnam <> po_wa-ernam or
fi_wa-cpudt <> po_wa-aedat.
sy-subrc = 9.
endif.
endwhile.
endloop.
loop at merge_int into merge_wa.
case merge_wa-waers.
when 'CAD'.
merge_wa-curr = 'Canadian dollars'.
when 'USD'.
merge_wa-curr = 'U. S. dollars'.
when 'GBP'.
merge_wa-curr = 'British pounds'.
when 'EUR'.
merge_wa-curr = 'Euros'.
when others.
merge_wa-curr = 'Other'.
endcase.
modify merge_int from merge_wa transporting curr.
endloop.
get time field merge_end.
dif3 = merge_end - merge_start.
endform. " merge_data
*&---------------------------------------------------------------------*
*& Form copy_itab
*&---------------------------------------------------------------------*
* Do not optimize this form
*----------------------------------------------------------------------*
form copy_itab .
loop at merge_int into merge_wa.
move-corresponding merge_wa to copy_wa.
append copy_wa to copy_int.
endloop.
delete adjacent duplicates from copy_int
comparing bukrs belnr gjahr ebeln.
endform. " copy_itab
*&---------------------------------------------------------------------*
*& Form write_heading
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_heading .
*&---------------------------------------------------------------------*
*& Form write_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_data .
describe table copy_int lines no_lines.
loop at copy_int into copy_wa.
write: / copy_wa-bukrs under 'CoCd',
copy_wa-belnr under 'Doc No',
copy_wa-gjahr under 'FYr',
copy_wa-ebeln under 'PONo',
copy_wa-lifnr under 'Ven No',
copy_wa-name1 under 'Vendor Name',
copy_wa-ernam under 'UserID',
copy_wa-curr under 'Currency'.
endloop.
if sy-subrc = 0.
skip 1.
write: /001 'Number of rows selected: ', no_lines.
else.
write: /001 'No data selected'.
endif.
endform. " write_data
*&---------------------------------------------------------------------*
*& Form write_statistics
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form write_statistics .
describe table fi_int lines no_lines.
write: /001 'Number of FI records selected :', no_lines.
describe table po_int lines no_lines.
write: /001 'Number of PO records selected :', no_lines.
describe table copy_int lines no_lines.
write: /001 'Number of merged records :', no_lines.
skip 1.
write: /001 'Time for better optimized select on FI data :',
dif1, 'seconds'.
write: /001 'Time for better optimized select on PO data :',
dif2, 'seconds'.
write: /001 'Time for better optimized merge of data :',
dif3, 'seconds'.
write: /001 'Time for better optimized program :',
dif4, 'seconds'.
DATA: bkpf TYPE bkpf,
bseg TYPE bseg,
lfa1 TYPE lfa1,
ekko TYPE ekko,
ekpo TYPE ekpo.
SELECT-OPTIONS: s_lifnr FOR bseg-lifnr MEMORY ID lif OBLIGATORY.
TYPES: BEGIN OF fi_tab,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
buzei TYPE bseg-buzei,
lifnr TYPE bseg-lifnr,
ebeln TYPE bseg-ebeln,
ebelp TYPE bseg-ebelp,
budat TYPE bkpf-budat,
waers TYPE bkpf-waers,
blart TYPE bkpf-blart,
usnam TYPE bkpf-usnam,
cpudt TYPE bkpf-cpudt,
cputm TYPE bkpf-cputm,
name1 TYPE kna1-name1,
END OF fi_tab.
TYPES: BEGIN OF po_tab,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
loekz TYPE ekko-loekz,
lifnr TYPE ekko-lifnr,
aedat TYPE ekko-aedat,
ernam TYPE ekko-ernam,
loekz1 TYPE ekpo-loekz,
menge TYPE ekpo-menge,
netwr TYPE ekpo-netwr,
END OF po_tab.
TYPES: BEGIN OF merge_tab,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
buzei TYPE bseg-buzei,
name1 TYPE kna1-name1,
ebeln TYPE ekko-ebeln,
lifnr TYPE ekko-lifnr,
ernam TYPE ekko-ernam,
waers TYPE bkpf-waers,
curr(20),
END OF merge_tab.
TYPES: BEGIN OF bseg_tab,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
buzei TYPE bseg-buzei,
lifnr TYPE bseg-lifnr,
ebeln TYPE bseg-ebeln,
ebelp TYPE bseg-ebelp,
END OF bseg_tab.
TYPES: BEGIN OF bkpf_tab,
bukrs TYPE bkpf-bukrs,
belnr TYPE bkpf-belnr,
gjahr TYPE bkpf-gjahr,
budat TYPE bkpf-budat,
waers TYPE bkpf-waers,
blart TYPE bkpf-blart,
usnam TYPE bkpf-usnam,
cpudt TYPE bkpf-cpudt,
cputm TYPE bkpf-cputm,
END OF bkpf_tab.
TYPES: BEGIN OF lfa1_tab,
lifnr TYPE kna1-lifnr,
name1 TYPE kna1-name1,
END OF lfa1_tab.
DATA: fi_int TYPE TABLE OF fi_tab,
fi_wa TYPE fi_tab,
po_int TYPE TABLE OF po_tab,
po_wa TYPE po_tab,
merge_int TYPE TABLE OF merge_tab,
merge_wa TYPE merge_tab,
copy_int TYPE TABLE OF merge_tab,
copy_wa TYPE merge_tab,
bseg_int TYPE TABLE OF bseg_tab,
bseg_wa TYPE bseg_tab,
bkpf_int TYPE TABLE OF bkpf_tab,
bkpf_wa TYPE bkpf_tab,
lfa1_int TYPE TABLE OF lfa1_tab,
lfa1_wa TYPE lfa1_tab.
FIELD-SYMBOLS: <fs_bseg_int> TYPE bseg_tab,
<fs_bkpf_int> TYPE bkpf_tab,
<fs_lfa1_int> TYPE lfa1_tab,
<fs_po_int> TYPE po_tab,
<fs_fi_int> TYPE fi_tab.
DATA: pgm_start TYPE sy-uzeit,
pgm_end TYPE sy-uzeit,
sel1_start TYPE sy-uzeit,
sel1_end TYPE sy-uzeit,
sel2_start TYPE sy-uzeit,
sel2_end TYPE sy-uzeit,
merge_start TYPE sy-uzeit,
merge_end TYPE sy-uzeit,
dif1 TYPE i,
dif2 TYPE i,
dif3 TYPE i,
dif4 TYPE i,
no_lines TYPE i,
fi_index TYPE sy-tabix.
GET TIME FIELD pgm_end.
dif4 = pgm_end - pgm_start.
PERFORM write_statistics.
TOP-OF-PAGE.
PERFORM write_heading.
*&---------------------------------------------------------------------*
*& Form init_parm
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM init_parm .
ENDFORM. " init_parm
*&---------------------------------------------------------------------*
*& Form get_fi
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replacing SELECT against BSEG with two SELECTS against BSIK
* and BSAK in order to use an index
* - Replacing nested SELECTs with SELECT INTO TABLE
* - Replacing SELECT * with SELECT <field list>
* - Using LOOP AT <itab> ASSIGNING
* - Use a BINARY SEARCH
*----------------------------------------------------------------------*
FORM get_fi .
GET TIME FIELD sel1_start.
SELECT bukrs belnr gjahr buzei lifnr ebeln ebelp
FROM bsik
INTO TABLE bseg_int
WHERE lifnr IN s_lifnr
AND gjahr > '2001'.
IF sy-subrc <> 0.
MESSAGE e001 WITH 'No FI data selected'.
ENDIF.
SELECT bukrs belnr gjahr buzei lifnr ebeln ebelp
FROM bsak
APPENDING TABLE bseg_int
WHERE lifnr IN s_lifnr
AND gjahr > '2001'.
IF sy-subrc <> 0.
MESSAGE e001 WITH 'No FI data selected'.
ENDIF.
SELECT bukrs belnr gjahr budat waers blart usnam
cpudt cputm
FROM bkpf
INTO TABLE bkpf_int
FOR ALL ENTRIES IN bseg_int
WHERE bukrs = bseg_int-bukrs
AND belnr = bseg_int-belnr
AND gjahr = bseg_int-gjahr.
SELECT lifnr name1
FROM lfa1
INTO TABLE lfa1_int
FOR ALL ENTRIES IN bseg_int
WHERE lifnr = bseg_int-lifnr.
SORT: bseg_int BY bukrs belnr gjahr buzei,
bkpf_int BY bukrs belnr gjahr,
lfa1_int BY lifnr.
LOOP AT bseg_int ASSIGNING <fs_bseg_int>.
CLEAR fi_wa.
MOVE: <fs_bseg_int>-bukrs TO fi_wa-bukrs,
<fs_bseg_int>-belnr TO fi_wa-belnr,
<fs_bseg_int>-gjahr TO fi_wa-gjahr,
<fs_bseg_int>-buzei TO fi_wa-buzei,
<fs_bseg_int>-lifnr TO fi_wa-lifnr,
<fs_bseg_int>-ebeln TO fi_wa-ebeln,
<fs_bseg_int>-ebelp TO fi_wa-ebelp.
READ TABLE bkpf_int WITH KEY
bukrs = <fs_bseg_int>-bukrs
belnr = <fs_bseg_int>-belnr
gjahr = <fs_bseg_int>-gjahr
ASSIGNING <fs_bkpf_int>
BINARY SEARCH.
IF sy-subrc = 0.
MOVE: <fs_bkpf_int>-budat TO fi_wa-budat,
<fs_bkpf_int>-waers TO fi_wa-waers,
<fs_bkpf_int>-blart TO fi_wa-blart,
<fs_bkpf_int>-usnam TO fi_wa-usnam,
<fs_bkpf_int>-cpudt TO fi_wa-cpudt,
<fs_bkpf_int>-cputm TO fi_wa-cputm.
ENDIF.
READ TABLE lfa1_int WITH KEY
lifnr = <fs_bseg_int>-lifnr
ASSIGNING <fs_lfa1_int>
BINARY SEARCH.
IF sy-subrc = 0.
MOVE: <fs_lfa1_int>-name1 TO fi_wa-name1.
ENDIF.
APPEND fi_wa TO fi_int.
ENDLOOP.
SORT fi_int BY bukrs belnr gjahr.
GET TIME FIELD sel1_end.
dif1 = sel1_end - sel1_start.
ENDFORM. " get_fi
*&---------------------------------------------------------------------*
*& Form get_po
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replacing nested SELECT with a JOIN
*----------------------------------------------------------------------*
FORM get_po .
GET TIME FIELD sel2_start.
SELECT ekko~ebeln ekpo~ebelp ekko~loekz ekko~lifnr ekko~aedat
ekko~ernam ekpo~loekz ekpo~menge ekpo~netwr
FROM ekko
JOIN ekpo ON ekpo~ebeln = ekko~ebeln
INTO TABLE po_int
WHERE lifnr IN s_lifnr.
IF sy-subrc <> 0.
MESSAGE e001 WITH 'No PO data selected'.
ENDIF.
GET TIME FIELD sel2_end.
dif2 = sel2_end - sel2_start.
ENDFORM. " get_po
*&---------------------------------------------------------------------*
*& Form merge_data
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Using BINARY SEARCH and INDEXed READ
* - Using LOOP AT <itab> ASSIGNING (first loop)
* - Using MODIFY TRANSPORTING (second loop)
* - Replace IF/ELSEIF with CASE
*----------------------------------------------------------------------*
FORM merge_data .
SORT: po_int BY lifnr ernam aedat,
fi_int BY lifnr usnam cpudt.
WHILE sy-subrc = 0.
MOVE: <fs_po_int>-ebeln TO merge_wa-ebeln,
<fs_po_int>-lifnr TO merge_wa-lifnr,
<fs_po_int>-ernam TO merge_wa-ernam.
MOVE: <fs_fi_int>-bukrs TO merge_wa-bukrs,
<fs_fi_int>-belnr TO merge_wa-belnr,
<fs_fi_int>-gjahr TO merge_wa-gjahr,
<fs_fi_int>-buzei TO merge_wa-buzei,
<fs_fi_int>-name1 TO merge_wa-name1,
<fs_fi_int>-waers TO merge_wa-waers.
APPEND merge_wa TO merge_int.
IF <fs_fi_int>-lifnr <> <fs_po_int>-lifnr OR
<fs_fi_int>-usnam <> <fs_po_int>-ernam OR
<fs_fi_int>-cpudt <> <fs_po_int>-aedat.
sy-subrc = 9.
ENDIF.
ENDWHILE.
ENDLOOP.
LOOP AT merge_int INTO merge_wa.
CASE merge_wa-waers.
WHEN 'CAD'.
merge_wa-curr = 'Canadian dollars'.
WHEN 'USD'.
merge_wa-curr = 'U. S. dollars'.
WHEN 'GBP'.
merge_wa-curr = 'British pounds'.
WHEN 'EUR'.
merge_wa-curr = 'Euros'.
WHEN OTHERS.
merge_wa-curr = 'Other'.
ENDCASE.
MODIFY merge_int FROM merge_wa TRANSPORTING curr.
ENDLOOP.
GET TIME FIELD merge_end.
dif3 = merge_end - merge_start.
ENDFORM. " merge_data
*&---------------------------------------------------------------------*
*& Form copy_itab
*&---------------------------------------------------------------------*
* Optimize this FORM by:
* - Replace LOOP AT <itab> APPEND ENDLOOP
* with <itab2> = <itab1>
*----------------------------------------------------------------------*
FORM copy_itab .
copy_int[] = merge_int[].
DELETE ADJACENT DUPLICATES FROM copy_int
COMPARING bukrs belnr gjahr ebeln.
ENDFORM. " copy_itab
*&---------------------------------------------------------------------*
*& Form write_heading
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM write_heading .
*&---------------------------------------------------------------------*
*& Form write_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM write_data .
DESCRIBE TABLE copy_int LINES no_lines.
LOOP AT copy_int INTO copy_wa.
WRITE: / copy_wa-bukrs UNDER 'CoCd',
copy_wa-belnr UNDER 'Doc No',
copy_wa-gjahr UNDER 'FYr',
copy_wa-ebeln UNDER 'PONo',
copy_wa-lifnr UNDER 'Ven No',
copy_wa-name1 UNDER 'Vendor Name',
copy_wa-ernam UNDER 'UserID',
copy_wa-curr UNDER 'Currency'.
ENDLOOP.
IF sy-subrc = 0.
SKIP 1.
WRITE: /001 'Number of rows selected: ', no_lines.
ELSE.
WRITE: /001 'No data selected'.
ENDIF.
ENDFORM. " write_data
*&---------------------------------------------------------------------*
*& Form write_statistics
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM write_statistics .
DESCRIBE TABLE fi_int LINES no_lines.
WRITE: /001 'Number of FI records selected :', no_lines.
DESCRIBE TABLE po_int LINES no_lines.
WRITE: /001 'Number of PO records selected :', no_lines.
DESCRIBE TABLE copy_int LINES no_lines.
WRITE: /001 'Number of merged records :', no_lines.
SKIP 1.
WRITE: /001 'Time for highly optimized select on FI data :',
dif1, 'seconds'.
WRITE: /001 'Time for highly optimized select on PO data :',
dif2, 'seconds'.
WRITE: /001 'Time for highly optimized merge of data :',
dif3, 'seconds'.
WRITE: /001 'Time for highly optimized program :',
dif4, 'seconds'.
ENDFORM. " write_statistics
I ran each program three times: once with a single vendor to pick up a small amount of data; once with a range of vendors to pick up a moderately large amount of data and once with a wide open range of vendors to pick up as much data as possible. And then I ran the programs multiple times.
So - What are the Results
When selecting small amounts of data, using an index made the select run about 45 times faster. Then, removing nested SELECTs made it run about an extra 15 times faster. So the most important performance consideration is the use of an index. Removing the nested SELECTs also seemed to help, but these results may be skewed by fact that I only ran the highly optimized program once for this situation.
When selecting larger amounts of data, the elimination of nested loops reduced the run time from over an hour and a half to under a second. The use of the index sped the select up by a factor of five or six. Clearly, in this situation, removing nested loops is the most important performance tool. The use of an index will definitely help, but becomes less important as more of the database is selected.
When selecting very large amounts of data, the elimination of nested loops is still the most important performance tool. (I was not able to run either of the first two programs in under a day because of this.) Replacing nested SELECTS with FOR ALL ENTRIES reduced the run time by about a factor of two and so, helped in this case as well. The use of an index really doesn't matter when you're trying to select the whole database.
Conclusions
Remember the initial assumption - you must fix an existing program that performs poorly. In all likelihood you will find that this is from one of three causes:
The failure to use an index in SELECT(s)
The use of nested LOOPs
Nested SELECT(s)
Depending on the amount of data the program is retrieving, fix the one(s) that are causing the problem(s). While you can always tune a program to run minutes, seconds or just milliseconds faster, at some point, you will run into the point of diminishing returns. It will take you longer to make the program changes than the users will save due to those program changes. You also have to bear in mind that making any changes to an existing program that works carries some dangers. There is always the risk that the changes you make may make the program run faster, but produce incorrect results. Even if the user signs off on these changes, problems may arise later that may be caused by those changes.
In the final analysis, when writing new programs, I try to use as many of the tuning techniques as I can. But if I have a problem with an existing one, I look no further than nested loops and indexes.
Rob Burbank is a programmer/analyst in the Department of Administrative Management Systems at the University of Toronto.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
