Performance Tuning issue

vijay_kumar134 — Fri, 25 Jan 2013 06:24:51 GMT

Friends,

Please help me for tuning the below code.

FORM get_data .

TYPES: BEGIN OF ty_resb,
          bdmng TYPE resb-bdmng,
          shkzg TYPE resb-shkzg,
          ebeln TYPE resb-ebeln,
          ebelp TYPE resb-ebelp,
         END   OF ty_resb.

DATA: lit_resb TYPE TABLE OF ty_resb,
lwa_resb TYPE ty_resb.

SELECT zelement
         zchar FROM zpp_alias
         INTO TABLE git_zpp_alias
         WHERE zelement IN (c_cust_pattern,c_customer,
                            c_mold_line,c_alloy).

IF git_zpp_alias IS NOT INITIAL.
    SELECT atinn
           atnam FROM cabn
           INTO TABLE git_cabn
           FOR ALL ENTRIES IN git_zpp_alias
           WHERE atnam = git_zpp_alias-zchar AND
                 datuv IN r_datuv.
     ENDIF.

* IF git_cabn IS NOT INITIAL. Commented 0113
*Begin of Change 0113
**    SELECT objek
**           atinn
**           atwrt FROM ausp
**           INTO TABLE git_ausp
**           FOR ALL ENTRIES IN git_cabn
**           WHERE "objek BETWEEN c_2ser_zero AND c_2ser_nine AND
**                 atinn = git_cabn-atinn.

IF git_cabn IS NOT INITIAL.   " Added 0113
    SELECT objek                                        "#EC CI_NOFIRST
           atinn
           atwrt FROM ausp
                 INTO TABLE git_ausp
                 FOR ALL ENTRIES IN git_cabn
                 WHERE atinn = git_cabn-atinn. "objek BETWEEN c_2ser_zero AND c_2ser_nine AND
*End of Change 0113
ENDIF.

LOOP AT git_ausp INTO gwa_ausp.
gwa_kna2-kunnr = gwa_ausp-atwrt.

    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
      EXPORTING
        input = gwa_kna2-kunnr
      IMPORTING
        output = gwa_kna2-kunnr.

APPEND gwa_kna2 TO git_kna2.
CLEAR : gwa_ausp , gwa_kna2.
ENDLOOP.

SORT git_kna2 BY kunnr ASCENDING.
DELETE ADJACENT DUPLICATES FROM git_kna2 COMPARING kunnr.

IF git_kna2 IS NOT INITIAL.
    SELECT kunnr
           name1 FROM kna1
           INTO TABLE git_kna1
           FOR ALL ENTRIES IN git_kna2
           WHERE kunnr = git_kna2-kunnr.
ENDIF.

LOOP AT git_kna1 INTO gwa_kna1.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
      EXPORTING
        input = gwa_kna1-kunnr
      IMPORTING
        output = gwa_kna1-kunnr.

MODIFY git_kna1 FROM gwa_kna1 TRANSPORTING kunnr.
ENDLOOP.

IF git_ausp IS NOT INITIAL.

    SELECT atinn
           atzhl
           atwrt FROM cawn
           INTO TABLE git_cawn
           FOR ALL ENTRIES IN git_ausp
           WHERE atinn = git_ausp-atinn
             AND atzhl IN r_atzhl
             AND adzhl IN r_adzhl.
    SORT git_ausp BY atwrt.
    LOOP AT git_cawn INTO gwa_cawn.
      READ TABLE git_ausp INTO gwa_ausp
                          WITH KEY atwrt = gwa_cawn-atwrt BINARY SEARCH.
      IF sy-subrc NE 0.
        DELETE TABLE git_cawn FROM gwa_cawn.
      ENDIF.
    ENDLOOP.
ENDIF.

IF git_cawn IS NOT INITIAL.
    SELECT atinn
           atzhl
           spras
           atwtb FROM cawnt
           INTO TABLE git_cawnt
           FOR ALL ENTRIES IN git_cawn
           WHERE atinn = git_cawn-atinn
             AND atzhl = git_cawn-atzhl.
ENDIF.

SELECT ebeln
         bsart
         aedat FROM ekko
               INTO TABLE git_ekko_ekpo
               WHERE ebeln IN s_ebeln.

DELETE git_ekko_ekpo WHERE bsart NE c_800o.
IF s_aedat IS NOT INITIAL.
DELETE git_ekko_ekpo WHERE aedat NOT IN s_aedat.
ENDIF.

