COLLECT Horrible Performance in Loop

[Former Member]

This look takes over3 hours with 500k records, how can I make this faster??

SORT i_bw_detail_F   ASCENDING BY   z_belnr z_koart z_shkzg z_buzei.
SORT i_comb          ASCENDING BY   z_belnr z_koart z_shkzg z_buzei.

 LOOP AT i_bw_detail_F ASSIGNING <i_bw_detail_F>.
   READ TABLE i_comb ASSIGNING <i_comb> WITH KEY
                              z_belnr = <i_bw_detail_F>-z_belnr
                              z_koart = <i_bw_detail_F>-z_koart
                              z_shkzg = <i_bw_detail_F>-z_shkzg
                              z_buzei = <i_bw_detail_F>-z_buzei
                              BINARY SEARCH.

*  if we get a hit that matches BW and R/3 key data
   IF sy-subrc = 0.
*    setup the bw field symbol with the entry date / time so the collect will work
     <i_bw_detail_F>-z_entry_date = <i_comb>-z_entry_date.
     <i_bw_detail_F>-z_entry_time = <i_comb>-z_entry_time.

*     load the structure to be collected from BW into i_comb (r/3 data)
      st_i_comb-z_gjahr      =   <i_bw_detail_F>-z_gjahr.
      st_i_comb-z_monat      =   <i_bw_detail_F>-z_monat.
      st_i_comb-z_bukrs      =   <i_bw_detail_F>-z_bukrs.
      st_i_comb-z_belnr      =   <i_bw_detail_F>-z_belnr.
      st_i_comb-z_koart      =   <i_bw_detail_F>-z_koart.
      st_i_comb-z_shkzg      =   <i_bw_detail_F>-z_shkzg.
      st_i_comb-z_buzei      =   <i_bw_detail_F>-z_buzei.
      st_i_comb-z_bw_dmbtr   =   <i_bw_detail_F>-z_bw_dmbtr.
      st_i_comb-z_entry_date =   <i_bw_detail_F>-z_entry_date.
      st_i_comb-z_entry_time =   <i_bw_detail_F>-z_entry_time.

*     merge the bw data with the R/3 data
      COLLECT st_i_comb INTO i_comb.

ENDLOOP.

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Oct 5, 2009 1:33 PM

[Former Member · Accepted solution]

Please read the online help on COLLECT statement. Especially the third paragraph.

Because you are (1) reading the table like a standard table and (2) using COLLECT within the same loop, ABAP engine has to enable hash management and disable repeatedly. That is probably causing the performance issues for you.

There are many ways to overcome this issue, but I would probably COLLECT (or APPEND) into another temporary internal table and then COLLECT from temp itab into i_comb in a separate loop.

Edit: In reading Rob's response for the second time, I think his suggestion is even better.

Edited by: Sudhi Karkada on Oct 5, 2009 8:27 PM

[former_member194613]

it is not the read but the SORT!!!

[former_member194613]

the recommended usage of the COLLECT is together with a hashed table !!!

Collecting makes only sense with a unique, i.e. take the fastest table.

The COLLECT with the standard table uses an implicit hash key which can be destroyed by other operations.

Only recommended, if you built a table from scratch without any other operation in the loop!!!

Here the SORT destroys the implicit hash key.

Siegfried

[Former Member]

I don't understand how this works, could you show a code example or direct me to a resource showing how this works?

[Former Member]

Hi Gary,

I have made a few modifications to your code. Please let me know if this helps.

DATA: w_index_comb  TYPE                 sy-tabix,
      w_index_bw    TYPE                 sy-tabix,
      w_bw_detail_f LIKE                 i_bw_detail_f,

      i_comb_sort   LIKE SORTED TABLE OF i_comb
        WITH UNIQUE KEY z_belnr z_koart z_shkzg z_buzei
        WITH HEADER LINE.

SORT i_bw_detail_f   ASCENDING BY   z_belnr z_koart z_shkzg z_buzei.

INSERT LINES OF i_comb INTO TABLE i_comb_sort.

LOOP AT i_bw_detail_f ASSIGNING <i_bw_detail_f>.

  w_index_bw = sy-tabix.

  READ TABLE i_comb_sort ASSIGNING <i_comb> WITH KEY
                             z_belnr = <i_bw_detail_f>-z_belnr
                             z_koart = <i_bw_detail_f>-z_koart
                             z_shkzg = <i_bw_detail_f>-z_shkzg
                             z_buzei = <i_bw_detail_f>-z_buzei.

* if we get a hit that matches BW and R/3 key data
  IF sy-subrc = 0.

    w_index_comb = sy-tabix.

*   setup the bw field symbol with the entry date / time so the collect
*   will work
    w_bw_detail_f-z_entry_date = <i_comb>-z_entry_date.
    w_bw_detail_f-z_entry_time = <i_comb>-z_entry_time.

    st_i_comb-bw_dmbtr = <i_comb>-bw_dmbtr.

    ADD <i_bw_detail_f>-z_bw_dmbtr TO st_i_com-bw_dmbtr.

    MODIFY i_comb_sort FROM st_i_comb
      INDEX w_index_comb
      TRANSPORTING
        bw_dmbtr.

    MODIFY i_bw_detail_f FROM w_bw_detail_f
      INDEX w_index_bw
      TRANSPORTING
        z_entry_date
        z_entry_time.

  ELSE.

*   load the structure to be collected from BW into i_comb (r/3 data)
    st_i_comb-z_gjahr      =   <i_bw_detail_f>-z_gjahr.
    st_i_comb-z_monat      =   <i_bw_detail_f>-z_monat.
    st_i_comb-z_bukrs      =   <i_bw_detail_f>-z_bukrs.
    st_i_comb-z_belnr      =   <i_bw_detail_f>-z_belnr.
    st_i_comb-z_koart      =   <i_bw_detail_f>-z_koart.
    st_i_comb-z_shkzg      =   <i_bw_detail_f>-z_shkzg.
    st_i_comb-z_buzei      =   <i_bw_detail_f>-z_buzei.
    st_i_comb-z_bw_dmbtr   =   <i_bw_detail_f>-z_bw_dmbtr.
    st_i_comb-z_entry_date =   <i_bw_detail_f>-z_entry_date.
    st_i_comb-z_entry_time =   <i_bw_detail_f>-z_entry_time.

*   merge the bw data with the R/3 data
    INSERT st_i_comb INTO TABLE i_comb_sort.

  ENDIF.

ENDLOOP.

i_comb[] = i_comb_sort[].

[Former Member]

Since you already are positioned on the record you want to COLLECT, why don't you just do an ADD directly to that record. The COLLECT is bound to add some overhead.

Rob