LOOP in LOOP, (self loop) performance tuning ?

[Former Member]

Hi ABAP folks,

Can you please advise on the below code. My transformation program is taking 55 minutes to complete 1 round for 100,000 records....which is very bad coding. I am not good in abap. Can you please help me to fine tune the below code for optimum performance. I know its difficult to understand other's code without the actual scenario . But at-least at a high level any suggestion's like using field symbol's or sort or binary search any key words is much appreciated. I will be great at-least if it comes down 15 minutes.

If you need more details, I can update you. Basically this is written in the end-routine of the DSO. to get the add quanity and sub quantity of the material's based on the material movements and to get the price of the material's in the 2nd block based on the PO amount and Material amount.

      LOOP AT RESULT_PACKAGE ASSIGNING <fs_rp>.

        CLEAR: subqty, addqty, wa_ITAB1, WA_ITAB2, WA_ITAB3.

        LOOP AT it_ITAB1 INTO wa_ITAB1 WHERE PLANT =

        <fs_rp>-PLANT

        AND MATERIAL = <fs_rp>-MATERIAL

          AND PSTNG_DATE = <fs_rp>-DATE0.

          IF wa_ITAB1-MOVETYPE EQ '312'.

            addqty = addqty + wa_ITAB1-QUANT_B.

          ELSEIF wa_ITAB1-MOVETYPE EQ '202'.

            subqty = subqty + wa_ITAB1-QUANT_B.

          ENDIF.

        ENDLOOP.

        <fs_rp>-/BIC/YIO_ADD = addqty.

        <fs_rp>-/BIC/YIO_SUB = subqty.

*Calculating material price in the below code

        IF <fs_rp>-/BIC/YIOFLAG EQ 'Y'.

          READ TABLE it_ITAB2 INTO WA_ITAB2 WITH KEY MATERIAL =

          <fs_rp>-MATERIAL.

          IF SY-SUBRC = 0.

<FS_RP>-/BIC/YIOQNTPR = WA_ITAB2-NETPRICE.

<FS_RP>-LOC_CURRCY = WA_ITAB2-LOC_CURRCY.

          ENDIF.

        ELSEIF <fs_rp>-/BIC/YIOFLAG  EQ 'N'.

          READ TABLE IT_ITAB3 INTO WA_ITAB3 WITH key MATERIAL =

          <FS_RP>-MATERIAL

          PLANT =  <FS_RP>-PLANT

          CALMONTH = <FS_RP>-CALMONTH.

          IF SY-SUBRC = 0.

<FS_RP>-/BIC/YIOQNTPR = WA_ITAB3-PRICE_VAL.

<FS_RP>-LOC_CURRCY = WA_ITAB3-CURRENCY.

          ENDIF.

        ENDIF.

      ENDLOOP.

Thanks

DR

Moderator message: Enough tips/hints have already been given. The OP must use them to re-write the code. Spoonfeeding shall not be allowed.

Thread locked!

Message was edited by: Suhas Saha

[Former Member]

You could use Parallel cursror on the LOOP at it_ITAB1( Check Tips and Tricks under transaction SAT )

From SAT Help

Documentation

If ITAB1 has n1 entries and ITAB2 has n2 entries, the time needed for

the nested loop with the straightforward algorithm is O(n1 * n2),

whereas the parallel cursor approach takes only O(n1 + n2) time.

The above parallel cursor algorithm assumes that ITAB2 contains only

entries also contained in ITAB1.

If this assumption does not hold, the parallel cursor algorithm

gets slightly more complicated, but its performance characteristics

remain the same.

Also. ITAB2 and ITAB3 can be declared as HASHED(Preferred) / SORTED table(Or Read with BINARY SEARCH ).

It will surely have an impact on the performance improvement.

R

[SuhaSaha]

I am surprised that SAP suggests "parallel cursor" approach in SAT

ITAB2 and ITAB3 can be declared as HASHED(Preferred) / SORTED table(Or Read with BINARY SEARCH ).

