CODEWITHSUNDEEP
t-sqloptimizationprofilingperformancesql-optimization
Yvo
Yvo

Reputation: 19263

What's faster IN or OR?

In T-SQL what's faster?

DELETE * FROM ... WHERE A IN (x,y,z)

Or

DELETE * FROM ... WHERE A = x OR A = y OR A = z

In my case x, y and z are input parameters for the stored procedure. And I'm trying to get the performance of my DELETE and INSERT statements to the best of my abilities.

Upvotes: 7

Views: 3279

Answers (9)

monibius
monibius

Reputation: 1579

The absolute fastest under SQL Server is to use a DELETE with an INNER JOIN. With three values you wont notice the difference, but with more values (we are doing several thousand) the difference is phenominal. You could stash your values into a temporay table then join onto that.

E.g.

DELETE C
FROM Customer AS C INNER JOIN #ValuesToDelete AS D ON C.CustID = D.CustID

You can also add an optional where clause.

Upvotes: 1

Tundey
Tundey

Reputation: 2965

Regardless of whether or not A is a computation or column, looks like SQL Server 2005 converts IN to OR clauses.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135111

they should generate the same exact plan from my experience

take a look at the plan

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425643

In SQL Server, the optimizer will generate identical plans for these queries.

Upvotes: 4

C. K. Young
C. K. Young

Reputation: 223133

Write two stored procedures, one using IN, the other using OR, on a test server. Run each procedure 10,000 (or 1,000,000, or whatever) times, and compare the timings.

In general, this is pretty much the "only" way to have a good answer to the question of which approach is faster: write simple timing test cases, and run them many, many times.

Upvotes: 4

chaos
chaos

Reputation: 124325

Don't think; profile.

I urge you not to rely on intuition, yours or anyone else's, when considering questions of speed. Instead, try both options, with some kind of profiling/run time measurement, and find out which is faster in your circumstances.

Upvotes: 15

AdaTheDev
AdaTheDev

Reputation: 147314

"IN" will be translated to a series of "OR"s...if you look at the execution plan for a query with "IN", you'll see it has expanded it out.

Much cleaner to use "IN" in my opinion, especially in larger queries it makes it much more readable.

Upvotes: 12

l0b0
l0b0

Reputation: 58918

If A is a computation, it will be performed once using IN and N times using OR.

Upvotes: 1

FerranB
FerranB

Reputation: 36817

It must be exactly equals. Most of RDMBS transalte IN to ORs.

Of course, if you consider the translation from INs to ORs to be high time consuming, the sentence with ORs is faster ;-)

Update: I'm considering that A is a column.

Upvotes: 0

Related Questions