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

Issue with nested loop

Hi,

There is a nested loop in the program which is causing a performance issue. The first internal table loop contains 6700 entries and the second internal table loop contains more than 5,00,000 entries. Can somebody please have a look at the below code and suggest how i can modify it to avoid the performance issue?

LOOP AT lt_zquota_item_skip INTO ls_zquota_item_val.

READ TABLE i_zquota_item2 TRANSPORTING NO FIELDS

WITH KEY product = ls_zquota_item_val-product

source = ls_zquota_item_val-source

destination = ls_zquota_item_val-destination

BINARY SEARCH.

IF sy-subrc IS INITIAL.

v_tabix = sy-tabix.

LOOP AT i_zquota_item2 INTO wa_zquota_item2 FROM v_tabix

WHERE product = ls_zquota_item_val-product

AND source = ls_zquota_item_val-source

AND destination = ls_zquota_item_val-destination.

v_tabx2 = sy-tabix.

APPEND wa_zquota_item2 TO lt_zquota_keep.

DELETE i_zquota_item2 INDEX v_tabx2.

ENDLOOP.

ENDIF.

READ TABLE i_zquota_item_val TRANSPORTING NO FIELDS

WITH KEY product = ls_zquota_item_val-product

source = ls_zquota_item_val-source

destination = ls_zquota_item_val-destination

BINARY SEARCH.

IF sy-subrc IS INITIAL.

v_tabix = sy-tabix.

LOOP AT i_zquota_item_val INTO ls_zquota_item_val FROM v_tabix

WHERE product = ls_zquota_item_val-product

AND source = ls_zquota_item_val-source

AND destination = ls_zquota_item_val-destination.

DELETE i_zquota_item_val INDEX sy-tabix.

ENDLOOP.

ENDIF.

ENDLOOP.

Add a comment
10|10000 characters needed characters exceeded

Related questions

