| View previous topic :: View next topic |
| Author |
Message |
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
 Posted: Tue Apr 06, 2004 2:15 am Post subject: SQL "Exist " |
 |
|
Hi Friends,
I want the below section to be replaced with SQL "exist" for test, instead of select.
How do I do it. i.e., All I want is a True/False test against same table instead of scanning through the table and selecting a matching entry.
| Code: |
C2000-VALID-ACCOUNT-CHECK SECTION.
-------------------------------*
EXEC SQL
SELECT
SUPP_ID
INTO
:WS-SUPP-ID2
FROM
DTWACCOUNT
WHERE
SUPP_ID = :WS-SUPP-ID1 AND
ACCT_NBR = :WS-NMI-01-ACCT-NBR-RED
END-EXEC.
C2000-EXIT.
-------------*
EXIT. |
|
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
nagasadhu
Beginner
Joined: 08 Jul 2006
Posts: 17
Topics: 6
|
| Posted: Thu Dec 14, 2006 12:07 am Post subject: performance differance |
|
|
Hi
Can someone tell me if there is a diffeerence in performance between the following 2 queries?
Here index1 is a duplicate index and can have upto 25 duplicate rows.
1) | Code: |
SELECT index1
,nonindex4
FROM dbtst.tb1 a
,dbtst.tb1 b
WHERE a.nonindex1 = 'constant1'
AND a.index1 = b.index1
AND (b.nonindex2 = 'constant2'
OR b.nonindex3 = 'constant3')
; |
2) | Code: |
SELECT index1
,nonindex4
from dbtst.tb1 a
WHERE a.nonindex1 = 'constant1'
AND exists ( SELECT *
FROM dbtst.tb1 b
WHERE b.index1 = a.index1
AND (b.nonindex2 = 'constant2'
OR b.nonindex3 = 'constant3')) ; |
Thanx |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Thu Dec 14, 2006 6:30 am Post subject: |
|
|
The difference is, that in the first case DB2 can decide, which table will be treated as outer table of the join and which one as inner table.
Your second query forces DB2 in the way the query is split into query blocks. Whether this is better or worse in terms of performance depends solely on the structure and quantity of your data.
regards
Christian |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
