|
View previous topic :: View next topic
|
| Author |
Message |
Yoyoyo
New User
Joined: 19 Sep 2006
Posts: 26
|
|
|
|
Hi,
I have 2 queries: 
QUERY 1:
| Code: |
SELECT NAME
FROM TABLE1
WHERE EFF_DATE <= :WS-EFF-DT
AND field1 = :TEST
UNION ALL
SELECT NAME
FROM TABLE1
WHERE EFF_DATE > :WS-EFF-DT
AND field1 = :TEST
|
QUERY 2:
| Code: |
SELECT NAME
FROM TABLE1
WHERE (EFF_DATE <= :WS-EFF-DT
OR EFF_DATE > :WS-EFF-DT)
AND field1 = :TEST
|
Which query will have better performance 
Y Y Y |
|
| Back to top |
|
 |
ovreddy
Active User
.jpg)
Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.
|
|
|
|
Hi,
SELECT NAME
FROM TABLE1
WHERE field1 = :TEST;
This will have better performance and gives the same result.
Reddy. |
|
| Back to top |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
The second query (with no union all) and the one suggessted with only field1 in the where clause are the same in performance.
Since the date is having all possible conditions, (>,< and =) its good to not use them in the SQL. |
|
| Back to top |
|
|
Yoyoyo
New User
Joined: 19 Sep 2006
Posts: 26
|
|
|
|
| Thanks a lot guys! |
|
| Back to top |
|
|
MCEVOY
New User
Joined: 21 Nov 2005
Posts: 18
|
|
|
|
| Have you considered that EFF_DTE might be nullable, and therefore the suggested improvements may not be equivalent to the original query? |
|
| Back to top |
|
|
Yoyoyo
New User
Joined: 19 Sep 2006
Posts: 26
|
|
|
|
| Good catch MCEVOY... but the EFF_DATE in my case is a NOT NULL field. |
|
| Back to top |
|
|
|
|
