Solved: Query Tuning for Performance

Former Member · ‎2009 Feb 18

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

Former Member · ‎2009 Feb 18

Hi,

Dont put select quaries in loop. Its affect performance.First collect all the data in temporary tables.

then read these tables according to the data collected in final table.

Also use ranges for select quary of konv.

also use sort table & make indexing like this.

for example:

w_index = sy-index + 1.

READ TABLE it_vbrk INTO wa_vbrk INDEX w_index.

IF sy-subrc EQ 0.

w_tabix = sy-tabix + w_tabix.

LOOP AT it_vbrk INTO wa_vbrk FROM w_tabix.

endloop.

endif.

or u can use hashed table with unique keys.

Also check in se30 whether the coding takes time or database fetching takes time.

Regards,

Anagha Deshmukh

Former Member · ‎2009 Feb 18

Hello,

U can check likes these steps

From ABAP/4 development workbench select Test - Runtime Analysis.

From ABAP/4 editor, select utilities - more utilities - Runtime Analysis.

From ABAP/ source code screen, select Execute - Runtime Analysis.

From R3 screen, select System - Utilities - Runtime Analysis.

Entering Transaction code SE30 in the command field.

Thank u,

santhosh

kiran_k8 · ‎2009 Feb 18

Jitesh,

Can you please let me know where this Wet Bulb Globe Temprature comes into picture.Are you working on Industry Specific Modules ?

K.Kiran.

Former Member · ‎2009 Feb 18

mine is Pharma Industry...i did nt get ur question

Former Member · ‎2009 Feb 18

Field knumv is not a key field in table VBRK (database view WB2_V_VBRK_VBRP2) ..

so u might get duplicate value's for this ..

Try like this ..

declare another internal table .. let knumv be the first field in it_vbrk1

loop at it_vbrk.

move corresponding it_vbrk to it_vbrk1.

append it_vbrk1.

endloop.

sort it_vbrk1 by knumv.

delete adjacent duplicates from it_vbrk1 comparing knumv.

*this has unique values for the field knumv ...

now use this internal table it_vbrk1 to fetch data from table .. konv ..

Former Member · ‎2009 Feb 18

I want to use the field POSNR of konv also... I am reading the condition material wise and using it in the report.

Former Member · ‎2009 Feb 18

Hi,

Dont put select quaries in loop. Its affect performance.First collect all the data in temporary tables.

then read these tables according to the data collected in final table.

Also use ranges for select quary of konv.

also use sort table & make indexing like this.

for example:

w_index = sy-index + 1.

READ TABLE it_vbrk INTO wa_vbrk INDEX w_index.

IF sy-subrc EQ 0.

w_tabix = sy-tabix + w_tabix.

LOOP AT it_vbrk INTO wa_vbrk FROM w_tabix.

endloop.

endif.

or u can use hashed table with unique keys.

Also check in se30 whether the coding takes time or database fetching takes time.

Regards,

Anagha Deshmukh

Rushikesh_Yeole · ‎2009 Mar 26

This message was moderated.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Query Tuning for Performance