 |
|
|
|
Next: Benchmarking query with profiler
|
| Author |
Message |
External
Since: Aug 15, 2008
Posts: 14
|
(Msg. 1) Posted: Wed Oct 08, 2008 9:04 am
Post subject: BETWEEN vs IN()
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
These two queries yield identical results. Query1 runs in about a
half-minute. Query2 runs in less than a second.
The only difference is in the WHERE clause. Query1 uses a BETWEEN for
sDate, Query2 uses IN().
The IN works for the dates because the tSales.sDate is always the
Friday of a week.
I have read that using BETWEEN is more optimal than using an IN(),
though not true in this case.
I have two questions.
1) Which is generally be the best choice in query design: IN() or
BETWEEN?
2) What is the maximum amount of criteria items that an IN()
statement can accept: IN(1,2,3,4,5,6...to ?)
Query1 - sDate BETWEEN
SELECT Retailer,
Dept,
SUM(Sales),
SUM(Units)
FROM tSales
WHERE Retailer IN ( 1094, 192, 3673, 1420, 822 )
and Dept IN (46)
AND sDate BETWEEN '2007-02-02 00:00:00.000' AND '2007-02-23
00:00:00.000'
GROUP BY Retailer, Dept
Query2 - sDate IN()
SELECT Retailer,
Dept,
SUM(Sales),
SUM(Units)
FROM tSales
WHERE Retailer IN ( 1094, 192, 3673, 1420, 822 )
and Dept IN (46)
AND sDate IN ( '2007-02-02', '2007-02-09', '2007-02-16',
'2007-02-23' )
GROUP BY Retailer, Dept |
|
| Back to top |
|
 | |