Anyway, what do you mean by this? Why do you use "preferred" with HASHED table?

The LOOP...WHERE statement is optimised for -

1. SORTED tables with full primary key/partial key(left-justified)
2. HASHED tables with full primary key
3. All table types with secondary key

Further info - ABAP Keyword Documentation

[Former Member]

Actually It does. In transaction SAT ( It used to be SE30 ). Under "Tip and Tricks" check the pic.I don't prefer to search over the internet when I get the necessary info inside the system itself. There can be many ways to consume information.

Since the ITAB2 and ITAB3 are read with MATERIAL and I think can be converted to Hashed table as the data volume is high here and I am sure you know the benefit..Again SAT Tips and Tricks can be of help.

By "Preferred" I meant Hashed table tend to give a better result when number of entries are around more than 1000 that SORTED Table(At least in my Programming experience of 14 years I have seen that). I think you will agree that it will at least be better than doing simple read.( If the entries are more than 20 : Again Ref : SAT Tips and ricks )

[SuhaSaha]

I was surprised because says in this thread that he's not convinced with the parallel cursor technique.

I hardly work with STANDARD tables since a few years, for me it's either HASHED or SORTED table.

I had asked you the question because i don't have the concrete answer to my question - "Are HASHED tables better than SORTED table?"

Sometimes HASHED tables are a pain. For e.g., when accessing the first/last records, deleting the content of the table inside the LOOP. Both of them arise due to the fact that index access is not possible with HASHED tables.

[Former Member]

Ah.. I see..

But I have seen great improvements. One of my BOM  report was running for 40 hours :-). Using parallel cursor reduced it to 3 Hours .

As per as the HASHED tables are concerned , yes they are of great help as long as we do any read operation with the same key as . the read time is constant for hashed tables and for large table with read I would always go for Hashed tables(If possible) instead of SORTED tables. Again I experienced the hugh benefit by myself in custom BW extractors and Reports.

I agree with your second and fourth paragraph totally. Even I cannot remember when I have used standard tables ( Except Obviously SET_TABLE_FOR_FIRST_DISPLAY usage ) and yes hashed tables has got limitations but again .."Writing ABAP code is easy but writing good ABAP code is very difficult"

[SuhaSaha]

Except Obviously SET_TABLE_FOR_FIRST_DISPLAY usage

Or even BAPI tables ... In such cases i mostly use

APPEND LINES OF sorted/hashed table TO alv_table.

[Former Member]

BAPI or FM tables I agree.

But what do you mean by "APPEND LINES OF sorted/hashed table TO alv_table."?

[Former Member]

can you please retune the code with the suggested approach of parallel cursor and binary search . Thanks for your advise.

[Former Member]

Hi Roy,

can you please change my code with paraller cursor's so that I can use it to reduce the run time. Atleast give me high level statements, I will try to convert with ABAP syntax using help. Appreciate it.

thank you

DR

[rajkumarnarasimman]

Hi Daniel,

Can you please do the following changes.

1. Use parallel cursor method for nested loop concept

     ABAP Code for Parallel Cursor - Loop Processing - Code Gallery - SCN Wiki

2. Use Binary search in Read statement

Hope you can feel the difference in performance.

Regards

Rajkumar Narasimman

[Former Member]

Thanks everyone for their replies. But let me tell you , I am not an abaper, but a sap bi consultant. appreciate if you can please fine tune the code with the given fields or table names and I would try to use your code with my minimum abap knowledge.

But the way, this transformation program runs daily for mat_plant MD which is around 100,000 records. Bascially the requirement is to read the stock count added/substrated by reading the material movement delta ( changed records) and will modify the added/substracted quantity for each of every material.

For example ...1st block is for the changed inventory values ..if plant a, material 1000 it will check the mat movements 312 and 202 and will modify add quantity or substracted quantity based on the material movement data. the reason we are comparing with posting date is to compare the current day's delta of the material movements.

