topic Re: Query Optimization - Index possible?? in Application Development and Automation Discussions

Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 06:41:52 GMT

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

Re: Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 07:02:07 GMT

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

Re: Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 07:09:01 GMT

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

Re: Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 07:53:44 GMT

> 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

Re: Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 09:15:39 GMT

use field group with query..

it surly help

Re: Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 09:42:28 GMT

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.

Re: Query Optimization - Index possible??

Former Member — Mon, 26 May 2008 21:59:45 GMT

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