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
 
UNION ALL - Which one performs better?

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

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Mon Oct 30, 2006 5:41 pm    Post subject: UNION ALL - Which one performs better?
Reply with quote

Hi,

I have 2 queries: icon_lol.gif

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 icon_question.gif

Y icon_lol.gif Y icon_lol.gif Y icon_lol.gif
Back to top
View user's profile Send private message

ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Mon Oct 30, 2006 6:36 pm    Post subject:
Reply with quote

Hi,

SELECT NAME
FROM TABLE1
WHERE field1 = :TEST;

This will have better performance and gives the same result.

Reddy.
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Oct 31, 2006 12:05 am    Post subject: Re: UNION ALL - Which one performs better?
Reply with quote

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
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Tue Oct 31, 2006 11:43 am    Post subject:
Reply with quote

Thanks a lot guys!
Back to top
View user's profile Send private message
MCEVOY

New User


Joined: 21 Nov 2005
Posts: 18

PostPosted: Wed Nov 01, 2006 2:19 pm    Post subject: Re: UNION ALL - Which one performs better?
Reply with quote

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
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Nov 01, 2006 2:59 pm    Post subject:
Reply with quote

Good catch MCEVOY... but the EFF_DATE in my case is a NOT NULL field.
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 Illegal use of keyword fetch. Token w... HABBIE DB2 2 Fri Aug 28, 2015 5:20 pm
No new posts Performs until subscript Value reache... rupeshgullu COBOL Programming 10 Tue Mar 25, 2014 5:12 pm
No new posts Can we Update cursor with group by an... vyasricha DB2 5 Sat May 26, 2012 11:05 am
No new posts Union or join query GuyC DB2 2 Tue May 10, 2011 1:25 pm
No new posts UNION in PLI Appu PL/I & Assembler 2 Thu Oct 14, 2010 9:52 am

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