and 2nd block is to calculate the price of the material's .....if flag = y for material price from the PO's created for those material's and material standard price for the flag N material's. Bascially this will be done daily for all the 100,000 mat_plant master data. Just to calculate the price of the material. cost of each material to be precise.

This is the overall logic. You all great experienced abaper's suggesting different techniques, to be frank. I cant try any of them. If you guys provide me the modified code atleast at high level, I will try to decode it with my abap team else I will try to figure it my self with my research. I cannot just change the code if you say, just try it with parallel cursor's or use hashed tables etc...as I dont even have a clue .how to change it . I am sorry, but helpless. Hope you understand the progam and my situation.

Please update if you need more details.

NOTE : Result package will have the total mat_plant md . Just the material's with respect to their plants along with today's date, as I said its a daily full load from ECC. Hope this helps.

Thanks again.

[Former Member]

Dear raj,

thanks for the reply. Can you please change/update the code with parallel cursor and binary search with read. I will do the syntax modifications. Please provide the high level code atleast.

Thanks

DR

[Former Member]

HI,

add some info about the amount of entries.

1.As you already mentioned, work with sorted tables. give this sorted tables a key. use this key when  doing loop and read table.

2.use "assigning" instead of "into"

3. reduce the amount of loops by writing if statements in the where clause. (if possible)

for example add key movetype to the typedefinition of sorted table it_ITAB1 and add in where condition where MOVETYPE eq 312 or 202.

regards

Stefan Seeburger

[hitesh_gabani]

Hi Daniel,

Use below code for the loop statement (Using Parallel Cursor) :

SORT : RESULT_PACKAGE BY PLANT MATERIAL DATE0,

            it_ITAB1 BY PLANT MATERIAL PSTNG_DATE.

DATA : ld_index type SY-TABIX.

LOOP AT RESULT_PACKAGE ASSIGNING <fs_rp>.

       CLEAR: subqty, addqty, wa_ITAB1, WA_ITAB2, WA_ITAB3.

       READ it_ITAB1 INTO wa_ITAB1 WHERE PLANT =

        <fs_rp>-PLANT

        AND MATERIAL = <fs_rp>-MATERIAL

          AND PSTNG_DATE = <fs_rp>-DATE0 BINARY SEARCH.

          IF SY-SUBRC = 0.

               ld_index = sy-tabix.

               LOOP AT it_ITAB1 INTO wa_ITAB1 FROM ld_index.

                     IF PLANT <> <fs_rp>-PLANT AND MATERIAL <> <fs_rp>-MATERIAL <> PSTNG_DATE = <fs_rp>-DATE0.

                              EXIT.

                    ENDIF.              

                    IF wa_ITAB1-MOVETYPE EQ '312'.

                           addqty = addqty + wa_ITAB1-QUANT_B.

                     ELSEIF wa_ITAB1-MOVETYPE EQ '202'.

                           subqty = subqty + wa_ITAB1-QUANT_B.

                     ENDIF.

        ENDLOOP.

<fs_rp>-/BIC/YIO_ADD = addqty.

        <fs_rp>-/BIC/YIO_SUB = subqty.

*Calculating material price in the below code

        IF <fs_rp>-/BIC/YIOFLAG EQ 'Y'.

          READ TABLE it_ITAB2 INTO WA_ITAB2 WITH KEY MATERIAL =

          <fs_rp>-MATERIAL.

          IF SY-SUBRC = 0.

               <FS_RP>-/BIC/YIOQNTPR = WA_ITAB2-NETPRICE.

              <FS_RP>-LOC_CURRCY = WA_ITAB2-LOC_CURRCY.

          ENDIF.

        ELSEIF <fs_rp>-/BIC/YIOFLAG  EQ 'N'.

          READ TABLE IT_ITAB3 INTO WA_ITAB3 WITH key MATERIAL =

          <FS_RP>-MATERIAL

          PLANT =  <FS_RP>-PLANT

          CALMONTH = <FS_RP>-CALMONTH.

          IF SY-SUBRC = 0.

             <FS_RP>-/BIC/YIOQNTPR = WA_ITAB3-PRICE_VAL.

             <FS_RP>-LOC_CURRCY = WA_ITAB3-CURRENCY.

          ENDIF.

        ENDIF.

    ENDLOOP.

