performance concern

[Former Member]

Dear Experts,

The below query is causing a lot of performance concern. Kindly go through and let me know about the suitable modifications i can make.

select
          vbeln
          fkart
          vkorg
          vtweg
          fkdat
          sum( fkimg ) as fkimg
          matnr
          aubel
          vstel
          ktgrm
          matkl
          prctr
          spart
          SAKN1
          from ZV_BSEG_VBRP_RK
          into table it_sales
          where
          fkdat_i in s_fkdat and
          vtweg in s_vtweg and
          vkorg in s_vkorg and
          fkart in so_fkart and
          spart in s_spart and
          vstel in s_werks and
          matnr in s_matnr and
          vbeln in s_vbeln and
          fkimg ne '0' and
          ktgrm in ('01','02','03','04','05','06','07','08','09','10','11','12') and
          fksto ne 'X'
          group by vbeln fkart vkorg vtweg fkdat matnr aubel vstel ktgrm matkl prctr spart SAKN1.
.
    sort it_sales by vbeln matnr prctr.

    if it_sales[] is not initial.

select vbeln fkdat GJAHR VKORG from vbrk into table it_vbeln for all entries in it_sales where vbeln = it_sales-vbeln.

    select
          belnr
          shkzg
          dmbtr
          hkont
          MATNR
          prctr
          from bsEG into table it_fin1
          for all entries in it_vbeln
          where belnr = it_vbeln-vbeln AND
          BUKRS EQ IT_VBELN-VKORG and
          hkont >= '0000400001' and hkont <= '0000400251'.
.
*          hkont = it_sales-sakn1.
*          group by belnr shkzg hkont prctr.
*
          SORT IT_FIN BY hkont.

*delete it_fin where hkont >= '0000400001' and hkont <= '0000400251'.

          SORT IT_FIN BY BELNR MATNR PRCTR.

        IF it_fin1[] IS NOT INITIAL.

loop at it_fin1.
move-corresponding it_fin1 to it_fin.
collect it_fin.
append it_fin.
endloop.

          loop at it_fin.

            it_data_1-vbeln = it_fin-belnr.
            it_data_1-matnr = it_fin-matnr.
            it_data_1-prctr = it_fin-prctr.

*read table it_sales transporting no fields with key vbeln = it_data_1-vbeln matnr = it_Data_1-matnr prctr = it_data_1-prctr.

*if sy-subrc = 0.
*tabix = sy-tabix.
*FOR SALES INVOICE
read table it_sales with key vbeln = it_data_1-vbeln matnr = it_Data_1-matnr prctr = it_data_1-prctr.

            it_data_1-aubel = it_sales-aubel.
            it_data_1-vstel = it_sales-vstel.
            it_data_1-ktgrm = it_sales-ktgrm.
            it_data_1-matkl = it_sales-matkl.
            it_data_1-fkart = it_sales-fkart.
            it_data_1-vkorg = it_sales-vkorg.
            it_data_1-vtweg = it_sales-vtweg.
            it_data_1-fkdat = it_sales-fkdat.
            it_data_1-spart = it_sales-spart.

            if it_data_1-fkart = 'F2' or  it_data_1-fkart = 'IV' or  it_data_1-fkart = 'ZF2' or it_data_1-fkart = 'ZMIS' or  it_data_1-fkart = 'ZSF2' or it_data_1-fkart = 'ZMF2'.

              it_data_1-fkimg = it_sales-fkimg.


              it_data_1-shkzg = it_fin-shkzg.

              if it_data_1-shkzg = 'H'.

              it_data_1-dmbtr_1 = it_data_1-dmbtr_1 + it_fin-dmbtr.

              endif.

              if it_data_1-shkzg = 'S'.

              it_data_1-dmbtr_2 = it_data_1-dmbtr_2 + it_fin-dmbtr.

              endif.

              endif.


