SQL SERVER – Performance: Optimizing High Volume OR Conditions through TempTable

Following our previous discussion on the performance difference between using “OR” and “IN” in SQL queries – link to the previous article, we will dive a bit deeper into the topic. In this edition, we explore an alternative approach when dealing with high volumes of ‘OR’ conditions in our SQL queries: the use of a TempTable.

The SQL Server query optimizer is a powerful tool that can interpret the intent of a query and choose the most efficient execution plan, as we learned from the previous blog post. However, when dealing with many ‘OR’ conditions, the query can become complex and hard to read, and there may be a better way to optimize the performance.

Please note this does not apply queries with few OR conditions but rather for the exhaustive amount of OR condition, which eventually fails the optimizer. 

Let’s take a look at an example:

SELECT *
FROM Sales.SalesOrderHeader
WHERE CustomerID = 16529
OR CustomerID = 25249
OR CustomerID = 27668
OR CustomerID = 17524
OR CustomerID = 20489
OR CustomerID = 23563
...

This list can continue, making our query hard to read and potentially slower due to the multiple ‘OR’ conditions. Let’s see how we can optimize this using a TempTable:

CREATE TABLE #TempCustomerIDs (CustomerID int)
INSERT INTO #TempCustomerIDs (CustomerID)
VALUES (16529), (25249), (27668), (17524), (20489), (23563)...
SELECT *
FROM Sales.SalesOrderHeader
JOIN #TempCustomerIDs
ON Sales.SalesOrderHeader.CustomerID = #TempCustomerIDs.CustomerID

In this example, we first create a temporary table and populate it with the CustomerIDs we are interested in. Then, we join this TempTable with our main table. Now, our query is easier to read and more efficient.

The advantages of this approach are:

1. Readability: The query becomes much more readable, which helps with maintenance and debugging.
2. Performance: The SQL Server can often handle joins more efficiently than multiple OR conditions, especially when dealing with large data sets.
3. Flexibility: This approach is more flexible, as we can easily add or remove CustomerIDs from our TempTable.

Just like with the “OR” vs “IN” discussion, this is not a hard and fast rule. Different approaches may yield better results depending on your database’s current state, the available indexes, the table’s size, and the system’s available resources. Always test your queries’ performance and inspect their execution plans.

When I’ve tested with smaller samples, I’ve noticed that the results tend to be unpredictable. Instead of experiencing performance benefits, I view Joins as a more reliable alternative to Joins. Furthermore, in the blog, we haven’t touched upon the cost of populating the temporary table, which is another reason why the Join method isn’t recommended for smaller sets of records.

You can always reach out to me on Twitter.

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