The following query:
SELECT * FROM Customer WHERE CustID = 1 OR CustID = 5 OR CustID = 10
can also be written as:
SELECT * FROM Customer WHERE CustID IN (1, 5, 10)
Certainly the latter query is concise. Also I have a clustered index on
CustID. Should I prefer former or latter?
Also if I need construct a query with IN/OR operator that contains 200 or
300 values in it, will performance degrade? Is there an alternative? The
BETWEEN operator is not an option for me.
Thanks.
Raghu/..> Certainly the latter query is concise. Also I have a clustered index on
quote:
> CustID. Should I prefer former or latter?
What does "should" mean? At what point does conciseness beat out
performance? Did you try both queries and compare performance? Did you see
any difference in the execution plans?
quote:
> Also if I need construct a query with IN/OR operator that contains 200 or
> 300 values in it, will performance degrade?
Did you try it?
quote:
> Is there an alternative?
Stuff your list into a single-column (one row per list value) table, maybe
even a #temp table, and perform an inner join?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||You raised all good questions. However I am still in the early stages.
1) Is there a difference between IN and OR in the way I used it?
2) When using lots of values, will there be a difference between using IN or
OR operator?
If the only answer to above question is to try, then I will do that. I am
looking for some logical explanation.
Thanks.
Raghu/..
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23KyzM$e5DHA.2432@.TK2MSFTNGP10.phx.gbl...
quote:
> What does "should" mean? At what point does conciseness beat out
> performance? Did you try both queries and compare performance? Did you
see
quote:|||IN is the same as a bunch of = OR'd together as far as the query engine is
> any difference in the execution plans?
>
or[QUOTE]
> Did you try it?
>
> Stuff your list into a single-column (one row per list value) table, maybe
> even a #temp table, and perform an inner join?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
concerned
Greg Jackson
PDX, Oregon|||>I am looking for some logical explanation.
Interesting choice of words.
You have a set of values that you want to use
in the IN clause; yet, that set of values is not
in your database -- whose purpose in life is to
help you play with sets of data. Was it illogical
to play with that set of data without the aid
of the database which you have at hand?
Furthermore, is it going to be illogical
to send that set of data through
the sql statement compiler?
You will likely find this post:
From: Delbert Glass (delbert@.noincoming.com)
Subject: Re: IN clause very slow
Newsgroups: microsoft.public.sqlserver.programming
Date: 2002-07-26 13:50:16 PST
(and the thread containing it) of interest.
You might even wonder
whether or not the sql statement compiler
as been improved since that time.
Ponder:
Isn't the principal illogicalness of sending
a set of data through the sql statement compiler
independent of how much effort is incurred?
Bye,
Delbert Glass
"Raghu" <Raghu@.nospamzzzqcsi.com> wrote in message
news:eWQmNPf5DHA.1804@.TK2MSFTNGP12.phx.gbl...
quote:
> You raised all good questions. However I am still in the early stages.
> 1) Is there a difference between IN and OR in the way I used it?
> 2) When using lots of values, will there be a difference between using IN
or
quote:|||>SELECT * FROM Customer WHERE CustID = 1 OR CustID = 5 OR CustID = 10
> OR operator?
> If the only answer to above question is to try, then I will do that. I am
> looking for some logical explanation.
> Thanks.
> Raghu/..
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23KyzM$e5DHA.2432@.TK2MSFTNGP10.phx.gbl...
on[QUOTE]
> see
> or
maybe[QUOTE]
>
quote:
>can also be written as:
>SELECT * FROM Customer WHERE CustID IN (1, 5, 10)
For these examples,
one can expect the IN version to be converted to the OR version.
(Take a guess what you will see if you point
your mouse at the different operators in the plans.)
You could also write:
SELECT * FROM Customer WHERE CustID = 1
UNION ALL
SELECT * FROM Customer WHERE CustID = 5
UNION ALL
SELECT * FROM Customer WHERE CustID = 10
depending on what version of which database engine
you are using there can be a big difference
between how well the different versions execute.
Bye,
Delbert Glass
"Raghu" <Raghu@.nospamzzzqcsi.com> wrote in message
news:efrJJ5e5DHA.2576@.TK2MSFTNGP11.phx.gbl...
quote:|||> 2) When using lots of values, will there be a difference between using IN or
> Is there any preference to using IN versus OR operator in a WHERE clause?
> The following query:
> SELECT * FROM Customer WHERE CustID = 1 OR CustID = 5 OR CustID = 10
> can also be written as:
> SELECT * FROM Customer WHERE CustID IN (1, 5, 10)
> Certainly the latter query is concise. Also I have a clustered index on
> CustID. Should I prefer former or latter?
> Also if I need construct a query with IN/OR operator that contains 200 or
> 300 values in it, will performance degrade? Is there an alternative? The
> BETWEEN operator is not an option for me.
> Thanks.
> Raghu/..
>
quote:
d">
> OR operator?
As Jaxon mentioned, the Query Processor will convert the IN to a bunch of OR
's before the optimizer
gets is hand on the query. However, I've seen cases where a LOT of values pr
oduces bad performance
(search KB). In this case, creating a table and do JOIN/IN/EXISTS against th
at table should be
better.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raghu" <Raghu@.nospamzzzqcsi.com> wrote in message news:eWQmNPf5DHA.1804@.TK2MSFTNGP12.phx.gbl...[Q
UOTE]
> You raised all good questions. However I am still in the early stages.
> 1) Is there a difference between IN and OR in the way I used it?
> 2) When using lots of values, will there be a difference between using IN
or
> OR operator?
> If the only answer to above question is to try, then I will do that. I am
> looking for some logical explanation.
> Thanks.
> Raghu/..
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23KyzM$e5DHA.2432@.TK2MSFTNGP10.phx.gbl...
> see
> or
>[/QUOTE]|||Good idea.
I'd never heard of this trick.
However, I'm not so proud as not to piggy back on it....:-)
If creating a Table to dump the values into, I would recommend a Table
Variable as opposed to a Temp Table.
Cheers,
Greg Jackson
PDX, Oregon|||> If creating a Table to dump the values into, I would recommend a Table
quote:
> Variable as opposed to a Temp Table.
I suggest trying both. In this case, a table variable seems reasonable, I
admit. Just to point out that table variables are sometimes slower than temp
tables (one reason is that the optimizer doesn't care about collecting any
statistics for temp variables).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uvzsqdp5DHA.2300@.TK2MSFTNGP10.phx.gbl...
quote:|||cool.
> Good idea.
> I'd never heard of this trick.
> However, I'm not so proud as not to piggy back on it....:-)
> If creating a Table to dump the values into, I would recommend a Table
> Variable as opposed to a Temp Table.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
again, another thing I did not know.
thanks
GAJ
No comments:
Post a Comment