Regards,

Hitesh

[Former Member]

Thanks a lot Hitesh.

I am getting the below error while doing the syntax check. Can you please advise where do I correct it.

E:"LINE ...", "TABLE ...", "REPORT ...", "DATASET ...", "TEXTPOOL ...",

or "CURRENT LINE" expected after "READ".

Its showing RED at this statement " AND PSTNG_DATE = <fs_rp>-DATE0 BINARY SEARCH.". in the first READ

This is the complete select :

READ it_itab1 INTO wa_itab1 WHERE
         PLANT = <fs_rp>-PLANT
          AND  MATERIAL = <fs_rp>-MATERIAL
           AND PSTNG_DATE = <fs_rp>-DATE0 BINARY SEARCH.

Thanks

DR

[Former Member]

Hello Daniel,

I think the easy way would be to declare it_ITAB1 as a sorted internal table with keys as PLANT MATERIAL AND PSTNG_DATE.

So that

LOOP AT it_ITAB1 INTO wa_ITAB1 WHERE PLANT =

        <fs_rp>-PLANT  will use binary search algorithm when looping. This would be as good as using parallel cursor technique.

ie. data : it_itab1 type sorted table of ty_xxxx with unique/non-unique keys PLANT MATERIAL PSTNG_DATA.

Also declare itab2 and itab3 as sorted tables, with material as the key.

Check if the performance improves.

[hitesh_gabani]

Hi,

Sry My mistake,

Instead of where use with key.

like,

READ it_itab1 INTO wa_itab1 WITH KEY

         PLANT = <fs_rp>-PLANT

          AND  MATERIAL = <fs_rp>-MATERIAL

           AND PSTNG_DATE = <fs_rp>-DATE0 BINARY SEARCH.

Regards,

Hitesh

[ipravir]

Hi Daniel,

You can use below code..which is modified version of your code.

SORT: it_itab1 BY plant material pstng_date,movetype,

it_itab2 BY material,

it_itab3 BY material plant calmonth.

LOOP AT result_package ASSIGNING <fs_rp>.

  CLEAR: wa_itab1, wa_itab2, wa_itab3.

  LOOP AT it_itab1 INTO wa_itab1 WHERE plant = <fs_rp>-plant

                                 AND material = <fs_rp>-material

                                 AND pstng_date = <fs_rp>-date0

                                 AND ( movetype EQ '312' OR movetype EQ '202' ).

    IF wa_itab1-movetype EQ '312'.

      <fs_rp>-/bic/yio_add = <fs_rp>-/bic/yio_add + wa_itab1-quant_b.

    ELSEIF wa_itab1-movetype EQ '202'.

      <fs_rp>-/bic/yio_sub = <fs_rp>-/bic/yio_sub + wa_itab1-quant_b.

    ENDIF.

  ENDLOOP.

  IF <fs_rp>-/bic/yioflag EQ 'Y'.

    READ TABLE it_itab2 INTO wa_itab2 WITH KEY material = <fs_rp>-material BINARY SEARCH.

    IF sy-subrc = 0.

      <fs_rp>-/bic/yioqntpr = wa_itab2-netprice.

      <fs_rp>-loc_currcy = wa_itab2-loc_currcy.

    ENDIF.

  ELSEIF <fs_rp>-/bic/yioflag  EQ 'N'.

    READ TABLE it_itab3 INTO wa_itab3 WITH KEY material =  <fs_rp>-material

                                               plant =  <fs_rp>-plant

                                               calmonth = <fs_rp>-calmonth

                                               BINARY SEARCH.

    IF sy-subrc = 0.

      <fs_rp>-/bic/yioqntpr = wa_itab3-price_val.

      <fs_rp>-loc_currcy = wa_itab3-currency.

    ENDIF.

  ENDIF.

ENDLOOP.

Regards,

Praveer.