10774 - BETWEEN command against the command IN

Hi experts,

can you give me an example in TSQL for this:
The BETWEEN command is more efficient than
the IN because the first for a range of values and the second, more specific values
causing the judgment becomes less effective.
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
And again: Without context, this does not hold.
0
enrique_aeoAuthor Commented:
It is advice for the optimization of queries
0
ste5anSenior DeveloperCommented:
There is only one general advice about performance:

Test all different approches. It's not possible (almost) to predict what the optimizer will do. So just test and measure the different possibilities.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

enrique_aeoAuthor Commented:
can you give an example in TSQL code
0
ste5anSenior DeveloperCommented:
How should I guess, what queries you have to optimize?????????

You need to test and measure all possible predicates of your actual query on your real data on the real server to get a comparable result.

It's simple: THERE IS NO RULE OF THUMB HERE. TESTING AND MEASURING ÍS THE ONLY APPROACH.
0
PortletPaulEE Topic AdvisorCommented:
This question absolutely needs more information it's too broad to have a simple answer.

For example
   neither BETWEEN or IN would be suitable for a date range
   are we discussing 12 vales for the "IN list" or 1.2 million?

Plus: The 2 features are not exact substitutes as they do quite different things.

   field BETWEEN 1 and 33 is the equivalent of: field >= 1 and value <= 33

   field IN (1,2,33) is the equivalent  ( field = 1 OR field = 2 OR field = 33 )

so:
   between would return any number 1 through 33 in that field
   IN (1,2,33) only returns 1 or 2 or 33
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
in thi example is the same

set statistics io on
select *
from Sales.SalesOrderDetail sod
where ProductID between 707 and 710

select *
from Sales.SalesOrderDetail sod
where ProductID IN (707,708, 709,710)
set statistics io off


(6321 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 2476
(6321 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 2476

an another example please?
0
Scott PletcherSenior DBACommented:
The logical reads would not affected, but the total number of comparisons SQL needs to make to determine whether the WHERE is true or not is affected.

BETWEEN would be more efficient than a list because of fewer comparisons: the longer the list, the more BETWEEN gains.  That is:
BETWEEN 1 AND 100
is roughly 50 times better than:
IN (1, 2, 3, ..., 97, 98, 99, 100)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
enrique_aeo, do you still need help on this question?
0
enrique_aeoAuthor Commented:
an example would be perfect TSQL code
0
ste5anSenior DeveloperCommented:
You need to examine your concrete case. There is no rule of thumb, which version is faster. E.g. here's an example where IN is better in terms of cost depending on existing indices:

Without index

Capture-woi.PNG
vs. with index

Capture-wi.PNG
0
Scott PletcherSenior DBACommented:
@ste5an:

That doesn't demonstrate that IN is "better" in those examples.  You have to look at the query plan and, particularly, the I/O stats.  Query cost is an extremely rough estimate that at times can be very far off.  I've seen it report 90% of the cost to a query that is in fact much more efficient.
0
ste5anSenior DeveloperCommented:
Yup, I know, but in this concrete case IN is faster. Well, on a real small scale, but it is faster.

that at times can be very far off

Guess why I wrote, it must be measured and compared in concrete cases?

btw, the run-time difference is due the parameterization ;)
0
Scott PletcherSenior DBACommented:
Sometimes you can still generalize.

For example, with a keyed index available, a sargable expression will yield a better search than a non-sargable one, regardless of the specific data.

Likewise, BETWEEN is so much efficient than an IN list that there is no reason to use an IN list if BETWEEN is available and does the same thing.  The case where IN might produce a better plan (I guess anything is possible) is so extreme as to not be worth the trouble.
0
enrique_aeoAuthor Commented:
set statistics io on
      select *
      from Person.Contact c
      where c.ModifiedDate between '20030208' and '20030210'

      select *
      from Person.Contact c
      where c.ModifiedDate IN ('20030208', '20030209','20030210')
set statistics io off


(29 row(s) affected)
Table 'Contact'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(29 row(s) affected)
Table 'Contact'. Scan count 3, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.