10-15-2008 10:21 AM
Hi Experts,
the following bit of code takes long time to execute(2hrs).
select * from dfttop into table t_dfttop
where vtref1 like u2018YMC%u2019 and
((augbb = u201800000000u2019 and xrag = u2018Xu2019)
or
(augbb between w_cltfr and w_cltto)) and
zwage_type in s_wtype and
aunnd ne u201803u2019.
Can anyone give me some suggestion. Basically data in zwage_type is very huge. Can some one help me to stream line this and improve the performance.
Edited by: Arun Kumar Sekar on Oct 15, 2008 11:21 AM
10-15-2008 12:10 PM
2hrs is a long time, better split you statement in 2:
select *
from dfttop
into table t_dfttop
where vtref1 like u2018YMC%u2019
and augbb = u201800000000u2019
and xrag = u2018Xu2019
and zwage_type in s_wtype
and aunnd ne u201803u2019.
select *
from dfttop
appending table t_dfttop
where vtref1 like u2018YMC%u2019
and augbb between w_cltfr and w_cltto
and zwage_type in s_wtype
and aunnd ne u201803u2019.
If you are lucky then both are now supprted by different indexes and are faster.
I did not check the indexes, maybe somebody else does.
Siegfried
10-15-2008 12:38 PM
Hi,
The problem in the select query which you have given is :
(a) Use the field names which are required.
(b) Maximum time will be taken by "like 'YMC%' " and this is where the complexity.
(c) Another complexity is the OR as it will increase the overhead and cost of fetch from the table dfttop.
if you rectify the logic accordingly and thing this can help u a lot.
Thanks and Regards,
Harsh
10-16-2008 10:45 AM
>
> Hi,
>
>
> The problem in the select query which you have given is :
> (a) Use the field names which are required.
> (b) Maximum time will be taken by "like 'YMC%' " and this is where the complexity.
> (c) Another complexity is the OR as it will increase the overhead and cost of fetch from the table dfttop.
>
> if you rectify the logic accordingly and thing this can help u a lot.
> Thanks and Regards,
> Harsh
Hi Harsh,
disagree:
(a)
Though keeping the selected records small is general a good idea in terms of available resources it will contribute only marginal to the problem here (wich is a low selectivity on indexed columns that
could lead to a full table scan instead of an index access).
(b)
Well, it depends. An index can be used because the wild card is at the trailing end. AND if the given
value is very selective it can support an index with fast access.
(c)
I agree. Formulating the statements with AND on indexed columns giving a high selectivity
(btw: the IN is also expanded into OR's and limits possible fast execution plans)
In general I would use ST05 to check the execution plans for varying statements.
It may that Siegfrieds solution could be more efficient - allthough it has the drawback that the table is scanned twice (if no indexes are supported).
Always try do formulate the problem in one statement. Given good statistics (consider histogram statistics additonally to the default statistics) the optimizer can come to a good plan. But if you select
10- 20% of the tables rows it may be cheaper to scan the table instead of doing a lot of lockups into indexes
and back to the table's data.
Bye
yk
10-16-2008 11:07 AM
>
> Hi Experts,
>
> the following bit of code takes long time to execute(2hrs).
> select * from dfttop into table t_dfttop
> where vtref1 like u2018YMC%u2019 and
> ((augbb = u201800000000u2019 and xrag = u2018Xu2019)
> or
> (augbb between w_cltfr and w_cltto)) and
> zwage_type in s_wtype and
> aunnd ne u201803u2019.
>
> Can anyone give me some suggestion. Basically data in zwage_type is very huge. Can some one help me to stream line this and improve the performance.
>
> Edited by: Arun Kumar Sekar on Oct 15, 2008 11:21 AM
Hi Arun,
I will add to the good comments that you may look on zwage_type: What do you mean with huge?
Let's say 20- 50 items will be ok. But if you talking about hundreds (thousands) it maybe worth to check
where you get the values from.
If it's a table and you can join to it using the INDEXED keys go for subquery on that table. Using EXISTS you can formulate it with an AND (instead of using IN wich will expand to OR's in the database and inturn makes it more difficult to get an efficient execution plan).
EXISTS supports the index of the lookup table (i.e. the primary key that you can join back to your
table). If the scenario can be expressed that way try to check the subqueries approach.
select * from dfttop into table t_dfttop
where vtref1 like u2018YMC%u2019 and
((augbb = u201800000000u2019 and xrag = u2018Xu2019) " really selective?
or
(augbb between w_cltfr and w_cltto))
and
EXISTS ( Select * from tab_s_wtype where
dfttop.col1 = tab_s_wtype .col1
...) " here you join with the index column(s) to
" lookup from dfttop to tab_s_wtype
and aunnd ne u201803u2019. " here you can express this with EQ instead of NE ?
Check this for the execution plan in ST05.
bye
yk
10-21-2008 11:17 AM
10-21-2008 8:13 PM
Hi,
One Suggestion from my side:
Try to remove the ne operator in the very last ............
10-22-2008 6:37 AM
Hi,
According to me maximum time is going on due to like operator because with like operator it does sequential read which takes time
so firstly select all the enteries by using query as
select * from dfttop into table t_dfttop
where
((augbb = u201800000000u2019 and xrag = u2018Xu2019)
or
(augbb between w_cltfr and w_cltto)) and
zwage_type in s_wtype and
aunnd ne u201803u2019.
then delete all the enteries from internal table t_dfttop
which are not starting with 'YMC'.
thanks,
Puneet