Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Query takes 2 hrs to execute.

Former Member
0 Kudos

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

7 REPLIES 7

former_member194613
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

>

> 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

Former Member
0 Kudos

>

> 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

Former Member
0 Kudos

Make secondary index ..

Former Member
0 Kudos

Hi,

One Suggestion from my side:

Try to remove the ne operator in the very last ............

Former Member
0 Kudos

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