External
Since: Jan 11, 2008
Posts: 1069
|
(Msg. 2) Posted: Wed Oct 08, 2008 9:43 am
Post subject: Re: BETWEEN vs IN() [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"A problem well stated is a problem half solved." -- Charles F.
Kettering
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.
Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html
Since we have no idea what constraints, indexes, etc. you have there
is not way to guess as to causes. A differetn schema would probably
have differetn results. That is a part of SQL that drives procedural
programmers nuts -- add an index or constraint and the same query
works differently than it did before. |
|
| Back to top |
|
| |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2160
|
(Msg. 3) Posted: Wed Oct 08, 2008 12:14 pm
Post subject: Re: BETWEEN vs IN() [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
The best choice in this case is
WHERE sDate >= '20070202' AND sDate < '20070224'
To find out why your queries are performing differently, you'll need to look
at the execution plans.
And there is no way anyone can say "BETWEEN is more optimal than IN"... It
is a case by case basis, like much of everything else. I do highly
recommend AGAINST using BETWEEN for datetime range queries, but this has
nothing to do with performance.
On 10/8/08 12:04 PM, in article
422cd248-a666-4611-8903-d5a1e4a9d1b1.DeleteThis@c36g2000prc.googlegroups.com,
"benatom@comcast.net" <benatom.DeleteThis@comcast.net> wrote:
> These two queries yield identical results. Query1 runs in about a
> half-minute. Query2 runs in less than a second.
>
> The only difference is in the WHERE clause. Query1 uses a BETWEEN for
> sDate, Query2 uses IN().
>
> The IN works for the dates because the tSales.sDate is always the
> Friday of a week.
>
> I have read that using BETWEEN is more optimal than using an IN(),
> though not true in this case.
>
> I have two questions.
>
> 1) Which is generally be the best choice in query design: IN() or
> BETWEEN?
> 2) What is the maximum amount of criteria items that an IN()
> statement can accept: IN(1,2,3,4,5,6...to ?)
>
>
> Query1 - sDate BETWEEN
>
> SELECT Retailer,
> Dept,
> SUM(Sales),
> SUM(Units)
> FROM tSales
> WHERE Retailer IN ( 1094, 192, 3673, 1420, 822 )
> and Dept IN (46)
> AND sDate BETWEEN '2007-02-02 00:00:00.000' AND '2007-02-23
> 00:00:00.000'
> GROUP BY Retailer, Dept
>
> Query2 - sDate IN()
>
> SELECT Retailer,
> Dept,
> SUM(Sales),
> SUM(Units)
> FROM tSales
> WHERE Retailer IN ( 1094, 192, 3673, 1420, 822 )
> and Dept IN (46)
> AND sDate IN ( '2007-02-02', '2007-02-09', '2007-02-16',
> '2007-02-23' )
> GROUP BY Retailer, Dept >> Stay informed about: BETWEEN vs IN() |
|
| Back to top |
|
| |
External
Since: Aug 20, 2008
Posts: 646
|
(Msg. 4) Posted: Wed Oct 08, 2008 2:06 pm
Post subject: Re: BETWEEN vs IN() [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
benatom DeleteThis @comcast.net wrote:
> 1) Which is generally be the best choice in query design: IN() or
> BETWEEN?
I agree with Aaron on using a half-open interval when querying date
intervals, instead of BETWEEN. However, on SQL Server 2008 when using
the DATE data type BETWEEN makes sense.
If you have correct indexes, statistics, etc., the query optimizer
should pick a good plan. I tested on SQL Server 2008 and it generates
the same query plan for both IN and BETWEEN (but that was a very small
data set to test with to be able to judge).
> 2) What is the maximum amount of criteria items that an IN()
> statement can accept: IN(1,2,3,4,5,6...to ?)
There is no limit on the number of items you can have listed in IN (or
if there is one it is probably much bigger that anyone will be willing
to type). Normally you use IN with a subquery, like "col IN (SELECT col
FROM Table)", and having a limit will not be practical.
--
Plamen Ratchev
http://www.SQLStudio.com >> Stay informed about: BETWEEN vs IN() |
|
| Back to top |
|
| |
External
Since: Sep 09, 2003
Posts: 255
|
(Msg. 5) Posted: Thu Oct 09, 2008 6:25 pm
Post subject: Re: BETWEEN vs IN() [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> 1) Which is generally be the best choice in query design: IN() or
> BETWEEN?
Generally BETWEEN performs better than IN. As mentioned by others, I
would advise againts using BETWEEN for data types like date, datetime,
float, etc. and use "<", "<=", "<" and/or "<=" instead.
If you have a case where IN performs better, then your statistics are
probably outdated and no longer represent the data. You should run
UPDATE STATISTICS, preferably WITH FULLSCAN.
> 2) What is the maximum amount of criteria items that an IN()
> statement can accept: IN(1,2,3,4,5,6...to ?)
Once you add more than a few hundred values, the compilation time will
start to grow very fast. More than 500 values and the compilation will
practically bring down the server. So as a rule of thumb, it would only
consider it for less than 100 values.
--
Gert-Jan
SQL Server MVP >> Stay informed about: BETWEEN vs IN() |
|
| Back to top |
|
| |
| Related Topics: | need help with query - I want to sum the records in a table (u) which have 'P.M.' in a field value and test against a fixed limit something to the effect of : case when SUM(substring(u.sTime,1,4)='P.M.') <= 64 Then .... except it should work ...
Next?? - I have a field stored as NTEXT(16). I need to replace some of the content in this field. I know the replace function doesnt work. I see there is UPDATETEXT - is this the ONLY way to do it ?? 'cos it looks quite complicated? thanks, Justin
How can I get the resultset from a stored procedure - I have a stored procedure which will output a table. How can I use a sql statement to get the result of the sp? Thanks a lot
Audit V2 - I am looking to write a SQL script that will show me for each table, in all the databases on a server, which users / groups have access. In addition, I want to break the access into whether it is SELECT, UPDATE, INSERT or DELETE permissions. Is this..
Detecting an existence of local temporary table - Hi, I create a temporary table. for example: <font color=purple> ; create table #t1 (ID_T1 integer)</font> Next I would like to detect If the table already exists: exec sp_table #t1 if @@ROWCOUNT > 0 <font color=purple>&... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Profile
Private Messages
Log in