IF git_ekko_ekpo IS NOT INITIAL.

    SELECT ebeln ebelp loekz txz01 matnr werks bednr matkl idnlf pstyp konnr ktpnr
           FROM ekpo
           INTO TABLE git_ekpo
           FOR ALL ENTRIES IN git_ekko_ekpo
                 WHERE ebeln = git_ekko_ekpo-ebeln AND
                       ebelp IN s_ebelp.

    DELETE git_ekpo WHERE werks NE p_werks.
    DELETE git_ekpo WHERE pstyp NE c_pstyp.
    DELETE git_ekpo WHERE loekz NE c_loekz.
    IF s_matkl IS NOT INITIAL.
      DELETE git_ekpo WHERE matkl NOT IN s_matkl.
    ENDIF.
ENDIF.

LOOP AT git_ekpo INTO gwa_ekpo.
MOVE gwa_ekpo TO gwa_ekpo_new.
gwa_ekpo_new-matnr1 = gwa_ekpo-bednr.

    CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'
      EXPORTING
        input        = gwa_ekpo_new-matnr1
      IMPORTING
        output       = gwa_ekpo_new-matnr1
      EXCEPTIONS
        length_error = 1
        OTHERS       = 2.
    IF sy-subrc <> 0.
    ENDIF.

APPEND gwa_ekpo_new TO git_ekpo_new.
ENDLOOP.

git_ekpo[] = git_ekpo_new[].

IF NOT git_ekpo[] IS INITIAL.
    SELECT matnr mtart
           FROM mara
           INTO TABLE git_mara_new
           FOR ALL ENTRIES IN git_ekpo
           WHERE matnr = git_ekpo-matnr1.

*--Pass KONNR and KNTRP as purchase order number to EKPO and get the
*description field as activity
    SELECT ebeln   ebelp
           txz01
           FROM ekpo
           INTO TABLE git_ekpo_act
           FOR ALL ENTRIES IN git_ekpo
           WHERE ebeln = git_ekpo-konnr
             AND ebelp = git_ekpo-ktpnr.
*--end of addition.

*--> Reading EKET data
SELECT ebeln ebelp eindt

          FROM eket
           INTO TABLE git_eket
           FOR ALL ENTRIES IN git_ekpo
           WHERE ebeln = git_ekpo-ebeln
             AND ebelp = git_ekpo-ebelp
             AND etenr IN r_etenr.

*---- Reading ekko data
    SELECT lifnr ebeln bedat
            FROM ekko INTO TABLE git_ekko
                 FOR ALL ENTRIES IN git_ekpo WHERE
                 lifnr IN s_lifnr AND
                 ebeln = git_ekpo-ebeln.

*---- Reading t023t data
    SELECT matkl wgbez
            FROM t023t INTO TABLE git_t023t
           FOR ALL ENTRIES IN git_ekpo WHERE spras = sy-langu AND

matkl = git_ekpo-matkl.

* Reading resb data
    CLEAR git_ekpo1[].
    git_ekpo1[] = git_ekpo[].
    DELETE ADJACENT DUPLICATES FROM git_ekpo1 COMPARING ebeln ebelp.

IF s_matnr IS INITIAL.
*Begin of change 0113
** SELECT xloek matnr charg bdmng meins shkzg ebeln ebelp

**            FROM resb
**                   INTO TABLE git_resb
**                   WHERE
**                         matnr NE space   AND
**                         werks EQ p_werks AND
**                         xloek EQ c_xloek.
**
**      SORT git_ekpo BY ebeln ebelp.
**      LOOP AT git_resb INTO gwa_resb.
**        READ TABLE git_ekpo INTO gwa_ekpo WITH KEY ebeln = gwa_resb-ebeln
**                                                   ebelp = gwa_resb-ebelp BINARY SEARCH.
**        IF sy-subrc NE 0.
**          DELETE git_resb.
**        ENDIF.
**      ENDLOOP.

**      IF NOT s_charg IS INITIAL.
**        DELETE git_resb WHERE charg NOT IN s_charg.
**      ENDIF.

** DELETE git_resb WHERE shkzg NE c_shkzg.

