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
 

 

Performance difference between two queries

 
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: Wed Nov 05, 2008 2:33 am    Post subject: Performance difference between two queries
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

Site Director


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

PostPosted: Wed Nov 05, 2008 2:53 am    Post subject:
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    Post subject:
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    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 Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm


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