Skip to Content
avatar image
Former Member

Query takes 2 hrs to execute.

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Oct 15, 2008 at 11:10 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 15, 2008 at 11:38 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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      >

      > 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

  • avatar image
    Former Member
    Oct 16, 2008 at 10: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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 21, 2008 at 10:17 AM

    Make secondary index ..

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 21, 2008 at 07:13 PM

    Hi,

    One Suggestion from my side:

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 22, 2008 at 05: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

    Add comment
    10|10000 characters needed characters exceeded