Skip to Content

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.
    1. 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 ekkoebeln ekpoebelp ekkoloekz ekkolifnr ekko~aedat         ekkoernam ekpoloekz ekpomenge ekponetwr     from ekko       join ekpo on ekpoebeln = ekkoebeln     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 ekkoebeln ekpoebelp ekkoloekz ekkolifnr ekko~aedat         ekkoernam ekpoloekz ekpomenge ekponetwr     FROM ekko       JOIN ekpo ON ekpoebeln = ekkoebeln     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:

    1. The failure to use an index in SELECT(s)
    2. The use of nested LOOPs
    3. 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.

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

    1. Rob Burbank Post author
      Well, I have to admit, I never thought about getting into trouble with deletes, but you’re right; one would have to take extra care in that situation.

      But on the other hand, the same would be true for empty select-options or ranges.

      Would you use a join instead?

      Rob

      (0) 
      1. Pedro Lima
        “But on the other hand, the same would be true for empty select-options or ranges.”

        Yes, you are right. But I think that when people see something like

        SELECT *
          FROM t005
          INTO TABLE lt_t005
           FOR ALL ENTRIES IN lt_land
        WHERE land1 = lt_land-land1.

        they don’t expect that an empty lt_land will give them all the data in the T005 table. It’s counter-intuitive (at least for me it is).

        Regards,
        Pedro

        (0) 
        1. Rob Burbank Post author
          Thanks for your comments Pedro.

          Actually, I think ranges are a bit different from select-options in terms of being (counter) intuitive. When a user leaves a select option blank, he or she expects no restrictions; however, when a program tries to fill a range table with expected entries but instead leaves it empty, the programmer probably is not expecting no restrictions.

          So, I still think using ranges is similar to FOR ALL ENTRIES, but SELECT-OPTIONS, although they behave the same, probably need less checking.

          Rob

          (0) 
  1. David Halitsky
    … which means that every time you use it, you must compare it against the “verboten” nest of select loops with fully resolved index and key fields.

    See my documentation of this fact here:

    Anyone Got Some Real Benchmark Stats on “For all Entries”???

    “Yes, Virginia, the check is in the mail” (or, why you can’t trust SQL)

    More generally, it’s incumbent upon all of us to realize that native DB optimizers are only good over a really trivial range of really trivial cardinalities, and that as soon as we’re out of this range, the ForAllEntries construct does as miserably as the underlying optimizer, even if it uses only key/index fields.

    (0) 
    1. Rob Burbank Post author
      Thanks for your comments David. I saw both your blogs as I was developing this one. It’s my feeling (not proven, but demonstrated in some cases) that any performance improvement to be had by using FOR ALL ENTRIES or a JOIN instead of nested SELECTS or SELECTS within loops is not worth the effort and may cause problems down the road.

      But the point of the blog is that the main performance problems are caused by not using an index or nested loops.

      Rob

      (0) 
      1. David Halitsky
        It’s like an “urban legend” that you have to debunk at every new customer site you’re at …
        (0) 
        1. Rob Burbank Post author
          Well, I’m not sure it’s an urban legend. I think it does provide some performance improvement. Just not enough to get excited about. The same goes for MOVE-CORRESPONDING, MODIFY…TRANSPORTING, etc.

          As I pointed out in the blog, I’m thinking of doing a blog on a comparison of JOINS and FOR ALL ENTRIES. If I do, I’ll include nested SELECTS as well.

          Or maybe you had that in your sights.

          Rob

          (0) 
          1. David Halitsky
            Some (all?) of the younger crowd around here thinks that data-retrieval from the back-end is a problem that Larry solved with Oracle and Bill with SQLServer.

            So they think it’s perfectly OK to build a career doing front-end user interface “bells-and-whistles”.  (After all, when they find out data-retrieval is STILL a problem, they can always ask their friendly BI Accelerator folks to solve the problem the way Bill and Larry should have solved it to begin with – by building a database that supports any number of alternative indices without sacrificing efficiency.  Right?)

            And that’s why I’m all in favor of any and all blogs/blog posts that wake the youngsters up to the fact that doing data retrieval with industrial-strength concurrency is not the same as being satsified when your three work-group partners hit enter at the same time as you do and the screen populates in less than 1 sec.

            (0) 
  2. Bernd Boecker
    Hi Rob,

    ORACLE 9i introduced index skip scan to allow index lookups if you don’t provide the first column in your WHERE clause.

    It works well if and only if the leading edge of the index  has very few discrete values and the optimizer understands that.

    So it’s not a silver bullet against a flawed WHERE clause like in your examples

    Developers should have more knowledge about the data distributions than any piece of optimizer can “read” from it’s statistics.

    Your example shows this very clear.

    bye

    yk

    (0) 
    1. Rob Burbank Post author
      This is the first I’ve heard of this. We are on a DB2 system, but migrated from Oracle a few years ago (don’t remember what version). But it does seem to me that in cases where I use say the second field of an index in the where, it goes faster than if no index is used. I guess this explains it.

      Thanks for the comment.

      Rob

      (0) 
  3. Bernd Boecker
    Hi Rob,

    ORACLE 9i introduced index skip scan to allow index lookups if you don’t provide the first column in your WHERE clause.

    It works well if and only if the leading edge of the index  has very few discrete values and the optimizer understands that.

    So it’s not a silver bullet against a flawed WHERE clause like in your examples

    Developers should have more knowledge about the data distributions than any piece of optimizer can “read” from it’s statistics.

    Your example shows this very clear.

    bye

    yk

    (0) 
    1. Rob Burbank Post author
      In general, I think you are correct. It would have been better to use AT NEW or something like that; however, in this particular case, because we are first SELECTing only vendor items from BSIK and BSAK, there will usually be only one vendor item per document.

      So in this case, it will only be a flesh wound.

      Merci de vos commentaires.

      Rob

      (0) 

Leave a Reply