What's faster IN or OR?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

"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.

Answer 4

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.

Answer 5

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.

Answer 6

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

Answer 7

they should generate the same exact plan from my experience

take a look at the plan

Answer 8

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

Answer 9

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.