SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Performance of Nested Loops



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Performance tuning | Производительность
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Sat Sep 22, 2007 12:33 pm    Post subject: Performance of Nested Loops Reply with quote

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:
Code:

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


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:
Code:

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

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.


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

  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:
Code:

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


The final program combines all of the techniques:
Code:

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

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.


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

  GET TIME FIELD merge_start.

  LOOP AT po_int ASSIGNING <fs_po_int>.

    READ TABLE fi_int ASSIGNING <fs_fi_int> WITH KEY
      lifnr = <fs_po_int>-lifnr
      usnam = <fs_po_int>-ernam
      cpudt = <fs_po_int>-aedat
      BINARY SEARCH.

    fi_index = sy-tabix.

    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.

      fi_index = fi_index + 1.
      READ TABLE fi_int ASSIGNING <fs_fi_int> INDEX fi_index.

      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 .

  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.

Rob Burbank is a programmer/analyst in the Department of Administrative Management Systems at the University of Toronto.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Performance tuning | Производительность All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
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.