Query Optimization - Index possible??

[Former Member]

Hi Experts

I have the following Query to be optimized. Now am checking out if creating an INDEX would improve the performance of this Query

Please suggest - if an index is possible on this query? If yes, upon what field I can create it?

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~vbtyp in s_doccat and          
a~auart in s_ordtyp 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~erdat >= pre_dat and                    
a~erdat < p_syndt ) or            
 ( a~erdat = p_syndt and                     
a~erzet <= p_syntm ) ) ) and              
( ( a~lifsk in s_lifsk ) or                     
( a~lifsk = '  ' ) )                          
and	b~abstk ne 'C'.                         

t_vbak1[] = t_vbak[].
sort t_vbak1 by vbeln.
if t_vbak1[] is not initial.
    delete adjacent duplicates from t_vbak1 comparing vbeln.
endif.

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_vbak1
where
w~vbeln = t_vbak1-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~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 = ' ' ) ).

refresh: t_vbak1[].
clear: t_vbak1[].
DELETE t_vbap where kwmeng LE 0.

Highly Rewardable

Santo

[Former Member]

For Optimization.....Plz avoid join.......

if possible use views...........

if possible change the logic................to increase performance

If it is Usefull Plz Reward

Regards

Anbu

Edited by: Anbu B on May 26, 2008 9:02 AM

[Former Member]

I would like to know about the possibility of INDEX on this query too....

[Former Member]

> avoid join ... use views ...

sorry, that is nonsense views are joins which are defined in the dictonary

select ...                               
into  table t_vbak                               
from vbak as a                                   
inner join vbuk as b 
on b~vbeln = a~vbeln        
where a~vbtyp in s_doccat and          
a~auart in s_ordtyp 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~erdat >= pre_dat and                    
a~erdat < p_syndt ) or            
 ( a~erdat = p_syndt and                     
a~erzet <= p_syntm ) ) ) and              
( ( a~lifsk in s_lifsk ) or                     
( a~lifsk = '  ' ) )                          
and	b~abstk ne 'C'.                         

There is a huge misunderstanding which apprea here in the fourm very often.

Not the join is your problem but the in-clauses! You have to know which in-clauses are actually filled. Usually

on some are filled. But there are a lot of different combinations possible.

Identify the ones which are really important, try to put them into an order.

Then you can check whether there is an index for each combination, or whether there is no index.

A new index for the vbak table (all where conditions are on table a = vbak) is not so easy to realize. It is large central table with lots of changes,

lots of standard coding works with that coding, I guess your coding will be of low importance.

Siegfried

[Former Member]

use field group with query..

it surly help

[Former Member]

hi santo,

you are retriving data from different tables. so here index can help you some what to improve performance.

if are using single table to show data than index is very help full.

if you are showing data with the help of two or more tables do not use joins.

by using these statement you can improve your performance..

here i have two internal table

IT1 & IT2 and IT_result.

use this query.

select aa ,bb ,cc from table1 into table IT1 where aa =1.

if IT1 is not initial.

select aa bb dd from table2 into table it2

for all entries in IT1

where it2-aa = it1-aa.

endif.

if IT2 is not initial.

select aa bb dd from table3 into table it_result

for all entries in IT2

where it_result-aa = it2-aa.

endif.

by this you can improve your perfomance.

Give Rewards if helpfull.

[Former Member]

You don't give your requirements, but I'm assuming that a material number would be more likely to be entered than an order number on the selection screen. If this is the case, you could try working in table VAPMA whose primary index is the material number.

Rob