* H - Credit
* S - Debit
            if it_data_1-fkart = 'G2' or  it_data_1-fkart = 'IG' or  it_data_1-fkart = 'RE' or  it_data_1-fkart = 'ZCRE'.

              if it_data_1-fkart = 'G2'.
                it_data_1-cr_qty = '0'.
              else.
                it_data_1-cr_qty = it_sales-fkimg.
              endif.

              it_data_1-shkzg = it_fin-shkzg.

              if it_data_1-shkzg = 'H'.

              it_data_1-dmbtr_cr_1 = it_data_1-dmbtr_cr_1 + it_fin-dmbtr.

              endif.

              if it_data_1-shkzg = 'S'.

              it_data_1-dmbtr_cr_2 = it_data_1-dmbtr_cr_2 + it_fin-dmbtr.

              endif.

              endif.


            if it_data_1-fkart = 'L2'.

              it_data_1-dr_qty = 0.

              it_data_1-shkzg = it_fin-shkzg.

              if it_data_1-shkzg = 'H'.

              it_data_1-dmbtr_dr_1 = it_data_1-dmbtr_dr_1 + it_fin-dmbtr.
              endif.

              if it_data_1-shkzg = 'S'.

              it_data_1-dmbtr_dr_2 = it_data_1-dmbtr_dr_2 + it_fin-dmbtr .

              endif.

            endif.

            select single vtext into it_data_1-vtext from tvkmt where spras = 'EN' and ktgrm = it_data_1-ktgrm.

            select single txt20 into it_data_1-gltxt from skat where spras = 'EN' and saknr = it_data_1-hkont and ktopl = '1000'.

            select single maktg into it_data_1-maktg from makt where matnr = it_data_1-matnr.

            select single vtext into it_data_1-division from tspat where spart = it_data_1-spart and spras = 'EN'.

            append it_data_1.

            clear it_data_1.
            clear it_sales.

          endloop.

          sort it_data_1 by matnr vbeln.

if p_check ne 'X'.

          loop at it_data_1.

            concatenate it_data_1-matnr ' ' it_data_1-matkl ' ' it_data_1-ktgrm into it_final_1-count.

            move: it_data_1-matnr to it_final_1-matnr,
                  it_data_1-matkl to it_final_1-matkl,
                  it_data_1-ktgrm to it_final_1-ktgrm,
                  it_data_1-vtext to it_final_1-vtext,
                  it_data_1-sakn1 to it_final_1-sakn1,
                  it_data_1-spart to it_final_1-spart,
                  it_data_1-gltxt to it_final_1-gltxt,
                  it_data_1-division to it_final_1-division,
                  it_data_1-prctr to it_final_1-prctr,
                  it_data_1-maktg to it_final_1-maktg,
                  it_data_1-fkimg to it_final_1-fkimg,
*                  it_data_1-dmbtr to it_final_1-dmbtr,
*                  it_data_1-dmbtr_cr to it_final_1-dmbtr_cr,
*                  it_data_1-dmbtr_dr to it_final_1-dmbtr_dr,
                  it_data_1-dr_qty to it_final_1-dr_qty,
                  it_data_1-cr_qty to it_final_1-cr_qty,
                  it_data_1-dmbtr_1 TO it_final_1-dmbtr_1,
                  it_data_1-dmbtr_2 TO it_final_1-dmbtr_2,
                  it_data_1-dmbtr_dr_1 TO it_final_1-dmbtr_dr_1,
                  it_data_1-dmbtr_dr_2 TO it_final_1-dmbtr_dr_2,
                  it_data_1-dmbtr_cr_1 TO it_final_1-dmbtr_cr_1,
                  it_data_1-dmbtr_cr_2 TO it_final_1-dmbtr_cr_2.

            append it_final_1.
            clear it_data_1.

          endloop.

          data: wa_matnr_1 like mara-matnr,
                wa_matkl_1 like vbrp-matkl,
                wa_ktgrm_1 like vbrp-ktgrm,
                wa_hkont like bsis-hkont,
                wa_gltxt like skat-txt20,
                wa_hkont_dr like bsis-hkont,
                wa_hkont_cr like bsis-hkont,
                wa_maktg_1 like makt-maktg,
                wa_vtext_1 like tvkmt-vtext,
                wa_vtext_2 like tspat-vtext,
                wa_spart like vbrk-spart,
                wa_prctr like vbrp-prctr.

          sort it_final_1 by matnr matkl ktgrm division prctr hkont.

          loop at it_final_1.

            wa_matnr_1 = it_final_1-matnr.
            wa_matkl_1 = it_final_1-matkl.
            wa_ktgrm_1 = it_final_1-ktgrm.
            wa_vtext_1 = it_final_1-vtext.
            wa_spart = it_final_1-spart.
            wa_hkont = it_final_1-sakn1.
            wa_gltxt = it_final_1-gltxt.
            wa_vtext_2 = it_final_1-division.
            wa_prctr = it_final_1-prctr.
            wa_maktg_1 = it_final_1-maktg.
