SQL SERVER – Performance Comparison IN vs OR

Some questions never get old and some question and I believe we will be discussing them for many more years in the future. Just other days, I received this question during my SQL Server Performance Tuning Practical Workshop. The question was about Performance Comparison IN vs OR. Though personally, I have answered this question quite a many times before, let us answer it once again, it never hurts to repeat the truth multiple times.

Question: Which is query runs faster –

  1. The query with IN operator
  2. The query with OR operator

Well, the answer is – Both are Equal. Yes, that is the truth. Let us see a simple demonstration.

Fun following query on the sample database AdventureWorks

Solarwinds
SET STATISTICS IO ON
GO
SELECT TOP (1000) [CustomerID]
,[PersonID]
,[StoreID]
,[AccountNumber]
FROM [AdventureWorks2014].[Sales].[Customer]
WHERE StoreID IN (934,1028,642,932,1026)
GO
SELECT TOP (1000) [CustomerID]
,[PersonID]
,[StoreID]
,[AccountNumber]
FROM [AdventureWorks2014].[Sales].[Customer]
WHERE StoreID = 934 OR StoreID = 1028
OR StoreID = 642 OR StoreID = 932 OR StoreID = 1026
GO

Now let us go and check messages:

(10 rows affected)
Table ‘Customer’. Scan count 1, logical reads 123, physical reads 0,…

(10 rows affected)
Table ‘Customer’. Scan count 1, logical reads 123, physical reads 0,…

You will notice in either of the cases, SQL Server takes the same amount of logical reads. Now let us see the execution plan as well.

SQL SERVER - Performance Comparison IN vs OR inor1

As you can see from the execution plan, they are absolutely identical and they both have exactly the same missing index warning as well.

SQL SERVER - Performance Comparison IN vs OR inor2

However, if you carefully look at the Filter Operator in the execution plan of the query where I have used IN condition, you can see that SQL Server execution plan has converted that all the values from the IN operator to OR automatically. Yes, that is correct, SQL Server optimizer engine internally automatically maps all the values specified in the IN operator to OR operator. As SQL Server converts IN to OR automatically that is the primary reasons for both them having identical performance.

Please leave a comment and let me know if you would love to see any similar performance comparison.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?
Next Post
SQL SERVER – Optimal Value Max Worker Threads

Related Posts

11 Comments. Leave new

  • Would like to see how this goes with NOT IN and NOT EXISTS

    Reply
  • Also when you join two tables using IN operator, always use table alias for columns

    https://exploresql.com/2018/11/21/sql-server-always-use-table-alias-when-using-in-operator/

    Reply
  • So, I wonder what’s the alternate solution which is more performance-friendly?

    Reply
  • What about IN using sub query and JOIN, which is better?

    SET STATISTICS IO ON
    GO

    –Query using IN
    select top 100 * from [Sales].[SalesOrderHeader] soh where SalesOrderID in (select SalesOrderID from [Sales].[SalesOrderDetail] sod where ProductID=710)
    GO

    –Query using JOIN
    select top 100 soh.* from [Sales].[SalesOrderHeader] soh inner join [Sales].[SalesOrderDetail] sod on soh.SalesOrderID=sod.SalesOrderID
    where sod.ProductID=710
    GO

    –I see both the queries are taking same number of logical reads.

    –Query using IN
    (44 row(s) affected)
    Table ‘SalesOrderHeader’. Scan count 0, logical reads 143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘SalesOrderDetail’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    –Query using JOIN
    (44 row(s) affected)
    Table ‘SalesOrderHeader’. Scan count 0, logical reads 143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘SalesOrderDetail’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Reply
  • chirag solanki
    March 16, 2020 4:30 pm

    Hi Pinal ,

    I am big fan of your blog and I learn very much from your articles.

    I have one question , How to handle options arguments in store procedure query. For example I have query to get all sales with optional argument like Item , Brand ,Product, Department , Sales Date etc.. This fields are not mandatory. Currently I am using query like this (@BrandID = 0 or SalesItem.BrandID = @BrandID) which create performance issue .. Please give me right way to pass options arguments veritable without create dynamic sql query.. Thank you in advance

    Reply
  • Hi chirag solanki,

    Pass NULL to the input parameter and change the where condition as below:

    WHERE SalesItem.BrandID = ISNULL(@BrandID,SalesItem.BrandID)

    –Mayura

    Reply
  • chirag solanki
    March 17, 2020 3:52 pm

    No improvement in query

    declare @Barcode varchar(120) = null
    set @Barcode = ‘CCC011912061351’

    select *from trnStock
    where Barcode = @Barcode

    (1 row affected)
    Table ‘trnStock’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.

    select *from trnStock
    where Barcode = isnull(@Barcode,Barcode)

    (1 row affected)
    Table ‘trnStock’. Scan count 1, logical reads 17143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 109 ms, elapsed time = 102 ms.

    Reply
    • Try this method

      declare @Barcode varchar(120) = null
      set @Barcode = ‘CCC011912061351’

      If @Barcode is not null
      select *from trnStock
      where Barcode = @Barcode
      else
      select *from trnStock;

      Reply

Leave a Reply

Menu