8 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Apr 29, 2011 at 07:38 AM

    Hi,

    The code does look optimized to the best, so not sure on how much value my below suggestion would provide, check it out anyways

    LOOP AT lt_zquota_item_skip INTO ls_zquota_item_val.
        READ TABLE i_zquota_item2 TRANSPORTING NO FIELDS
        WITH KEY product = ls_zquota_item_val-product
        source = ls_zquota_item_val-source
        destination = ls_zquota_item_val-destination
        BINARY SEARCH.
        IF sy-subrc IS INITIAL.
          v_tabix = sy-tabix.
          LOOP AT i_zquota_item2 INTO wa_zquota_item2 FROM v_tabix
          WHERE product = ls_zquota_item_val-product
          AND source = ls_zquota_item_val-source
          AND destination = ls_zquota_item_val-destination.
            v_tabx2 = sy-tabix.
            APPEND wa_zquota_item2 TO lt_zquota_keep.
            DELETE i_zquota_item2 INDEX v_tabx2.
    * Check if the next entry satisfies the key search if not exit the loop.
    * index will remain the same as you have deleted the previous entry
            READ TABLE i_zquota_item2 INTO wa_zquota_item2 INDEX v_tabx2.
            IF sy-subrc EQ 0.
              IF wa_zquota_item2-product     EQ ls_zquota_item_val-product AND
                 wa_zquota_item2-source      EQ ls_zquota_item_val-source AND
                 wa_zquota_item2-destination EQ ls_zquota_item_val-destination.
              ELSE.
                EXIT. "Exit loop - Dont have to validate the remaining recs
              ENDIF.
            ENDIF.
          ENDLOOP.
        ENDIF.
        READ TABLE i_zquota_item_val TRANSPORTING NO FIELDS
        WITH KEY product = ls_zquota_item_val-product
        source = ls_zquota_item_val-source
        destination = ls_zquota_item_val-destination
        BINARY SEARCH.
        IF sy-subrc IS INITIAL.
          v_tabix = sy-tabix.
          LOOP AT i_zquota_item_val INTO ls_zquota_item_val FROM v_tabix
          WHERE product = ls_zquota_item_val-product
          AND source = ls_zquota_item_val-source
          AND destination = ls_zquota_item_val-destination.
            DELETE i_zquota_item_val INDEX sy-tabix.
    * Similarly for the this loop too, but are you sure you are passing the
    * right parameters for the where clause
          ENDLOOP.
        ENDIF.
      ENDLOOP.
    

    Regards,

    Chen

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 29, 2011 at 07:39 AM

    Hi Benjamin,

    Remove where clause

    --WHERE product = ls_zquota_item_val-product

    AND source = ls_zquota_item_val-source

    AND destination = ls_zquota_item_val-destination--

    from loop and exit where there is mismatch in the values for product or source or destination. Also remember to set the value for v_tabix when exiting form the loop.

    LOOP AT i_zquota_item2 INTO wa_zquota_item2 FROM v_tabix

    if wa_zquota_item2-product <> ls_zquota_item_val-product

    OR wa_zquota_item2-source <> ls_zquota_item_val-source

    OR wa_zquota_item2-destination <> ls_zquota_item_val-destination.

    EXIT.

    ENDIF.

    ...

    Regards,

    Shyam.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 29, 2011 at 09:22 AM

    Shyam has given you the right direction with his post above.

    Here is the complete piece of code with the changes:

    
    LOOP AT lt_zquota_item_skip INTO ls_zquota_item_val.
    
      READ TABLE i_zquota_item2 TRANSPORTING NO FIELDS
        WITH KEY product = ls_zquota_item_val-product
                 source = ls_zquota_item_val-source
                 destination = ls_zquota_item_val-destination
        BINARY SEARCH.
    
      IF sy-subrc IS INITIAL.
        v_tabix = sy-tabix.
        LOOP AT i_zquota_item2 INTO wa_zquota_item2 FROM v_tabix.
          if ( wa_zquota_item2-product NE ls_zquota_item_val-product ) OR
             ( wa_zquota_item2-source NE ls_zquota_item_val-source) OR
             ( wa_zquota_item2-destination NE ls_zquota_item_val-destination )
            exit.
          endif.
    
          APPEND wa_zquota_item2 TO lt_zquota_keep.
          DELETE i_zquota_item2. "index specification not necessary in loop
    
    * v_tabx2 = sy-tabix. "commented out as I don't see the usage reason
        ENDLOOP.
      ENDIF.
    
      READ TABLE i_zquota_item_val TRANSPORTING NO FIELDS
        WITH KEY product = ls_zquota_item_val-product
                 source = ls_zquota_item_val-source
                 destination = ls_zquota_item_val-destination
        BINARY SEARCH.
    
      IF sy-subrc IS INITIAL.
        v_tabix = sy-tabix.
        LOOP AT i_zquota_item_val INTO ls_zquota_item_val FROM v_tabix.
          if ( ls_zquota_item_val-product NE ls_zquota_item_val-product ) OR
             ( ls_zquota_item_val-source NE ls_zquota_item_val-source) OR
             ( ls_zquota_item_val-destination NE ls_zquota_item_val-destination )
            exit.
          endif.
          DELETE i_zquota_item_val. "index specification not necessary in loop
        ENDLOOP.
      ENDIF.
    
    ENDLOOP. 
    

    Edited by: Yuri Ziryukin on Apr 29, 2011 11:23 AM

    Edited by: Yuri Ziryukin on Apr 29, 2011 11:25 AM

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 02, 2011 at 08:30 AM

    What is the release of your system? If newer than 6.2, then the solution is much much simpler:

    Use a sorted table and write a DELETE WHERE .... then you save READ BINARY, LOOP, EXIT it is all done for you.

    Without the exit there is of course no optimization at all, the entry is found fast, the improvement is a factor 2.

    With exit you move from linear scaling to logarithmic scaling.

    All explained here:

    Measurements on internal tables: Reads and Loops:

    /people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Siegfried,

      the idea of the original code is not only to delete entries, but also to collect them into another internal table (see: APPEND wa_zquota_item2 TO lt_zquota_keep.)

      It means that he cannot simply use the delete for a sorted table and get rid of all loops, etc. Loop has to be done anyway.

      The only thing that can be spared is the IF comparison to exit the loop. If the table is defined as sorted, he can indeed use loop at ... where ... .

      But who knows if this table can really be defined as a sorted table. Maybe he uses it in several places of code with the different sort order. We cannot be sure. 😊

      Regards,

      Yuri

  • author's profile photo Former Member
    Former Member
    Posted on May 20, 2011 at 12:10 PM

    You can use the hased internal table and then instead of loop .... endloop.

    You can use do --endo inside that it there will read table and exit..

    may solve your problems.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 20, 2011 at 01:57 PM

    Despite reading the full code in detail, I can comment on that

    >You can use the hased internal table and then instead of loop .... endloop.

    LOOP and hashed table do not really fit together, hashed tables should only be used if there is a unique key and if you address records with the full unique key. That means you work on single records, i.e with READs.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 24, 2011 at 01:14 PM

    Hi,

    Another solution would be to use only sorted tables, which are quite good for solving those kind of issues.

    If it's possible, try to define your 2 main tables this way (I'm using DATA, it could of course be some kind of parameter )

    DATA: i_zquota_item2 TYPE SORTED TABLE OF ty_s_zquota_item2 
                         WITH NON-UNIQUE KEY product source  destination,
          i_zquota_item_val TYPE SORTED TABLE OF ty_s_zquota_item_val
                         WITH NON-UNIQUE KEY product source destination.
    

    Then, you could simplify your program like that:

      LOOP AT lt_zquota_item_skip INTO ls_zquota_item_val.
    
        LOOP AT i_zquota_item2 INTO wa_zquota_item2  WHERE product     = ls_zquota_item_val-product
                                                       AND source      = ls_zquota_item_val-source
                                                       AND destination = ls_zquota_item_val-destination.
          APPEND wa_zquota_item2 TO lt_zquota_keep.
        ENDLOOP.
    
        DELETE i_zquota_item_val
           WHERE product     = ls_zquota_item_val-product
             AND source      = ls_zquota_item_val-source
             AND destination = ls_zquota_item_val-destination.
      ENDLOOP.
    

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 28, 2011 at 01:39 AM

    Hi,

    Here's an angle that (surprisingly) no one has mentioned yet: doing multiple DELETEs on a large internal table (esp. 5 million entries!) is very expensive.

    Rather than doing:

    loop at itab

    where f1 = <something>

    delete itab.

    endloop.

    Try this:

    loop at itab

    where f1 <> <something>

    append itab to itab_filtered.

    endloop.

    • replace the original table with the filtered table

    itab[] = itab_filtered[].

    .. and your performance problems will disappear like snow on the equator.

    cheers

    Paul

    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.