7

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.

chaos
  • 122,029
  • 33
  • 303
  • 309
Yvo
  • 18,681
  • 11
  • 71
  • 90

9 Answers9

14

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.

chaos
  • 122,029
  • 33
  • 303
  • 309
12

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

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • Thnx, in the execution plan I saw that it would cost exactly the same amount of CPU time. – Yvo Jul 13 '09 at 14:42
  • 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. – Frank V Jul 13 '09 at 15:10
  • @Frank, we are talking about INs with value lists, not INs with subqueries. – Andrew Moore Jul 13 '09 at 15:41
  • @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. – Frank V Jul 13 '09 at 15:43
  • In this scenario they are the same. – AdaTheDev Jul 13 '09 at 17:11
4

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.

C. K. Young
  • 219,335
  • 46
  • 382
  • 435
3

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

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
2

they should generate the same exact plan from my experience

take a look at the plan

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
1

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

l0b0
  • 55,365
  • 30
  • 138
  • 223
1

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

Tundey
  • 2,926
  • 1
  • 23
  • 27
  • It might also convert it to a join, in the case where the contents of your IN are the results of another SELECT statement. – Joel Coehoorn Jul 13 '09 at 14:45
1

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.

monibius
  • 1,565
  • 16
  • 28
0

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.

FerranB
  • 35,683
  • 18
  • 66
  • 85