topic Re: SELECT Query Optimization in Application Development and Automation Discussions

SELECT Query Optimization

Former Member — Thu, 24 Apr 2008 07:22:40 GMT

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

Re: SELECT Query Optimization

Former Member — Thu, 24 Apr 2008 08:16:12 GMT

1) In the first select,

avoid using OR condition. this will take more time.

avoid NE condition. after populating the data delete the unwanted records.

make sure the select-options is not initial.

2) before using FOR ALL ENTRIES,

check if t_vbak[] is not initial

delete the duplicate entries

3) avoid using <>, > condition especially for constants like w_{kwmeng > 0. see how many records it will fetch with w}kwmeng > 0 and without it and see if it makes significant difference.

Regards,

Madhu

Re: SELECT Query Optimization

Former Member — Thu, 24 Apr 2008 10:25:40 GMT

Hi Chandravadan,

using NE like you do in

b~abstk ne 'C'

usually result in loss of performance. It is better to use a selection-option with the values you would like to include.

The next thing I would check, if the select statement is able to use the indexes. You can check this in the SQL-Trace (TA ST05). If not you need to rearrange your fields so that an index can be used.

Third: You do not check if the table t_vbak is empty after the first select statement. If he is empty all data in the second select-statement is read.

Regards Matthias Nutt

SAP Consulting Switzerland

Re: SELECT Query Optimization

Former Member — Thu, 24 Apr 2008 12:09:29 GMT

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

Re: SELECT Query Optimization

Former Member — Thu, 24 Apr 2008 21:30:55 GMT

Hi ,

Try to avoid joins instead, use for all entries.

And using loop update data.

Thanks.

Re: SELECT Query Optimization

Former Member — Fri, 25 Apr 2008 07:27:28 GMT

you did not get the point!