May 15, 2002 at 12:12 am
Hi
I have the following query,
select *
from table1
where field1 IN ( 'Row5','Row3','Row6','Row100' )
v/s
select *
from table1
where field1 = 'Row5' or
field1 = 'Row3' or
field1 = 'Row6' or
field1 = 'Row100'
Note:: There is a non clustered index on column 'field1'
I checked the execution plan for both queries, they are same however, for the first execution the compilation time for the second query is higher than the first. In both the cases the index usage is found to be the same.
What would be the reason ? Should IN be prefered to OR clause.
Thanks
May 15, 2002 at 4:36 am
Small list really don't make much difference, however IN has an ability that OR does not that I have seen on occasion and look for, plus IN is means less code to read.
However the thing that IN can do that OR I have never seen do is this. If the Query manager engine decides it can speed up performance it may build a temporary dataset table from the items in the in and INNER JOIN this memory resident table to the main table increasing the speed by which it can address the data. I was testing several scenarios and never could find the exact threshold as I had one query that this would happen on at 10 items in the list and another that varied between 17 and 30 each run. IN should be the preference but if you change to another DB system keep in mind what IN represents as it may not be supported by the other server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 17, 2002 at 10:48 am
Also you could consider using Unions i.e.
select *
from table1
where field1 = 'Row5'
union
select *
from table1
where field1 = 'Row3'
union
select *
from table1
where field1 = 'Row6'
union
select *
from table1
where field1 = 'Row100'
...slightly less readable than IN, but I've found particularly with complex multi-table joins, unions can be hugely more efficient than using IN or OR.
Dave J
Dave J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply