Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

BETWEEN clause in SQL

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

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Tue Aug 12, 2008 6:05 pm    Post subject: BETWEEN clause in SQL
Reply with quote

Hi,

Can somebody tell me which way is better in terms of performance and Why?

WHERE
EMPLOYEE_NO BETWEEN 10000 AND 20000

or

WHERE
EMPLOYEE_NO >= 10000 AND EMPLOYEE_NO <= 20000
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Aug 12, 2008 7:22 pm    Post subject:
Reply with quote

I would say between. Why ?

1. Between is a predicate given in DB2 itself for limitation purpose. So, i think they must have coded something relating to performance. When using between it checks for the number between the 2 limitations

2. When using WHERE it has to check for 3 conditions, i think.
a) >= condition
b) <= condition
c) AND

All the things i said, are to my thinking only.

And ofcourse, i could be wrong
Sushanth
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 12, 2008 8:39 pm    Post subject:
Reply with quote

Your answer is correct but explanation is wrong ....

BETWEEN is usually more efficient than <= predicate and the >= predicate
except when comparing a host variable to 2 columns ...

Stage 2 :

WHERE
:hostvar BETWEEN col1 and col2

Stage 1:

WHERE
Col1 <= :hostvar AND col2 >= :hostvar
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Aug 13, 2008 10:35 am    Post subject:
Reply with quote

So, my 2 POINT is CORRECT.
It has to check for three conditions.
>= , <=.
Both these conditions has to be satisfied. That is taken care by AND condtion.

And i like to shorten my 1st point.
Why make query bigger, when u can make it smaller.

Alway use in-built functions.
[ Why create new functions & methods, when they already exist]
____________________________________________________________

Ashimer,

HOST VARIABLES are gonna be holding values(place holders) when the query is sent to DB2, Host variables will be replaced by values.
So, how on earth they can affect anything regarding performance.
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Wed Aug 13, 2008 2:44 pm    Post subject: Reply to: BETWEEN clause in SQL
Reply with quote

Sushanth - as we all know Stage 1 predicate are more efficient thn Stage 2 predicate and what ashimer has suggested is the way a particular value of hostvaraible defining a predicate as Stage 1 or Stage 2.

Predicates in the format of a literal compared to a literal are Stage 2 predicates; and so are predicates that compare a host variable to a host variable and those that compare a host variable to a literal.

In case of between

COL BETWEEN value1 AND value2 is Stage 1
value BETWEEN COL1 AND COL2 is not .

where value is a constant, host variable, or special register

Hope this helps.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 13, 2008 10:02 pm    Post subject:
Reply with quote

Hello,

Quote:
Why make query bigger, when u can make it smaller.
Do not confuse a smaller amount of code with a smaller amount of recource consumption or that "smaller is always better". That is totally wrong.

It is completely inappropriate to worry about the one-time cost of writing a well-performing query versus the forever cost of running a poorly constructed query just to "make it smaller".

Possibly there is something i misunderstand. . .
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 Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts [SQL0029] INTO clause missing from em... HABBIE DB2 2 Fri Sep 04, 2015 3:54 pm
No new posts 3-D Table with nested DEPENDING ON Cl... VivekKhanna COBOL Programming 5 Sat Aug 08, 2015 11:18 pm
No new posts when=group clause: question about the... tuxama DFSORT/ICETOOL 2 Thu Mar 26, 2015 12:51 pm
No new posts pic clause on group variable error CuriousMainframer COBOL Programming 6 Mon Jul 14, 2014 6:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us