Query taking lot of time

Varun Singh · New User Joined: 01 Aug 2007 Posts: 25 Location: Delhi

Hello all,

I have a query
SELECT ID FROM T1
WHERE XLKEY LIKE %SEESEE%

Can anybody suggest me why the above mentioned query is
taking so much time

I have 2 indexes defined on the same column XLKEY

First index is ascending in order
while second index is descending in order

Thanks in advance

Gnanas N · Posted: Sat Dec 08, 2007 2:11 pm

Table may contain huge amount of records.

I hope your WHERE condition will take time since it has to be searched in such way that middle portion of XLKEY column value.

Correct me if I am wrong.

stodolas · Posted: Sat Dec 08, 2007 6:26 pm

Why the 2 indexes? It seems kind of redundant to have them just going in opposite directions and more overhead on the DB...

But gnasas is right if the the table is big that is a horrible where query. It requires a full table scan even with an index because it needs to look across the whole value of the column in each row.

dick scherrer · Posted: Sun Dec 09, 2007 2:44 am

Hello,

Just curious - How many rows are in T1?

Varun Singh · New User Joined: 01 Aug 2007 Posts: 25 Location: Delhi

Hi all,

It has just 28,400 records

What to do guys??

Thanks

Varun Singh · New User Joined: 01 Aug 2007 Posts: 25 Location: Delhi

Thankyou all for replying

Stodolas, I am also little bit stirred with the index definition on the table
I don't know why they have done like this( 2 indexes that too on a single column)

Any idea on how to refine this query or with any other method using
COBOL and DB2

dick scherrer · Posted: Mon Dec 10, 2007 12:16 am

Hello,

How much time is "so much time"?

As an experiment, you might specify ORDER BY.

Craq Giegerich · Posted: Mon Dec 10, 2007 2:18 am

Are the DB2 Statistics on the table up todate, has the table ever been reorg'ed, is there a cluster index on the table?

Varun Singh · New User Joined: 01 Aug 2007 Posts: 25 Location: Delhi

Hi all,

I had ran runstats still the same problem..
The table has not been reorganised.

Yes the key with order as ascending is having clustering option as 'Y'
whereas the key with descending is having the custering option as 'N'

any comments..
Dick I will try this experiment..and let all know

Thankyou

Varun Singh · New User Joined: 01 Aug 2007 Posts: 25 Location: Delhi

Hi dick I ran the query it gave me an error stating
order by cannot be used as the select into query
will not result more then one row.

I believe this is ok with normal DB2 query
but if we will use host variable ....it's not ok.

Thanks for replying

ksk · Posted: Tue Dec 11, 2007 5:09 pm

Hi,

When I was working on performance issue of a program in our project, I pointed out a query which was using LIKE and suggested the same to customer. As per my knowledge, LIKE takes lot of time to run a query. may be u can use something else inplace of LIKE.

KSK

Phrzby Phil · Posted: Tue Dec 11, 2007 6:54 pm

If you do this repeatedly with the same value, you might want to add a new column to flag these rows and update that column when you INSERT new rows.

dick scherrer · Posted: Tue Dec 11, 2007 7:56 pm

Hello,

You might try the query "both ways" in spufi (with and without the order by).

How does the original query run in spufi compared to within the program?

Varun Singh · New User Joined: 01 Aug 2007 Posts: 25 Location: Delhi

Hello everybody,

Ya dick, you are correct in SPUFI no issues with ORDER BY or without ORDER BY

The original query without order by is running smoothly in SPUFI
no issues.

Timestamp says
2007-12-12-04.58.51.885897(Before execution)
fetching one record
2007-12-12-04.58.51.903085(After execution)

Phil, didn't got what you want to say. Can you please give some examples

ragshere · New User Joined: 20 Dec 2004 Posts: 70

Varun,
If you are using a predicate in the form "COL1 LIKE '%var' " then this will not be indexable. That means DB2 will not use any indexes while executing the query. so even if you have more than 1 index on the same column it will not be used.

If your predicate is in the form COL1 LIKE 'var%' then DB2 will use the indexes.

Regards,
Rags

Phrzby Phil · Posted: Wed Dec 12, 2007 9:50 pm

Varun -

My suggestion is for special oddball situations where an expensive and very frequent query can best be handled by a system design upgrade to flag such rows specially at INSERT time.

dick scherrer · Posted: Wed Dec 12, 2007 10:28 pm

Hello,