*    ELSE.
      SELECT xloek matnr charg bdmng meins shkzg ebeln ebelp
              FROM resb INTO TABLE git_resb
                   WHERE matnr IN s_matnr AND matnr NE space AND "0113
                         werks EQ p_werks AND
                         xloek EQ c_xloek ."AND

      SORT git_ekpo BY ebeln ebelp.
      LOOP AT git_resb INTO gwa_resb.
        READ TABLE git_ekpo INTO gwa_ekpo WITH KEY ebeln = gwa_resb-ebeln
                                                   ebelp = gwa_resb-ebelp BINARY SEARCH.
      ENDLOOP.

      IF sy-subrc NE 0.
        DELETE git_resb.
      ENDIF.

      IF NOT s_charg IS INITIAL.
        DELETE git_resb WHERE charg NOT IN s_charg.
      ENDIF.

DELETE git_resb WHERE shkzg NE c_shkzg.
ENDIF.

    SORT git_resb BY ebeln ebelp matnr charg ASCENDING.
    DELETE git_resb WHERE charg = ' '.
ENDIF.
*--> Fetching Vendor Name and Location.
IF git_ekko IS NOT INITIAL.
    SELECT lifnr
           name1
           ort01 FROM lfa1
           INTO TABLE git_lfa1
           FOR ALL ENTRIES IN git_ekko
           WHERE lifnr = git_ekko-lifnr.
ENDIF.

IF NOT git_resb[] IS INITIAL.

*--> Fetching H.T Date
SELECT matnr werks charg

           ersda FROM mcha
           INTO TABLE git_mcha
           FOR ALL ENTRIES IN git_resb
           WHERE matnr = git_resb-matnr
             AND werks = p_werks
             AND charg = git_resb-charg.

*-->Fetch Pouring Date
    SELECT matnr werks stlan stlnr
           stlal FROM mast
           INTO TABLE git_mast
           FOR ALL ENTRIES IN git_resb
           WHERE matnr = git_resb-matnr
             AND werks = p_werks.

    IF git_mast IS NOT INITIAL.
      SELECT stlnr
             idnrk FROM stpo
             INTO TABLE git_stpo
             FOR ALL ENTRIES IN git_mast
             WHERE stlty = c_stlty_m"'M'
               AND stlnr = git_mast-stlnr.

      IF git_stpo IS NOT INITIAL.
        SELECT matnr werks charg ersda
                FROM mcha
               INTO TABLE git_pour
               FOR ALL ENTRIES IN git_stpo
               WHERE matnr = git_stpo-idnrk
                 AND werks = p_werks
                 AND charg IN r_charg.
      ENDIF.
    ENDIF.

*---- Reading mara data
    SELECT matnr matkl bismt wrkst ntgew
            FROM mara INTO TABLE git_mara
            FOR ALL ENTRIES IN git_resb WHERE
                 matnr = git_resb-matnr.

IF NOT git_mara[] IS INITIAL.

*---- Reading makt data
      SELECT matnr maktx
      FROM makt INTO TABLE git_makt
             FOR ALL ENTRIES IN git_mara WHERE
             matnr = git_mara-matnr.
    ELSE.
      MESSAGE s001(zpp_messages).
      EXIT.
    ENDIF.
    r_bwart-sign   = c_i.             " 'I'. "0113
    r_bwart-option = c_eq.            " 'EQ'. "0113
    r_bwart-low    = c_541.
    APPEND r_bwart.
    r_bwart-sign   = c_i.             " 'I'. "0113
    r_bwart-option = c_eq.            " 'EQ'. "0113

    r_bwart-low    = c_542.
    APPEND r_bwart.
    r_bwart-sign   = c_i.             " 'I'. "0113
    r_bwart-option = c_eq.            " 'EQ'. "0113
    r_bwart-low    = c_942.
    APPEND r_bwart.
    r_bwart-sign   = c_i.             " 'I'. "0113
    r_bwart-option = c_eq.            " 'EQ'. "0113
    r_bwart-low    = c_941.
    APPEND r_bwart.

    IF git_resb IS NOT INITIAL.
      SORT git_resb BY charg.
      SELECT mblnr mjahr zeile bwart matnr charg shkzg menge meins ebeln ebelp wempf
             FROM mseg CLIENT SPECIFIED
             INTO TABLE git_mseg_pt
             FOR ALL ENTRIES IN git_resb
                   WHERE mandt = sy-mandt
                     AND charg = git_resb-charg
                     AND bwart IN r_bwart.        "(c_541, c_542 , c_942 , c_941).

