Skip to Content
0
Former Member
Feb 18, 2009 at 08:42 AM

Query Tuning for Performance

154 Views

Dear Experts,

Please find herewith below my code. The problem is with KONV table... This report is based on many conditions of KONV table... So the specific table has to be READ many times with all data. How can i Fine tune it... The report is taking a long time to execute... Any commands or way to optimise the report..

SELECT
        vbeln_i
        vkorg
        vtweg
        spart
        fkart
        land1
        fkdat
        kunrg
        knumv
        waerk
        kurrf
        vkbur_i
        vkgrp_i
        matnr_i
        vrkme_i
        fkimg_i
        matkl_i
        posnr_i
        netwr_i
        augru_auft_i
        aubel_i
        vgbel_i
        pstyv_i
        vstel_i
        prctr_i
        FROM wb2_v_vbrk_vbrp2 INTO corresponding fields of TABLE it_vbrk
        WHERE
        vkorg = p_vkorg AND
        vtweg = '30' AND
        spart IN so_spart AND
        kunrg IN so_kunrg AND
        fkart IN so_fkart AND
        vbeln_i IN so_vbeln AND
        fkdat IN so_fkdat AND
        vkbur_i IN so_vkbur AND
        vkgrp_i IN so_vkgrp AND
        matnr_i IN so_matnr AND
        fkimg_i <> '0'.


sort it_vbrk by vbeln_i knumv posnr_i.

  IF it_vbrk[] IS NOT INITIAL.

    SELECT
          knumv
          kposn
          stunr
          kschl
          krech
          kbetr
          kpein
          waers
          kmein
          kkurs
          kinak
          FROM konv INTO TABLE it_konv
          FOR ALL ENTRIES IN it_vbrk
          WHERE knumv = it_vbrk-knumv AND
          kschl IN ('ZPR0','Z305','ZCCM','ZFCM').

sort it_konv by knumv.

    SELECT
          belnr
          hkont
          prctr
          dmbtr
          matnr
          werks
          FROM bseg INTO TABLE it_fin
          FOR ALL ENTRIES IN it_vbrk
          WHERE belnr = it_vbrk-vbeln_i AND
          matnr = it_vbrk-matnr_i AND
          werks = it_vbrk-vstel_i AND
          prctr = it_vbrk-prctr_i.

sort it_fin by belnr.

    LOOP AT it_vbrk INTO wa_vbrk.

      wa_data-vbeln = wa_vbrk-vbeln_i.
      wa_data-vkbur = wa_vbrk-vkbur_i.
      wa_data-vkgrp = wa_vbrk-vkgrp_i.
      wa_data-matnr = wa_vbrk-matnr_i.
      wa_data-vrkme = wa_vbrk-vrkme_i.
      wa_data-matkl = wa_vbrk-matkl_i.
      wa_data-posnr = wa_vbrk-posnr_i.
      wa_data-netwr = wa_vbrk-netwr_i.
      wa_data-reas = wa_vbrk-reas_i.
      wa_data-aubel = wa_vbrk-aubel_i.
      wa_data-vgbel = wa_vbrk-vgbel_i.
      wa_data-pstyv = wa_vbrk-pstyv_i.
      wa_data-vkorg = wa_vbrk-vkorg.
      wa_data-vtweg = wa_vbrk-vtweg.
      wa_data-spart = wa_vbrk-spart.
      wa_data-fkart = wa_vbrk-fkart.
      wa_data-land1 = wa_vbrk-land1.
      wa_data-fkdat = wa_vbrk-fkdat.
      wa_data-kunrg = wa_vbrk-kunrg.
      wa_data-knumv = wa_vbrk-knumv.
      wa_data-waerk = wa_vbrk-waerk.
      wa_data-kurrf = wa_vbrk-kurrf.
      wa_data-prctr = wa_vbrk-prctr_i.
      wa_data-vstel = wa_vbrk-vstel_i.

*    move-corresponding wa_vbrk to wa_data.
*********************************************************************************************
      READ TABLE it_fin WITH KEY belnr = wa_data-vbeln matnr = wa_data-matnr.

      if it_fin-belnr = wa_data-vbeln.
      wa_data-hkont = it_fin-hkont.

      SELECT SINGLE txt20 INTO wa_data-gltxt FROM skat WHERE spras = 'EN' AND saknr = wa_data-hkont AND ktopl = '1000'.
      endif.

