Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query execution time variation.

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

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Mon Jan 05, 2009 11:55 pm    Post subject: Query execution time variation.
Reply with quote

Code:
SELECT  A.CLM_CD, A.CLM_RANGE,COUNT(*),   
SUM(A.AMT_VAL)                             
FROM KGH.CLM_DTL_TAB A,                         
WHERE DM_PART_NUM  < 40                         
AND   CLM_ID_VAL  > 0                             
AND  TIMESTMP_VAL  >= '2008-11-31-01.44.09.000008'
GROUP BY  A.CLM_CD, A.CLM_RANGE;


The fields DM_PART_NUM, CLM_ID_VAL, TIMESTMP_VAL present in the Index field.
All the Remining fields not Present in the index

The tablespace REORGed and also INDEX also reorged on December 1st week.

After REORGS, i ran the above query. It ran with in 35 Mins.
And also EXPLAIN shows the above query using INDEX.


Note: This table contain more than Billion records.

In 30 Dec 2008, again the this table loaded with below LOAD CARD option.

Code:
LOAD DATA RESUME YES LOG NO       
     INDDN SYSREC                 
     COPY NO COPYPEND NO         
     INTO TABLE KGH.CLM_DTL_TAB


5 Million records loaded on 30 Dec 2008.After that i ran the above query.
The query took more than 3 hours for completion. But this time also EXPLAIN Shows its using the index. But it took more time for completion.

Please let me know the reason for this.


Edited: Please use BBcode when You post some code, that's rather readable, Thanks...Anuj
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 Jan 06, 2009 12:20 am    Post subject:
Reply with quote

Hello,

Measuring run-time is usually futile - it may be the most visable, but is not directly tunable. How long a process runs depends on many factors such as how much other work is going on in the system in general and how many other tasks are using the same table.

If is very common for run-time to vary greatly between runs.

You might talk with your dba to set up something to measure what db2 resources are used by this query and then compare usage for multiple executions.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jan 06, 2009 3:52 pm    Post subject:
Reply with quote

The query will use index but the matchcols in your case would be 1, because of less equality clauses in your where clause .... 5 million is quite a huge number for aggregrate functions .... as dick suggested find out from your DBA ..
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jan 07, 2009 9:58 am    Post subject:
Reply with quote

CallKris,

I had a similar problem, but query wasn't accessing BILLION records. But, it goes through a lot of computations, it took lot of time. So we came out with MQT's, which saved lot of time. If your query is not gonna change, i can say you can go for MQT.

Waiting for EXPERTS ADVICE on this!
Sushanth Bobby
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jan 07, 2009 4:06 pm    Post subject:
Reply with quote

Incase the Query is run only once after every load MQT's can be used or else an overhead of frequent REFRESH will be there
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jan 07, 2009 4:59 pm    Post subject:
Reply with quote

Ashimer,

Quote:
Incase the Query is run only once after every load

In this case, MQT are of no-use. Query is enough.

Sushanth
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
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Comparision with current time arunsoods DFSORT/ICETOOL 5 Thu Nov 09, 2017 10:37 am
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us