# 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

##### Siegfried Boes 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