|
View previous topic :: :: View next topic
|
| Author |
Message |
feng hao
New User
Joined: 26 Mar 2008
Posts: 44
Location: China
|
|
|
|
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 |
|
 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Your sesond query seems to be better ... |
|
| Back to top |
|
|
feng hao
New User
Joined: 26 Mar 2008
Posts: 44
Location: China
|
|
|
|
| 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| 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 |
|
|
feng hao
New User
Joined: 26 Mar 2008
Posts: 44
Location: China
|
|
|
|
| All right, ashimer, I will try Explain it later |
|
| Back to top |
|
|
|
