Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query taking lot of time

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Sat Dec 08, 2007 10:21 am    Post subject: Query taking lot of time
Reply with quote

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
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Sat Dec 08, 2007 2:11 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Dec 08, 2007 6:26 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Dec 09, 2007 2:44 am    Post subject:
Reply with quote

Hello,

Just curious - How many rows are in T1?
Back to top
View user's profile Send private message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Sun Dec 09, 2007 11:56 am    Post subject: Reply to: Query taking lot of time
Reply with quote

Hi all,

It has just 28,400 records

What to do guys??

Thanks
Back to top
View user's profile Send private message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Sun Dec 09, 2007 9:49 pm    Post subject: Reply to: Query taking lot of time
Reply with quote

Thankyou all for replying icon_surprised.gif

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Dec 10, 2007 12:16 am    Post subject:
Reply with quote

Hello,

How much time is "so much time"?

As an experiment, you might specify ORDER BY.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Dec 10, 2007 2:18 am    Post subject:
Reply with quote

Are the DB2 Statistics on the table up todate, has the table ever been reorg'ed, is there a cluster index on the table?
Back to top
View user's profile Send private message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Mon Dec 10, 2007 9:41 am    Post subject: Reply to: Query taking lot of time
Reply with quote

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
Back to top
View user's profile Send private message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Tue Dec 11, 2007 2:51 pm    Post subject: Reply to: Query taking lot of time
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Dec 11, 2007 5:09 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Tue Dec 11, 2007 6:54 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Dec 11, 2007 7:56 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Wed Dec 12, 2007 10:35 am    Post subject: Reply to: Query taking lot of time
Reply with quote

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
Back to top
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Wed Dec 12, 2007 4:13 pm    Post subject: Reply to: Query taking lot of time
Reply with quote

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
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Wed Dec 12, 2007 9:50 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 12, 2007 10:28 pm    Post subject:
Reply with quote

Hello,

Quote:
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.


Quote:
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)


It appears that the query does not run poorly all of the time. Determining why one environment (spufi) runs well and the other (cobol) does not would be valuable.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us