performance problem in using vbak table

[former_member772816]

hi everybody,

How can I improve query performance?

data: begin of lt_itab1,
      vkorg   like vbak-vkorg,
      vtweg   like vbak-vtweg,
      vbeln   like vbak-vbeln,
      posnr   like vbap-posnr,
      matnr   like vbap-matnr,
      kunnr   like vbak-kunnr,
      name1   like kna1-name1,
      fatmust like vbpa-kunnr,
      name2   like kna1-name1,
      vgbel   like vbak-vgbel,
      edatu   like vbep-edatu,
      aufnr   like caufv-aufnr,
      fatno   like vbrp-vbeln,
      atwrt1  like cawn-atwrt,
      atwtb1  like cawnt-atwtb,
      atwrt2  like cawn-atwrt,
      atwtb2  like cawnt-atwtb,
      atwrt3  like cawn-atwrt,
      atwtb3  like cawnt-atwtb.
data: end of lt_itab1.

data: begin of itab1 occurs 0,
      chk   type c,
      cuobj   like vbap-cuobj,
      atbez1  like cabnt-atbez,
      atinn1  like cabn-atinn,
      atnam1  like cabn-atnam,
      atbez2  like cabnt-atbez,
      atinn2  like cabn-atinn,
      atnam2  like cabn-atnam,
      atwrt2a like cawn-atwrt,
      atbez3  like cabnt-atbez,
      atinn3  like cabn-atinn,
      atnam3  like cabn-atnam,
      atwrt4  like cawn-atwrt,
      atwtb4  like cawnt-atwtb.
        include structure lt_itab1.
data: end of itab1.


 select   vbak~vkorg      
          vbak~vtweg
         vbep~vbeln
         vbep~posnr
         vbak~kunnr
         kna1~name1
         vbpa~kunnr as fatmust
         vbak~vgbel
         vbap~matnr
         vbep~edatu
         vbap~cuobj
         vbrp~vbeln as fatno
    into corresponding fields of table itab1
    from vbak
    inner join vbap on vbap~vbeln = vbak~vbeln
    inner join vbep on vbep~vbeln = vbap~vbeln
                   and vbep~posnr = vbap~posnr
    inner join kna1 on kna1~kunnr = vbak~kunnr
    left outer join vbpa on vbak~vbeln = vbpa~vbeln
                        and vbpa~posnr = 0
                        and vbpa~parvw = 'RE'
    left outer join vbrp on vbap~vbeln = vbrp~aubel
                        and vbap~posnr = vbrp~aupos
where vbak~vbeln in p_vbeln
  and vbak~kunnr in p_kunnr
  and vbak~vbtyp = 'C'
  and vbap~posnr in p_posnr
  and vbap~abgru eq space
  and vbep~edatu in p_edatu.

[S_Sriram]

Hi Murat

Using the transaction code ST12 enable the trace and find the expense sql statements.

Regards

SS

[Sandra_Rossi]

Get the database Execution Plan of this statement, analyze it and improve your statement. If you can't, attach the Execution Plan to your question.

[RaymondGiuseppi]

Alas, SAP removed some years ago the good old Note 185530 - Performance: Customer developments in SD.(even if you should be able to find it in the web...)

So trace SQL and analyze Execution plan and look at available index for performance.

[FredericGirod]

adding check Index ADV tables: VAPMA VAKPA .... (there is a program to activate some of these index)

[RaymondGiuseppi]

To manage indexes suggested by frdric.girod, look at OSS note 103212 - Bad performance during SD document update (look for SM30 on TVIND and report RVV05IVB)

Important transaction codes
===========================

• SE30 - Runtime analysis

• SM50 - Work process overview

• STAD - Individual statistics -

• ST05 - SQL trace