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.

Integumentary

Answer 1

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

Right

Thnx, in the execution plan I saw that it would cost exactly the same amount of CPU time. – Integumentary

This isn't true. In is not translated as a serise of ors. You'd be best to profile your query and actually see the result. More often than not EXISTS is faster than IN. But OR should be faster than both. It depends on indexes though. – Ralph

@Frank, we are talking about INs with value lists, not INs with subqueries. – Audrieaudris

@Andrew: Then I'm obviously wrong about the EXISTS. But I still understand that IN and OR are internalized differently by SQL server -- sub query or constant values. – Ralph

In this scenario they are the same. – Right

Answer 2

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.

Ohalloran

+1 Thank you for backing up what I'm trying to say myself. :-) – Arsine

Answer 3

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.

Arsine

Answer 4

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

Stonefly

Answer 5

they should generate the same exact plan from my experience

take a look at the plan

Supereminent

Answer 6

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

Nedneda

Answer 7

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

Paronymous

It might also convert it to a join, in the case where the contents of your IN are the results of another SELECT statement. – Slype

Answer 8

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.

Ligule

Answer 9

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.

Melan