Performance - what will kill you and what will lea...

Former Member · ‎11-16-2006

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:

report ztest1 line-size 120 message-id 00 line-count 44.

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.

initialization. perform init_parm.

start-of-selection.

get time field pgm_start.

perform get_fi. perform get_po. perform merge_data. perform copy_itab.

PERFORM write_data.

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.

loop at merge_int into merge_wa. if merge_wa-waers = 'CAD'. merge_wa-curr = 'Canadian dollars'. elseif merge_wa-waers = 'USD'. merge_wa-curr = 'U. S. dollars'. elseif merge_wa-waers = 'GBP'. merge_wa-curr = 'British pounds'. elseif merge_wa-waers = 'EUR'. merge_wa-curr = 'Euros'. else. merge_wa-curr = 'Other'. endif.

modify merge_int from merge_wa.

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 .

write: /037 'Test Report to Validate Data Selection'. write: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. skip 1.

endform. " 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 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.

initialization. perform init_parm.

start-of-selection.

get time field pgm_start.

perform get_fi. perform get_po. perform merge_data. perform copy_itab.

PERFORM write_data.

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 -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 -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 .

write: /037 'Test Report to Validate Data Selection'. write: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. skip 1.

endform. " 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:

report ztest3 line-size 120 message-id 00 line-count 44.

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.

initialization. perform init_parm.

start-of-selection.

get time field pgm_start.

perform get_fi. perform get_po. perform merge_data. perform copy_itab.

PERFORM write_data.

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 .

write: /037 'Test Report to Validate Data Selection'. write: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. skip 1.

endform. " 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'.

endform. " write_statistics 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.

INITIALIZATION. PERFORM init_parm.

START-OF-SELECTION.

GET TIME FIELD pgm_start.

PERFORM get_fi. PERFORM get_po. PERFORM merge_data. PERFORM copy_itab.

PERFORM write_data.

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 -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.

GET TIME FIELD merge_start.

LOOP AT po_int ASSIGNING -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 .

WRITE: /037 'Test Report to Validate Data Selection'. WRITE: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. SKIP 1.

ENDFORM. " 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.

Performance - what will kill you and what will leave you with only a flesh wound

So - What are the Results

Conclusions

Get Started with the ABAP Development Tools for SAP NetWeaver

Become an ABAP in Eclipse Feature Explorer and earn the Explorer Badge

Six kinds of debugging tips to find the source code where the message is raised