IF git_mseg_pt IS NOT INITIAL.
SORT git_mseg_pt BY mblnr mjahr.

        SELECT mblnr budat
               FROM mkpf
               INTO TABLE git_mkpf_mseg
               FOR ALL ENTRIES IN git_mseg_pt
               WHERE mblnr = git_mseg_pt-mblnr AND
                     mjahr = git_mseg_pt-mjahr." r_mjahr.
        IF NOT s_budat IS INITIAL.
          IF sy-subrc NE 0.                                 "0113
            DELETE git_mkpf_mseg WHERE budat NOT IN s_budat.
          ENDIF.                                            "0113
        ENDIF.
      ENDIF.

      SORT git_mkpf_mseg BY mblnr.
      LOOP AT git_mseg_pt INTO gwa_mseg_pt.
        READ TABLE git_mkpf_mseg INTO gwa_mkpf_mseg
                                 WITH KEY mblnr = gwa_mseg_pt-mblnr BINARY SEARCH.

        IF sy-subrc NE 0.
          DELETE TABLE git_mseg_pt FROM gwa_mseg_pt.
        ELSE.
          gwa_mseg_pt-budat = gwa_mkpf_mseg-budat.
          MODIFY git_mseg_pt FROM gwa_mseg_pt.
        ENDIF.
        CLEAR: gwa_mkpf_mseg, gwa_mseg_pt.
      ENDLOOP.

      IF sy-subrc NE 0.                                     "0113
        DELETE git_mseg_pt WHERE shkzg NE c_credit.
      ENDIF.                                                "0113

      SORT git_resb BY ebeln ebelp.
      LOOP AT git_mseg_pt INTO gwa_mseg_pt.
        READ TABLE git_resb INTO gwa_resb WITH KEY ebeln = gwa_mseg_pt-ebeln
                                                   ebelp = gwa_mseg_pt-ebelp BINARY SEARCH.

        IF sy-subrc NE 0.
          DELETE TABLE git_mseg_pt FROM gwa_mseg_pt.
        ENDIF.
        CLEAR: gwa_resb, gwa_mseg_pt.
      ENDLOOP.
    ENDIF.
*End of Change 0113

    LOOP AT git_mseg_pt INTO gwa_mseg_pt.
      MOVE: gwa_mseg_pt-mblnr TO gwa_mseg-mblnr,
            gwa_mseg_pt-mjahr TO gwa_mseg-mjahr,
            gwa_mseg_pt-zeile TO gwa_mseg-zeile,
            gwa_mseg_pt-bwart TO gwa_mseg-bwart,
            gwa_mseg_pt-matnr TO gwa_mseg-matnr,
            gwa_mseg_pt-charg TO gwa_mseg-charg,
            gwa_mseg_pt-shkzg TO gwa_mseg-shkzg,
            gwa_mseg_pt-menge TO gwa_mseg-menge,
            gwa_mseg_pt-meins TO gwa_mseg-meins,
            gwa_mseg_pt-ebeln TO gwa_mseg-ebeln,
            gwa_mseg_pt-ebelp TO gwa_mseg-ebelp,
            gwa_mseg_pt-wempf TO gwa_mseg-wempf,
            gwa_mseg_pt-budat TO gwa_mseg-budat.

      APPEND gwa_mseg TO git_mseg.
      CLEAR gwa_mseg.
    ENDLOOP.

    IF git_mseg IS NOT INITIAL.
      SELECT smbln FROM mseg INTO TABLE git_refdoc
                   FOR ALL ENTRIES IN git_mseg
                   WHERE smbln = git_mseg-mblnr.
    ENDIF.
    LOOP AT git_refdoc INTO gwa_refdoc.
      DELETE git_mseg WHERE mblnr = gwa_refdoc-smbln.
      CLEAR : gwa_refdoc.
    ENDLOOP.
*endif.
    SORT git_mseg BY ebeln ebelp charg wempf bwart budat ASCENDING.

    IF git_mseg IS NOT INITIAL.
      CLEAR git_mseg1[].
      git_mseg1[] = git_mseg[].

DELETE ADJACENT DUPLICATES FROM git_mseg1 COMPARING ebeln ebelp.

IF s_matnr IS INITIAL.