*********************************************************************************************

      SELECT SINGLE maktx FROM makt INTO wa_data-maktx WHERE matnr = wa_data-matnr.

      SELECT SINGLE zeinr matkl FROM mara INTO (wa_data-zeinr,wa_data-matkl) WHERE matnr = wa_data-matnr.

      SELECT SINGLE bezei FROM tvkbt INTO wa_data-soff WHERE vkbur = wa_data-vkbur AND spras = 'E'.

      SELECT SINGLE bezei FROM tvgrt INTO wa_data-sgrp WHERE vkgrp = wa_data-vkgrp AND spras = 'E'.

      SELECT SINGLE vtext INTO wa_data-division FROM tspat WHERE spart = wa_data-spart AND spras = 'EN'.

* added on 23.06.2008

      READ TABLE it_konv INTO wa_konv WITH KEY knumv = wa_data-knumv kposn = wa_data-posnr kschl = 'ZPR0' kinak = ''.

      wa_data-kschl = wa_konv-kschl.
      wa_data-kposn = wa_konv-kposn.
      wa_data-stunr = wa_konv-stunr.
      wa_data-kbetr = wa_konv-kbetr.
      wa_data-kpein = wa_konv-kpein.
      wa_data-kkurs = wa_konv-kkurs.

* This is calculating per unit rate

      wa_data-rate = ( wa_data-kbetr * wa_data-kpein ) / wa_data-kpein .
      wa_data-drate = wa_data-rate.
      CLEAR wa_konv.

      READ TABLE it_konv INTO wa_konv WITH KEY knumv = wa_data-knumv kposn = wa_data-posnr kschl = 'Z305' krech = 'A'.
      IF wa_konv-kbetr <> 0.
        wa_data-kbetr1 = ABS( wa_konv-kbetr ).
        wa_data-drate =  wa_data-rate  - ( wa_data-rate * ( ( wa_data-kbetr1 / 10 ) / 100 ) ).
        wa_data-dperc = ( wa_data-kbetr1 / 10 ).

        IF  wa_data-fkart = 'S1' OR wa_data-fkart = 'RE' OR wa_data-fkart = 'ZRES' OR wa_data-fkart = 'Z2RE' OR wa_data-fkart = 'G2' .
          IF wa_data-pstyv = 'TANN' OR wa_data-pstyv = 'ZMOH' OR wa_data-pstyv = 'ZPHY' OR wa_data-pstyv = 'ZSAM' OR wa_data-pstyv = 'ZZNN' OR wa_data-pstyv = 'ZREN1'.

*    IF WA_DATA-PSTYV = 'TANN' OR WA_DATA-PSTYV = 'ZMOH' OR WA_DATA-PSTYV = 'ZPHY' OR WA_DATA-PSTYV = 'ZSAM' or wa_data-pstyv = 'ZZNN'.
            wa_data-total = '0'.
            wa_data-fkimg = wa_vbrk-fkimg_i * -1.
          ELSE.
            wa_data-fkimg = wa_vbrk-fkimg_i * -1.
            wa_data-total = ( ( wa_data-fkimg * wa_data-drate ) ) / wa_data-kpein.
            wa_data-inrvalue = wa_data-total * wa_data-kurrf.
          ENDIF.
        ELSE.
          IF wa_data-pstyv = 'TANN' OR wa_data-pstyv = 'ZMOH' OR wa_data-pstyv = 'ZPHY' OR wa_data-pstyv = 'ZSAM' OR wa_data-pstyv = 'ZZNN'.
            wa_data-fkimg = wa_vbrk-fkimg_i.
            wa_data-total = '0'.
            wa_data-inrvalue = wa_data-total * wa_data-kurrf.
          ELSE.
            wa_data-fkimg = wa_vbrk-fkimg_i.
            wa_data-total =   ( wa_data-fkimg * wa_data-drate )  / wa_data-kpein.
            wa_data-inrvalue = wa_data-total * wa_data-kurrf.
          ENDIF.
        ENDIF.

      ELSE.

        READ TABLE it_konv INTO wa_konv WITH KEY knumv = wa_data-knumv kposn = wa_data-posnr kschl = 'Z305' krech = 'B'.
        IF wa_konv-kbetr <> 0.
           wa_data-kbetr1 = ABS( wa_konv-kbetr ).
          wa_data-drate = wa_data-rate.
          wa_data-dvalue = wa_data-kbetr1.

          IF  wa_data-fkart = 'S1' OR wa_data-fkart = 'RE' OR wa_data-fkart = 'ZRES' OR wa_data-fkart = 'Z2RE' OR wa_data-fkart = 'G2' .
            IF wa_data-pstyv = 'TANN' OR wa_data-pstyv = 'ZMOH' OR wa_data-pstyv = 'ZPHY' OR wa_data-pstyv = 'ZSAM' OR wa_data-pstyv = 'ZZNN' OR wa_data-pstyv = 'ZREN1'.