*        wa_hkont_cr = it_final_1-hkont_cr.


            at end of count.

              sum.
*          it_gl-hkont_dr = it_final_1-hkont_dr.
*          it_gl-hkont_cr = it_final_1-hkont_cr.
              it_gl-fkimg = it_final_1-fkimg.
              it_gl-dr_qty = it_final_1-dr_qty.
              it_gl-cr_qty = it_final_1-cr_qty.

              it_gl-dmbtr_1 = it_final_1-dmbtr_1.
              it_gl-dmbtr_2 = it_final_1-dmbtr_2.
              it_gl-dmbtr = it_final_1-dmbtr_1 - it_final_1-dmbtr_2.
*it_gl-dmbtr = it_final_1-dmbtr.
*
              it_gl-dmbtr_dr_1 = it_final_1-dmbtr_dr_1.
              it_gl-dmbtr_dr_2 = it_final_1-dmbtr_dr_2.
              it_gl-dmbtr_dr = it_final_1-dmbtr_dr_1 - it_final_1-dmbtr_dr_2.
*it_gl-dmbtr_dr = it_final_1-dmbtr_dr.

              it_gl-dmbtr_cr_1 = it_final_1-dmbtr_cr_1.
              it_gl-dmbtr_cr_2 = it_final_1-dmbtr_cr_2.
              it_gl-dmbtr_cr = it_final_1-dmbtr_cr_1 - it_final_1-dmbtr_cr_2.
*it_gl-dmbtr_cr = it_final_1-dmbtr_cr.

              it_gl-gltxt = wa_gltxt.
              it_gl-matnr = wa_matnr_1.
              it_gl-matkl = wa_matkl_1.
              it_gl-vtext = wa_vtext_1.
              it_gl-spart = wa_spart.
              it_gl-division = wa_vtext_2.
              it_gl-prctr = wa_prctr.
              it_gl-hkont = wa_hkont.
              it_gl-maktg = wa_maktg_1.
              it_gl-netqty = ( it_gl-fkimg + it_gl-dr_qty ) - ( it_gl-cr_qty ).
              it_gl-netval = ( it_gl-dmbtr + it_gl-dmbtr_dr ) - ( it_gl-dmbtr_cr ).


              append it_gl.

              clear wa_matnr_1.
              clear wa_vtext_1.
              clear wa_matkl_1.
              clear wa_ktgrm_1.
              clear wa_hkont.
              clear wa_hkont_dr.
              clear wa_hkont_cr.

            endat.

            clear it_final_1.
            clear it_gl.

          endloop.

        endif.
      ENDIF.
  endif.

Do provide your valuable suggestions

Regards,

Jitesh

Use meaningful subject for your Future questions

Edited by: Vijay Babu Dudla on Mar 23, 2009 6:20 AM

[Former Member]

Please follow the following thing when you fetch th data from database.

all the fields which need to fectch should be in same order in select query as they are database table. this helps to improve youe performence.

Select sum statement takes a long time compare to the other processing option such as at end at first etc. so it will always be help ful if you want sum of a particular field then do it via processing of interl tables.

Hope this will help you.

[Former Member]

the forum is not intended for code review you must do the preparation and ask specific

questions.

For your code:

For all reads or loop in loops, use sorted tables or use binary search !!!

For all database statements, more preparation is necessary, run SQL Trace

SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Have a look at summary by SQL statements, check which statement is slow.

Ask a question for that statement.

Siegfried

[Former Member]

Hi,

BSEG is a cluster table use secondary index tables bsak.

Remove move corresponding.

Thanks,

Krishna...

[Former Member]

Assuming you are using standard tables instead of sorted or hashed, your problem is likely here:

loop at it_fin.
  read table it_sales with 
    key vbeln = it_data_1-vbeln 
    matnr = it_Data_1-matnr 
    prctr = it_data_1-prctr.

  ...

endloop.

The read without the binary search option is in effect a nested loop. so have a look at:

[Performance of Nested Loops|/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops]

Rob