Handy Tips & Scenarios for ABAP in BI

[former_member183519]

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   **************************************