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
 
Which query has better performance?

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

New User


Joined: 26 Mar 2008
Posts: 44
Location: China

PostPosted: Wed Jun 17, 2009 12:13 pm    Post subject: Which query has better performance?
Reply with quote

There are two tables which have same structure, TEMPTBL and TEMPTBL2.
There 's new data in TEMPTBL while old data in TEMPTBL2.
The relationship is '1:N'. ID is the key for matching.

--------------------------------
TEMPTBL2

Code:
ID           QUANTITY  NAME   
+---------+---------+---------
 1               2.00  MERLE11
 1               3.00  MERLE12
 2               3.00  MERLE21
 2               4.00  MERLE22
 3               1.00  MERLE31


--------------------------------
TEMPTBL

Code:
 ID           QUANTITY  NAME   
-+---------+---------+---------
  1               1.00  MERLE   
  2               2.00  MERLE2 
  4               1.00  MERLE41


--------------------------------

I wanna get data from TEMPTBL2 when there is a match between TEMPTBL and TEMPTBL2 by ID. And the data format is 'ID, max(QUANTITY), NAME'.

Expected result:

Code:
 
ID           QUANTITY  NAME     
+---------+---------+---------+
 1               3.00  MERLE12 
 2               4.00  MERLE22


I wrote two queries which will result the same result as I want. However, I am unable to determine which one has better performance.

They are as below:

Code:
SELECT T2.ID, T2.QUANTITY, T2.NAME               
FROM IG80U1.TEMPTBL2 T2,                       
     (SELECT P1.ID, P2.QUANTITY                 
      FROM IG80U1.TEMPTBL  P1,                 
           (SELECT ID, MAX(QUANTITY) AS QUANTITY
           FROM IG80U1.TEMPTBL2                 
           GROUP BY ID) AS P2                   
      WHERE P1.ID = P2.ID) AS T1                 
WHERE T2.ID=T1.ID AND T2.QUANTITY=T1.QUANTITY;



Code:
SELECT T2.ID, T2.QUANTITY,T2.NAME       
FROM IG80U1.TEMPTBL2 T2,               
     IG80U1.TEMPTBL  T1               
WHERE T2.ID=T1.ID                     
  AND T2.QUANTITY=                     
           (SELECT MAX(QUANTITY) AS QUANTITY
            FROM IG80U1.TEMPTBL2 P1         
            WHERE P1.ID=T1.ID);


I will be very appreciated if anyone could give some advice about it to me.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jun 17, 2009 2:39 pm    Post subject:
Reply with quote

Your sesond query seems to be better ...
Back to top
View user's profile Send private message
feng hao

New User


Joined: 26 Mar 2008
Posts: 44
Location: China

PostPosted: Wed Jun 17, 2009 3:02 pm    Post subject:
Reply with quote

ashimer wrote:
Your sesond query seems to be better ...


Thank you very much, ashimer!

But what is the standard by which we use to determine whether a query is better than another?

I would like the DB2 query optimizer only goes through the whole TEMPTBL rather than the whole TEMPTBL2, if so, I prefer the second one as you too. However, I am not sure of it at all now.

Up to now, I have doubts in both the two's performance, and the second one just looks shorter and more clear in logic.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jun 17, 2009 3:13 pm    Post subject:
Reply with quote

Whenever in doubt do an EXPLAIN ... it clearly shows the in and out of how db2 is going to take on the query ...
Back to top
View user's profile Send private message
feng hao

New User


Joined: 26 Mar 2008
Posts: 44
Location: China

PostPosted: Thu Jun 18, 2009 8:19 am    Post subject:
Reply with quote

All right, ashimer, I will try Explain it later
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts CONTIG for performance? JPVRoff JCL & VSAM 8 Fri Jun 09, 2017 8:39 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

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