Overview: This document provides some information & tips which would be helpful while dealing with ABAP inside BI.


1)   Pre-requisite while Using FOR ALL ENTRIES.

        A) Check Higher table having data or not.

               Ex. IF SOURCE_PACKAGE IS NOT INITIAL.

        B) SORT Internal Table

        C) DELETE the adjacent duplicates

Example:

SELECT F1 F2 F3 FROM /BIC/PTAB INTO TABLE IT_TAB WHERE <condition>.  

     

                IF IT_TAB IS NOT INITIAL.   ”” Check A

                SORT IT_TAB BY F1 F2 F3.    “” Check B

                 DELETE ADJUCENT DUMPLICATES FROM IT_TAB COMPARING F1 F2 F3. “” Check C

—————————————————————————————————————————————————————-

2)  Before Using READ TABLE  Statement with BINARY SEARCH.

      Always Sort table which you are reading using READ TABLE  Statement with BINARY SEARCH based on fields addition to WITH KEY.

      SORT I_TAB1 BY F1 F2.

      READ TABLE I_TAB1 ASSIGNING <FS_TAB> WITH KEY FIELD1 = F1

      FIELD2 = F2 BINARY SEARCH.

—————————————————————————————————————————————————————-

3)  Deletion of records from Source pakage/ result package/any internal table.

Scenario A:

     Deletion of single recordfrom Source pakage/ result package/any internal table.

     Problem scenario:  we have to delete all material which have BLANK value.

     Not recommended 

     Loop at IT_TAB  INTO WA_TAB.

       IF WA_TAB-material  EQ ‘ ’ .

          DELETE T_TAB.

       ENDIF.

     ENDLOOP.

     Recommended 

      DELETE FROM IT_TAB WHERE material EQ ‘ ‘.

Scenario B:

              

    Deletion of multiple records from Source pakage/ result package/any internal table.

    Problem Description: In this scenario, we want to delete multiple plants say p1,p2,p3 from internal table.

    Not recommended

     DELETE FROM I_TAB WHERE plant = ‘p1’.

     DELETE FROM I_TAB WHERE plant = ‘p2’.

     DELETE FROM I_TAB WHERE plant = ‘p3’.

       “ this will degrade performance because we referring whole internal table thrice.

     Recommended

     “ Collect deletion entry into select-options table then use delete statement only once.

     DATA: V1 TYPE FIELD1. “ this is field for which we want to create SELECT-OPTIONS

     SELECT-OPTIONS : S_FIELD1 FOR V1.

      DATA : WA LIKE LINE OF S_FIELD1.

          WA-SIGN = ‘I’.

          WA-OPTIONS =  ‘EQ’.

          WA-LOW = ‘P1’.

          APPEND WA TO S_FIELD1.

          WA-SIGN = ‘I’.

          WA-OPTIONS =  ‘EQ’.

          WA-LOW = ‘P2’.

          APPEND WA TO S_FIELD1.

          WA-SIGN = ‘I’.

          WA-OPTIONS =  ‘EQ’.

          WA-LOW = ‘P3’.

          APPEND WA TO S_FIELD1.

          DELETE FROM I_TAB WHERE  plant IN S_FIELD1.

         “Use DELETE statement once

———————————————————————————————————————————————————–

4)      Avoid nesting Loop as much as possible.

Scenario A:In this scenario, we have two internal table (itab1 and itab2), in itab we  have unique material number and we need to update plant from itab2 into                          itab1 based on material number.

      Not Recommended

        LOOP AT itab1 INTO wa_tab1.

           LOOP AT itab2 INTO wa_tab2 WHERE matnr = wa_tab1-matnr.

               Wa_tab1-matnr = wa_tab2-matnr.

           ENDLOOP.

        ENDLOOP.

       Recommended   

       LOOP AT itab1 ASSGNNG <FS_tab1>.

            READ TABLE TAB2 ASSIGNING <FS_TAB2> WITH KEY matnr = <FS_tab2> BINARY SEARCH.

              IF sy-subrc  EQ 0 .

                  <FS_tab1>-plant = <FS_tab2>-plant.

              ENDIF.

        ENDLOOP.

Scenario B : In this scenario, we consider how to use parallel Cursor method.

Many times in bi, we need to write a routine where we have header and line item table, so for each entry in header table we want to do processing for multiple item in item table.

Problem Description: we have 2 internal tales IT_HEADER & IT_ITEM for each records in outer (header) table we want to manipulate inner(item) table.

So suppose outer table have 100 records and inner table have 100 records then for each record in outer table , inner table will always look up 100 times. Means looping process takes will execute 100 * 100 = 10000 times. This will degrade runtime performance. To avoid this we will implement Parallel Cursor method in this scenario.

    Not Recommended  

     DATA : IT_VBAK TYPE STANDARD TABLE OF VBAK,

         IT_VBAP TYPE STANDARD TABLE OF VBAP,

         WA_VBAK TYPE VBAK,

         WA_VBAP TYPE VBAP,

         LV_TABIX TYPE SY-TABIX.

        SELECT * FROM VBAK INTO IT_VBAK WHERE <<condition>>.

        SELECT * FROM VBAP INTO IT_VBAP WHERE <<condition>>.

         LOOP AT VBAK INTO WA_VBAK.

            LOOP AT VBAP INTO WA_VBAP WHERE VBELN = WA_VBAK-VBELN.

                     “”””” ABAP Coding statements

            ENDLOOP.

         ENDLOOP.

          “ This will degrade and results in very poor performance.

       Recommended 

    Things to keep in mind before using parallel cursor method.

         A) Always sort both internal tables those which are using in nesting looping

         B) Read inner internal table(IT_VBAP) with key WA_VBAK-VBELN  with BINARY SEARCH.

         C) LOOP on inner table(IT_VBAP) with table index which we got from step B. this will give starting position for inner table reading.

               DATA : IT_VBAK TYPE STANDARD TABLE OF VBAK,

                            IT_VBAP TYPE STANDARD TABLE OF VBAP,

                            WA_VBAK TYPE VBAK,

                            WA_VBAP TYPE VBAP,

                            LV_TABIX TYPE SY-TABIX.

               SELECT * FROM VBAK INTO IT_VBAK WHERE <<condition>>.

               SELECT * FROM VBAP INTO IT_VBAP WHERE <<condition>>.

                 SORT IT_VBAK BY VBELN.

                 SORT IT_VBAP BY VBELN.

               LOOP AT IT_VBAK INTO WA_VBAK.

                     READ TABLE IT_VBAP INTO WA_VBAP TRANSPORTING NO FIELDS WITH KEY

                                                                                 VBELN = WA_VBAK-VBELN BINARY SEARCH.

                      IF SY-SUBRC = 0.

                           LV_TABIX = SY-TABIX.

                               LOOP AT IT_VBAP INTO WA_VBAP FROM LV_TABIX.

                                  IF WA_VBAP-VBELN EQ WA_VBAK-VBELN.

                                             “ ABAP Logic

                                   ELSE.

                                               EXIT.           “ Exit Looping if condition not statisies

                                   ENDIF.

                                ENDLOOP.        

                     ENDIF.

               ENDLOOP.

———————————————————————————————————————————————————–

5) Some General Optimization Tips/Check-list :-

     a) Do not use asterisk (*) in SELECT statements. It means not to select unnecessary columns from database.

      b) Avoiding SELECT or SELECT SINGLE within a loop.(hit performance very badly L)

      c) Usage of hashed tables where a single record within the table is to be searched.

      d) Do not use nested SELECT statements. Rather use sub queries or inner joins.

      e) Use Transactions like SAT/ST05(sql trace)/SE30 for performance tuning

      f) Use Field Symbol instead of Work Area whenever possible

      g) Always specify as many primary keys as possible in WHERE clause to make the Select efficient

      h) Wild cards like ‘A%’ is avoided as much as possible(decrease query performanceL )

      i) For copying internal tables use ‘=’ operator instead of Looping & Appending (ex. IT_tab1[] = IT_tab2[] )

      j) DELETE or SORT is not used on a hashed table since it increases memory consumption

      k) Always when performing database updates:

            I) Lock data to be edited

            II) Read data from database for current data

           III) Process and write data to database

           IV) Release the lock

**************************** Thanks for your Time   ************************************** 

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Benedict Venmani Felix

    Nice 🙂 and thank you Hitesh. Might use the parallel cursor in one of my transformations. I wanted to aggregate certain values in a internal table based on another table and was wondering how best to do it without a loop inside a loop.

    Benedict

    (0) 
    1. Benedict Venmani Felix

      Thanks Hitesh ans Suhas. I am trying to calculate MTD, QTD and YTD in my transformation(Please don’t ask me why I am not doing it in my query 😥 ).

      Since BW transformations run in package sizes a ‘select with aggregation’ inside a loop was not an option.

      At first I used two loops, one inside other 🙁 but performance was as expected. Right now I am doing a sort of all the fields in the internal table and using the ‘AT NEW’, which BTW is the first time I am using this.

      Here’s the pseudo code;  Hope this explains

      SORT it_result_package BY f1 f2 f3 f4 fiscyear fiscper.

      Loop through it_result_package.

           AT NEW f3.

                CLEAR YTD.

           ENDAT.

           YTD = YTD + Value1

      Endloop.

      I came across this COLLECT statement yesterday in one of the discussions and I am yet to try and see it will serve my purpose. Will take a look at the REDUCE statement too. I am 5/10 when it comes to ABAP 😐

      Benedict

      (0) 
      1. HITESH GAVANDE Post author

        Hello Benedict,

        pls use below code: here i m doing ||el cursor on the same internal table. 😛

        SORT it_result_package BY f1 f2 f3 f4 fiscyear fiscper.

        lv_index = 0.

        loop at it_result_package assigning <result_fields_1> from lv_index1.

        lv_index = lv_index1 + 1.

          loop at it_result_package assigning <result_fields_2> from lv_index.

             if <result_fields_2>-F3 = <result_fields_1>-F3.

                YTD = YTD + Value1. 

                       ” instead of your NEW, i m using ||el cursor on the same internal table

             else.

                lv_index1 = lv_index.   ” set index for next loop run

                exit.

             endif.

          endloop.

        endloop.

        this code definately improve performance becoz here we are not looping on every record.(means if we have 1000 records then looping must be less than 1000 , which is not in case of your previos code 🙂 )

        implement same proto type of code and check.

        Regards

        Hitesh

        (0) 
          1. Suhas Karnik

            That’s probably not all that you’re doing with the YTD calculation. I’m guessing that the “YTD” value needs to be stored somewhere, and as this is the End Routine, you might want to populate the calculated YTD for each F3 value into each record.

            If that’s the case, then the nested loop (with || cursors) won’t help. You will need two loops; one for calculating YTD and one for populating the YTD in the Result Package. They have to be two loops because you cannot populate the YTD until you’ve calculated it by looping through all the records first. The two loops do not need to be nested though.

            (0) 
            1. HITESH GAVANDE Post author

              Hi Suhas,

              yes .. that will probably another case..

              although let wait to try this code by benedict with some additional changes..

              @Benedict Venmani Felix : may requires some additional changes in addition to above code.

                                                  lets try that, if still getting issue then pls post new thread so we                                         can get some more views from experts 😉  

              (0) 

Leave a Reply