Skip to Content
0
Former Member
Mar 23, 2009 at 03:02 PM

Performance issue with Loop under Loop

29 Views

Hi,

I have an issue with the performance, as per the requirement I have to use the Loop under Loop logic.

I have sorted the Where condition field in Parent Loop and also in Child Loop like below :

SELECT GUID_PRCTSC   "Primary Key as GUID in "RAW" Format
         GUID_PR       "Primary Key as GUID in "RAW" Format
         STCTS         "Numbering Scheme for Customs Tariff System
         DATAB         "Definitive Date (Valid-From Time)
         GUID_CTSNUMC  "Primary Key as GUID in "RAW" Format
         FROM /SAPSLL/PRCTSC
         INTO TABLE T_PRCTSC
         WHERE STCTS IN S_STCTS.

  IF T_PRCTSC IS INITIAL.
    MESSAGE : I007(ZMSSG) WITH 'Data not available for this entry'.
    STOP.
  ENDIF.


SORT T_PRCTSC BY GUID_PR.


  SELECT GUID_PRGEN  "Primary Key as GUID in "RAW" Format "+  DG1K902277
         GUID_PR     "Primary Key as GUID in "RAW" Format
         ATTR05A     "Materail Type
         ATTR05B     "Sub-Family
         ATTR05C     "SPEC BUS + DG1K902190
         ATTR10A     "Materail Group
         ATTR20A     "SUBSTANCE ID
         FROM /SAPSLL/PRGEN
         INTO TABLE T_PRGEN
         FOR ALL ENTRIES IN T_TCOATV20
         WHERE ATTR20A EQ T_TCOATV20-ATTRV20
           AND ATTR05A IN S_ATR5A " +DG1K902168
           AND ATTR05C IN S_ATR5C. " + DG1K902190

  IF T_PRGEN IS INITIAL.
    MESSAGE : I007(ZMSSG) WITH 'Data not available for this entry'.
    STOP.
  ENDIF.

*N-23
SORT T_PRGEN BY GUID_PR.

There are 90,000 records available in the table T_PRGEN.

LOOP AT T_PRGEN INTO WA_PRGEN.

    IF SY-SUBRC = 0.
       WA_FINAL-ATTR05A       = WA_PRGEN-ATTR05A.
       WA_FINAL-ATTR05B       = WA_PRGEN-ATTR05B.
       WA_FINAL-ATTR05C       = WA_PRGEN-ATTR05C. " + DG1K902190
       WA_FINAL-ATTR10A       = WA_PRGEN-ATTR10A.
       WA_FINAL-ATTR20A       = WA_PRGEN-ATTR20A.
    ENDIF.


    READ TABLE T_V_TCAV201 INTO WA_V_TCAV201 WITH KEY ATTRV20 = WA_PRGEN-ATTR20A.

    IF SY-SUBRC = 0.
      WA_FINAL-TEXT1   = WA_V_TCAV201-TEXT1.    "SUBID-TEXT1
    ENDIF.

    READ TABLE T_PNTPR INTO WA_PNTPR WITH KEY GUID_PR = WA_PRGEN-GUID_PR.

    IF SY-SUBRC = 0.
       WA_FINAL-PRVSY  = WA_PNTPR-PRVSY.   "PROD NO
       WA_FINAL-GRVSY  = WA_PNTPR-GRVSY.   "LOGICAL SYS GROUP
    ENDIF.

**&------------------------------------------------&
* TO Remove the Leading Zeros from prvsy
*&------------------------------------------------&
    SHIFT WA_FINAL-PRVSY LEFT DELETING LEADING '0'.

    READ TABLE T_CORSTA INTO WA_CORSTA WITH KEY GUID_MOBJ = WA_PNTPR-GUID_PR.

    IF SY-SUBRC = 0.
      WA_FINAL-QUAL_STA  = WA_CORSTA-QUAL_STA.
    ENDIF.

    READ TABLE T_PR INTO WA_PR WITH KEY GUID_PR = WA_PNTPR-GUID_PR.

