04-24-2008 8:22 AM
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
04-24-2008 1:09 PM
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
04-24-2008 9:16 AM
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 wkwmeng > 0. see how many records it will fetch with wkwmeng > 0 and without it and see if it makes significant difference.
Regards,
Madhu
04-24-2008 11:25 AM
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
04-24-2008 1:09 PM
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
04-24-2008 10:30 PM
Hi ,
Try to avoid joins instead, use for all entries.
And using loop update data.
Thanks.
04-25-2008 8:27 AM