Geek Answers Handbook

What is faster IN or OR?

What is faster in T-SQL?

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 to the stored procedure. And I try to execute my DELETE and INSERT statements as efficiently as possible.

+6
performance optimization profiling tsql sql-optimization
source share
9 answers

"IN" will be translated into the "OR" series ... if you look at the query execution plan with "IN", you will see that it has expanded it.

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

+11
source share

Do not think; profile.

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

+14
source share

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

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

+4
source share

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

+3
source share

they should generate an accurate accurate plan from my experience

take a look at the plan

+2
source share

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

+1
source share

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

+1
source share

Absolutely fast access to SQL Server is using DELETE with INNER JOIN. With three values ​​you will not notice the difference, but with a large number of values ​​(we make several thousand) the difference is phenomenal. You can cross out your values ​​in a temporary table, and then join it.

eg.

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

You can also add an optional where clause.

+1
source share

It must be exactly equal. Most RDMBS transalte IN to ORs .

Of course, if you think that transferring from INs to ORs is time consuming, offering with ORs is faster; -)

Update: I am considering that A is a column.

0
source share

More articles:


All Articles