|
View previous topic :: View next topic
|
| Author |
Message |
harijax
New User
Joined: 16 Jul 2008
Posts: 22
Location: Bangalore
|
|
|
|
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 |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Cristopher
New User
Joined: 31 Jul 2008
Posts: 53
Location: NY
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