*&--------------------------------------------------------&
*& IN THE PROD MASTER PRODUCT CHANGED ON VALUE HAS BEEN   &
*& MAINTAINED AS SINGLE '0', THIS WILL CAUSE ISSUES WHILE &
*&  USING CONVERSION EXIT TO DISPLAY THE DATE FORMAT IN   &
*&  MM/DD/YYYY HH:MM:SEC                                  &
*&--------------------------------------------------------&
    IF SY-SUBRC = 0.
      IF WA_PR-CHTSP = '0'.
        WA_FINAL-CRTSP   = WA_PR-CRTSP.
        wa_final-chtsp   = W_DATE.
      ENDIF.

      IF WA_PR-CHTSP NE '0'.
        WA_FINAL-CRTSP   = WA_PR-CRTSP.
        wa_final-chtsp   = WA_PR-CHTSP.
      ENDIF.
    ENDIF.

    READ TABLE T_PRT INTO WA_PRT WITH KEY GUID_PR = WA_PR-GUID_PR.

    IF SY-SUBRC = 0.
      WA_FINAL-PRTXT   = WA_PRT-PRTXT.
    ENDIF.

 LOOP AT T_PRCTSC INTO WA_PRCTSC WHERE GUID_PR = WA_PRGEN-GUID_PR. "+DG1K902258  - Performance issue

        IF SY-SUBRC = 0.

*&--------------------------------------------------------&
*& TO FILL ATTR20A,PRVSY FOR DIFF STCTS AND CCNGN FOR     |
*&  EACH LOOP                                             |
*&--------------------------------------------------------&
          IF WA_FINAL-ATTR20A  IS INITIAL
          AND  WA_FINAL-PRVSY IS INITIAL.

            IF WA_PRGEN-GUID_PR = WA_PNTPR-GUID_PR. "This condition is to fill up all the rows for the same 
                                                    " Subid which have multiple stcts fields. 
           IF SY-SUBRC = 0.
              WA_FINAL-ATTR05A       = WA_PRGEN-ATTR05A.
              WA_FINAL-ATTR05B       = WA_PRGEN-ATTR05B.
              WA_FINAL-ATTR05C       = WA_PRGEN-ATTR05C. " + DG1K902190
              WA_FINAL-ATTR10A       = WA_PRGEN-ATTR10A.
              WA_FINAL-ATTR20A       = WA_PRGEN-ATTR20A.
             ENDIF.

              IF SY-SUBRC = 0.
                WA_FINAL-TEXT1   = WA_V_TCAV201-TEXT1.    "SUBID-TEXT1
              ENDIF.

              IF SY-SUBRC = 0.
                WA_FINAL-PRVSY  = WA_PNTPR-PRVSY.   "PROD NO
                WA_FINAL-GRVSY  = WA_PNTPR-GRVSY.   "LOGICAL SYS GROUP
              ENDIF.

**&------------------------------------------------&
* TO Remove the Leading Zeros from prvsy
*&------------------------------------------------&
              SHIFT WA_FINAL-PRVSY LEFT DELETING LEADING '0'.

              IF SY-SUBRC = 0.
                WA_FINAL-QUAL_STA  = WA_CORSTA-QUAL_STA.
              ENDIF.

              IF SY-SUBRC = 0.

                IF WA_PR-CHTSP = '0'.
                  WA_FINAL-CRTSP   = WA_PR-CRTSP.
                  WA_final-chtsp   = W_DATE.
                ENDIF.

                IF WA_PR-CHTSP NE '0'.
                  WA_FINAL-CRTSP   = WA_PR-CRTSP.
                  WA_final-chtsp   = WA_PR-CHTSP.
                ENDIF.
              ENDIF.

              IF SY-SUBRC = 0.
                WA_FINAL-PRTXT   = WA_PRT-PRTXT.
              ENDIF.
            ENDIF.
          ENDIF.

      IF SY-SUBRC = 0. " + DG1K902198

            WA_FINAL-GUID_PR      = WA_PRCTSC-GUID_PR.
            WA_FINAL-STCTS        = WA_PRCTSC-STCTS.
            WA_FINAL-DATAB        = WA_PRCTSC-DATAB.       " + DG1K902198
          ENDIF.

         READ TABLE T_CTSNUMC INTO WA_CTSNUMC WITH KEY GUID_CTSNUMC = WA_PRCTSC-GUID_CTSNUMC.

          IF SY-SUBRC = 0.
            WA_FINAL-CCNGN        = WA_CTSNUMC-CCNGN.
          ENDIF.

          APPEND WA_FINAL TO T_FINAL.
          CLEAR WA_FINAL.
        ENDIF.

    ENDLOOP.


    APPEND WA_FINAL TO T_FINAL.
    CLEAR WA_FINAL.

  ENDLOOP.

Any suggestions to improve the performance will be appreciated!

Thanks & Regards,

Kittu

Edited by: Kittu on Mar 23, 2009 4:03 PM