*    IF WA_DATA-PSTYV = 'TANN' OR WA_DATA-PSTYV = 'ZMOH' OR WA_DATA-PSTYV = 'ZPHY' OR WA_DATA-PSTYV = 'ZSAM' or wa_data-pstyv = 'ZZNN'.
              wa_data-total = '0'.
              wa_data-fkimg = wa_vbrk-fkimg_i * -1.
            ELSE.
              wa_data-fkimg = wa_vbrk-fkimg_i * -1.
              wa_data-total = ( ( wa_data-fkimg * wa_data-rate ) - wa_data-kbetr1 ) * -1.
              wa_data-inrvalue = wa_data-total * wa_data-kurrf.
            ENDIF.
          ELSE.
            IF wa_data-pstyv = 'TANN' OR wa_data-pstyv = 'ZMOH' OR wa_data-pstyv = 'ZPHY' OR wa_data-pstyv = 'ZSAM' OR wa_data-pstyv = 'ZZNN'.
              wa_data-fkimg = wa_vbrk-fkimg_i.
              wa_data-total = '0'.
              wa_data-inrvalue = wa_data-total * wa_data-kurrf.
            ELSE.
              wa_data-fkimg = wa_vbrk-fkimg_i.
              wa_data-total = ( ( wa_data-fkimg * wa_data-rate ) - wa_data-kbetr1 ).
              wa_data-inrvalue = wa_data-total * wa_data-kurrf.
            ENDIF.
          ENDIF.

        ENDIF.
      ENDIF.

      CLEAR wa_konv.

      IF wa_data-kbetr1 EQ '0'.
        IF  wa_data-fkart = 'S1' OR wa_data-fkart = 'RE' OR wa_data-fkart = 'ZRES' OR wa_data-fkart = 'Z2RE' OR wa_data-fkart = 'G2' .
          IF wa_data-pstyv = 'TANN' OR wa_data-pstyv = 'ZMOH' OR wa_data-pstyv = 'ZPHY' OR wa_data-pstyv = 'ZSAM' OR wa_data-pstyv = 'ZZNN' OR wa_data-pstyv = 'ZREN1'.

*    IF WA_DATA-PSTYV = 'TANN' OR WA_DATA-PSTYV = 'ZMOH' OR WA_DATA-PSTYV = 'ZPHY' OR WA_DATA-PSTYV = 'ZSAM' or wa_data-pstyv = 'ZZNN'.
            wa_data-total = '0'.
            wa_data-fkimg = wa_vbrk-fkimg_i * -1.
          ELSE.
            wa_data-fkimg = wa_vbrk-fkimg_i * -1.
            wa_data-total = ( ( wa_data-fkimg * wa_data-drate ) ) / wa_data-kpein.
            wa_data-inrvalue = wa_data-total * wa_data-kurrf.
          ENDIF.
        ELSE.
          IF wa_data-pstyv = 'TANN' OR wa_data-pstyv = 'ZMOH' OR wa_data-pstyv = 'ZPHY' OR wa_data-pstyv = 'ZSAM' OR wa_data-pstyv = 'ZZNN'.
            wa_data-fkimg = wa_vbrk-fkimg_i.
            wa_data-total = '0'.
            wa_data-inrvalue = wa_data-total * wa_data-kurrf.
          ELSE.
            wa_data-fkimg = wa_vbrk-fkimg_i.
            wa_data-total =   ( wa_data-fkimg * wa_data-drate )  / wa_data-kpein.
            wa_data-inrvalue = wa_data-total * wa_data-kurrf.
          ENDIF.
        ENDIF.

      ENDIF.

      SELECT SINGLE name1 land1 FROM kna1 INTO (wa_data-name1,wa_data-land1) WHERE kunnr = wa_data-kunrg.

      SELECT SINGLE landx FROM t005t INTO wa_data-landx WHERE land1 = wa_data-land1 AND spras = 'EN'.

      SELECT SINGLE wgbez FROM t023t INTO wa_data-wgbez WHERE matkl = wa_data-matkl.

      SELECT SINGLE bezei FROM tvm1t INTO wa_data-bezei WHERE mvgr1 = wa_data-mvgr3.

      SELECT SINGLE bezei FROM tvm1t INTO wa_data-bezei1 WHERE mvgr1 = wa_data-mvgr4.


      APPEND wa_data TO it_data.

Please reply...

Thanks,

Regards,

Jitesh