Skip to Content
author's profile photo Former Member
Former Member

COLLECT Horrible Performance in Loop

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 05, 2009 at 06:25 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 06, 2009 at 07:27 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 07, 2009 at 07:27 AM

    it is not the read but the SORT!!!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 05, 2009 at 05:32 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 05, 2009 at 08:25 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.