Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SELECT Query Optimization

Hi All,

I have requirement to optimize the following query. The requirement is: either a new index need to be created or the SELECT statement need to be changed in order to improve the performance of the SELECT query.

 
select a~vbeln a~auart a~vkorg a~vtweg a~spart
             a~angdt a~bnddt a~guebg a~gueen a~vkgrp
             a~vkbur a~gsber a~kunnr a~erdat a~erzet
             a~waerk a~vbtyp a~autlf a~vsbed a~kvgr1
             a~kvgr2 a~kvgr3 a~kvgr4 a~kvgr5 a~abrvw
             a~abdis
        into table t_vbak
        from vbak as a inner join vbuk as b on b~vbeln = a~vbeln
        where
              ( ( a~erdat > pre_dat ) and
              ( a~erdat <= w_date  ) ) and
              a~vbtyp in s_doccat and
              a~vbeln in s_ordno and
              a~vkorg in s_vkorg and
              a~vtweg in s_vtweg and
              a~spart in s_spart and
              ( ( a~lifsk in s_lifsk ) or
              ( a~lifsk = '  ' ) ) and
              b~abstk ne 'C'.

      
	select  w~mandt
                w~vbeln  w~posnr  w~meins w~matnr w~werks  w~netwr
                w~kwmeng w~vrkme  w~matwa  w~charg w~pstyv
                w~posar  w~prodh  w~grkor  w~antlf  w~kztlf w~lprio
                w~vstel  w~route  w~umvkz  w~umvkn  w~abgru w~untto
                w~awahr  w~erdat  w~erzet  w~fixmg  w~prctr  w~vpmat
                w~vpwrk  w~mvgr1  w~mvgr2  w~mvgr3  w~mvgr4  w~mvgr5
                w~bedae  w~cuobj  w~mtvfp
                x~etenr x~wmeng x~bmeng   x~ettyp  x~wepos  x~abart
                x~edatu x~tddat x~mbdat   x~lddat  x~wadat  x~abruf 
                x~etart x~ezeit
                into table t_vbap
               from  vbap as w inner join vbep as x 
                          on x~vbeln = w~vbeln and
                             x~posnr = w~posnr and
                             x~mandt = w~mandt
         for all entries in t_vbak
         where
                       w~vbeln in s_ordno and
                       w~vbeln = t_vbak-vbeln and
            ( ( ( erdat > pre_dat  and  erdat < p_syndt ) or
            ( erdat = p_syndt and erzet <= p_syntm ) ) ) and
                       w~matnr in s_matnr and
                       w~pstyv in s_itmcat and
                       w~lfrel in s_lfrel and
                       w~abgru = '  ' and
                       w~kwmeng > 0   and
                       w~mtvfp in w_mtvfp  and
                       x~ettyp in w_ettyp  and
                       x~bdart in s_req_tp and
                       x~plart in s_pln_tp and
                       x~etart in s_etart and
                       x~abart in s_abart and
                       ( ( x~lifsp in s_lifsp ) or ( x~lifsp = ' ' ) ).

It would be great if you can suggest some change in the above code.

Appreciate your valuable inputs.

Thanks,

Chandravadan

Former Member
Former Member replied

weird !!!! ???? !!!

where   w~vbeln in s_ordno and
            w~vbeln = t_vbak-vbeln and

I don't know what you expect from such a condition!

It is a bug, the first line must be delete, it was used in the first select, so the vbeln are in the table t_vbak.

The unique key of VBAP is mandt, vbeln and posnr, will the following conditions really reduce the result set?

Especcially the erdat conditions are so confusing, that you better use it inside the select.

( ( ( erdat > pre_dat and erdat < p_syndt ) or

( erdat = p_syndt and erzet <= p_syntm ) ) ) and

w~matnr in s_matnr and

w~pstyv in s_itmcat and

w~lfrel in s_lfrel and

w~abgru = ' ' and

w~kwmeng > 0 and

Siegfried

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question