*Begin of change 0113
***        SELECT bdmng shkzg ebeln ebelp FROM resb
***                     INTO TABLE lit_resb
***                     WHERE matnr NE space   AND
***                           werks EQ p_werks AND
***                           xloek EQ c_xloek.
***
***        SORT git_mseg1 BY ebeln ebelp.
***        CLEAR gwa_resb.
***        LOOP AT lit_resb INTO lwa_resb.
***          READ TABLE git_mseg1 INTO gwa_mseg WITH KEY ebeln = lwa_resb-ebeln
***                                                      ebelp = lwa_resb-ebelp BINARY SEARCH.
***          IF sy-subrc EQ 0.
***            MOVE: lwa_resb-bdmng TO gwa_resb-bdmng,
***                   lwa_resb-ebeln TO gwa_resb-ebeln,
***                   lwa_resb-ebelp TO gwa_resb-ebelp.
***            APPEND gwa_resb TO git_resb_942.
***          ENDIF.
***        ENDLOOP.
***        SORT git_resb_942 BY ebeln ebelp bdmng.
***        DELETE ADJACENT DUPLICATES FROM git_resb_942 COMPARING ebeln
***        ebelp bdmng.
***      ELSE.
***        SELECT bdmng shkzg ebeln ebelp FROM resb
***                     INTO TABLE lit_resb
***                     WHERE matnr IN s_matnr AND matnr NE space AND
***                           werks EQ p_werks AND
***                           xloek EQ c_xloek.
        CLEAR gwa_resb.
        SORT git_mseg1 BY ebeln ebelp.
**        LOOP AT lit_resb INTO lwa_resb.
        LOOP AT git_resb INTO gwa_resb.
          READ TABLE git_mseg1 INTO gwa_mseg WITH KEY ebeln = gwa_resb-ebeln
                                                       ebelp = gwa_resb-ebelp BINARY SEARCH.
*                                                      ebeln = lwa_resb-ebeln
          IF sy-subrc EQ 0.

            MOVE: gwa_resb-bdmng TO gwa_resb-bdmng,
                  gwa_resb-ebeln TO gwa_resb-ebeln,
                  gwa_resb-ebelp TO gwa_resb-ebelp.

            APPEND gwa_resb TO git_resb_942.
*Begin of Comment 0113
***            MOVE: lwa_resb-bdmng TO gwa_resb-bdmng,       "0113
***                   lwa_resb-ebeln TO gwa_resb-ebeln,
***                   lwa_resb-ebelp TO gwa_resb-ebelp.
***            APPEND gwa_resb TO git_resb_942.
*End of Comment 0113
          ENDIF.
        ENDLOOP.
        SORT git_resb_942 BY ebeln ebelp bdmng.
        DELETE ADJACENT DUPLICATES FROM git_resb_942 COMPARING ebeln ebelp bdmng.
      ENDIF.
*End of change 0113
    ENDIF.

    IF NOT git_mseg[] IS INITIAL.
*----- Reading zpp_pegg data
      SELECT charg sernr vbeln posnr ebeln ebelp edatu begda loekz
                   FROM zpp_pegg_n INTO TABLE git_zpp_pegg
                   FOR ALL ENTRIES IN git_resb WHERE
                   charg = git_resb-charg.
      DELETE git_zpp_pegg WHERE loekz NE ' '.
    ENDIF.
ELSE.
    MESSAGE s001(zpp_messages).
    EXIT.
ENDIF.

PERFORM append_data.
PERFORM field_catalog.
PERFORM display_data.
ENDFORM. " GET_DATA

Thanks.

Viji.

Moderator Message : If you are not ready to listen to the moderator message(s) and do the mistake again and again, it would lead to your id deletion. This is the final warning

Message was edited by: Kesavadas Thekkillath

Re: Performance Tuning issue

Former Member — Fri, 25 Jan 2013 07:53:17 GMT

Hi,

you can try the below steps :

1. You have called thrice Function modules withing Loop / Endloop which is a strict avoid for performance issues.

2. Most of your SELECT queries populated data into git_mara / git_mara_new. I'm sure you can reduce the number of SELECT queries.

Try using Sorted Internal Tables.

3. Instead, try populating data with minimum SELECT queries and do more processing on your internal tables.

4. Use EPC check and Sql Trace also to review performance check of your code.

**Good points u followed

-> clearing work area after append

-> Binary Search.

Thanks

Vivek

topic Re: Performance Tuning issue in Application Development and Automation Discussions

Performance Tuning issue

Re: Performance Tuning issue