IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Performance difference between two queries


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Wed Nov 05, 2008 2:33 am
Reply with quote

Query1 and Query2 which one is efficient.

( Note: LAT_TP is present in Index )


Query1:

Code:
SELECT  A.ROLLNO,COUNT(*),     
SUM(A.MARK)                               
FROM GKK.MARK_DTL A                           
WHERE LAT_TP  <= ( SELECT MAX(LAT_ENT_TP)   
                  FROM GKK.LOAD_DTL  )     
AND   LAT_TP >=  ( SELECT MAX(LAT_BEG_TP)   
                  FROM GKK.LOAD_DTL  )     
GROUP BY A.ROLLNO;     


Query2:

Code:
SELECT   A.ROLLNO,COUNT(*),     
SUM(A.MARK)                               
FROM GKK.MARK_DTL A,
( SELECT MAX(LAT_ENT_TP)  ENDTP
                  FROM GKK.LOAD_DTL  )      AS B,
( SELECT MAX(LAT_BEG_TP)  BEGTP
                  FROM GKK.LOAD_DTL  )      AS C
WHERE LAT_TP <= B.ENDTP  AND LAT_TP >= C.BEGTP
GROUP BY A.ROLLNO;


Edited - poster needs to learn to use the "Code" tag
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Nov 05, 2008 2:53 am
Reply with quote

Hello,

Quote:
LAT_TP is present in Index
Being in an index is far less important than being the high-order part of an index. . .

What happens when you execute these queries?
Back to top
View user's profile Send private message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Fri Nov 07, 2008 12:40 am
Reply with quote

Query 2 execute faster than query 1
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts Timestamp difference and its average ... DB2 11
No new posts Difference when accessing dataset in ... JCL & VSAM 7
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts What is the difference between Taskty... Compuware & Other Tools 2
Search our Forums:

Back to Top