An interesting concept: Splitting queries with OR for performance

In a recent LinkedIn post, a poster had a novel idea. Say you have a query that has an OR condition. Usually the OR condition in question are related concepts (like this UserId or another one), but sometimes they are quite distinct (like ProductCost is in a range, and ProductColor is in a list of colors, distinct as far as the data is concerned at least.)

Each of two OR Boolean expressions each could use an index, but as an OR expression in the query, they might cause the query to do a complete *table scan because the optimizer doesn’t see what you see about the selectivity of a certain column or value.

*typically a clustered index, but this might be a non-clustered index that has all of the needed columns.

And of course I can’t find that post again.

Download the code from my github repo here.

The challenge

So would it be better to split this into two queries and UNION (or UNION ALL) the results? I admit I was dubious, and not many people pushed back on my response. So as is my typical pattern of posting, I decided to try it. And not to just try it with 2 expressions, but three, and four until it made sense.

Now, first things first, I will NOT dispute this as an interesting, even valid tool to use (even before I start testing it), especially on queries that are repeated often with very similar values. Anything you can do to make your queries faster, well, makes your queries faster. With the obvious caveat that you regularly maks sure your techniques are still working over time.

I mostly questioned if this is a good practice when using an optimizer like SQL Server has that will actually do this for you when it definitely makes sense. And perhaps my biggest concern if you take a query that the optimizer decided to run in one table scan and you turn it into 2 queries, then worst case, you’ve potentially made things worse thank if you had just let the scan happen. Why? Because if one query does a full scan (or both!), you are compounding your issues and just making your performance worse in a way you won’t easily notice. And the work you have to do to make sure this doesn’t occur could be way more costly than the one scan.

So unless your query needs to be executed a lot, and you can be completely sure that you couldn’t end up with worse performance, this could be a dangerous practice. In fact, to me it breaks some fo the fundamental tenets of a relational database to not know too much about the physical implementation…because it can change independently of your query. In fact, IF I as going to implement this pattern, I definitely would consider applying a hint to force the index I am relying on so it would immediately fail (idealling during testing), reminding you to revisit your solution if the underlying stats/indexes changed.

I would also admit that if you are using a very large table where it costs you money to do a table scan, then this might be worth it. But again, let’s see what we find when we actually test it.

The code (aka: evidence)

Of course, in this blog I am goint to give this a try. I am going to start out using AdventureWorks as always and later use a larger dataset. So I created a query (with SQL Prompt AI’s help) using the SalesOrderDetail table that searched for rows with a UnitPrice < 500 OR ProductId = 774:

			
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < 500
   OR  ProductId = 741;

I am using * to get all the rows for simplicity of example, and to make sure that, like normal, we are getting the data for the row. You may not need all the data, but you will usually use more than a column or two and your table will most likely be larger than this table.

When I executed this, I got back 88147 out of 121317 rows in the table. With these numbers, it should be expected that the plan gave me a table scan (technically a clustered index scan).

But what if I executed these as two different queries?

			
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < 400;
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  ProductId = 741;

		

Then, a lot of rows are output in two result sets: 94 for the ProductId, and 88053 for the UnitPrice (which is more than the 88145 we initially got back.)

Now, you can see in the plans, that both use indexes. If you look at the query plan, the UnitPrice query does a table scan, but it suggests an index, so I am going to apply it blindly (it is not a great index as it basically duplicates the entire table, but it will suffice for this example (giving the best chance for this example to succeed as is, and is indicative of an index you might apply on a subset of columns you need regularly):

			
CREATE NONCLUSTERED INDEX index_1
ON [Sales].[SalesOrderDetail] ([UnitPrice])
INCLUDE (
            [CarrierTrackingNumber],
            [OrderQty],
            [ProductID],
            [SpecialOfferID],
            [UnitPriceDiscount],
            [LineTotal],
            [rowguid],
            [ModifiedDate]
        );

		

So then, if we UNION the results (to get rid of duplicates), we should still get index usage.

			
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < 500
UNION
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  ProductId = 741;

		

Look at the query plan, and you will see that indexes are used, and we have an optimal solution… or do we?

The original poster suggested to make sure they was no overlap. So I will change the query to:

			
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < 500
  AND  ProductId <> 741
UNION ALL
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  ProductId = 741;

		

Same number of rows and now it doesn’t need to do the deduplication. (in fact it actually would would be the same with UNION as the optimizer knew there was no overlap)

So is this plan better?

I mean, it does look nice with the two branches executing and then being concatenated. But while this query plan looks nice with the index seeks, are we sure this is actually better than the table scan? A good way to check is to see how many logical reads occur. So using SET STATISTICS IO ON; and run the queries:

			
SET STATISTICS IO ON;
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < 500
   OR  ProductId = 741
SET STATISTICS IO OFF; 
SET STATISTICS IO ON;
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < 500
  AND  ProductId <> 741
UNION ALL
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  ProductId = 741;
SET STATISTICS IO OFF; 

		

Well, it turns out that the table scan is actually better! In fact, the 2-query approach costs about 10% more in logical reads (1360 vs 1241). While this may seem like a small difference, in larger databases with larger tables you can definitely see the impact that this has on performance. The key thing to realize is that the index seek (or scans) are happening on different indexes, while the table scan is going through one path and reading data that might not even be needed.

			
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,...
Table 'SalesOrderDetail'. Scan count 2, logical reads 1360,...

So while this specific example is concerning, is this technique doomed?

Testing different parameter values

Okay, you got me. I did pick an example where this was bound to fail. And the index I added was pretty large in and of itself. I will admit also that when I first started this blog I actually did expect to say “this is 100% a bad idea” because what you are doing is trying to be the optimizer. The fact is, the optimizer can produce a similar sort of plan as you can, when it makes a big difference. And it will often use a table scan that is the most efficient way of executing the query (even if you have heard that the table scan is always the worst!)

But in order to test this technique a bit more, I wrote a procedure that takes a parameter for the two values and by setting recompile on, and this will execute several permutations of the query we are working on to see the different plans that are employed.

			
CREATE OR ALTER PROCEDURE Testharness_TwoIndexedQueries
(
    @ProductId INT,
    @UnitPriceGT MONEY
)
WITH RECOMPILE AS
SET STATISTICS IO ON;
SET NOCOUNT ON;
DECLARE @parameterMessage varchar(60) = 
    CONCAT('ProductId=',@productId, ' and UnitPrice > ', @UnitPriceGT)
PRINT '-----------------------------------------------------------'
PRINT ' Parameters'
PRINT   @parameterMessage
PRINT '-----------------------------------------------------------'
PRINT '-----------------------------------------------------------'
PRINT'All rows - show cost of full table scan'
PRINT '-----------------------------------------------------------'
SELECT *
FROM Sales.SalesOrderDetail
PRINT '-----------------------------------------------------------'
PRINT'ProductId = '
PRINT '-----------------------------------------------------------'
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductId = @productId;
PRINT '-----------------------------------------------------------'
PRINT  'UnitPrice less than '
PRINT '-----------------------------------------------------------'
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < @UnitPriceGT
PRINT '-----------------------------------------------------------'
PRINT 'Simple OR condition'
PRINT '-----------------------------------------------------------'
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  UnitPrice < @UnitPriceGT
   OR  ProductId = @productId
PRINT '-----------------------------------------------------------'
PRINT 'UNION ALL approach'
PRINT '-----------------------------------------------------------'
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  ProductId = @productId   
UNION ALL  
SELECT *
FROM   Sales.SalesOrderDetail
WHERE  (UnitPrice < @UnitPriceGT AND productid <> @productId)
SET STATISTICS IO OFF; 

		

If you execute this procedure with parameter values that match the original query that was tested:

			
EXEC dbo.Testharness_TwoIndexedQueries @ProductId = 741,     -- int
                @UnitPriceGT = 500; -- money

You can see the following output (and if you leave on actual query plans, you can see the plans as well):

			
-----------------------------------------------------------
Parameters
ProductId=741 and UnitPrice > 500.00
-----------------------------------------------------------
-----------------------------------------------------------
All rows - show cost of full table scan
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
ProductId = 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 298, 
-----------------------------------------------------------
UnitPrice less than 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1062,
-----------------------------------------------------------
Simple OR condition
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
UNION ALL approach
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 2, logical reads 1360,

		

This matches what we have seen, that the two queries alone do less work than the table scan, yet added together they do more work (as we saw before). But this isn’t the complete story, because what if the size of the data is less? For example, in this case the ProductId = 943 returns fewer rows than ProductId 741:

			
EXEC dbo.Testharness_TwoIndexedQueries @ProductId = 943, -- int
                @UnitPriceGT = 500 -- money

The output is now:

			
-----------------------------------------------------------
Parameters
ProductId=943 and UnitPrice > 500.00
-----------------------------------------------------------
-----------------------------------------------------------
All rows - show cost of full table scan
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
ProductId = 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 24, 
-----------------------------------------------------------
UnitPrice less than 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1062,
-----------------------------------------------------------
Simple OR condition
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
UNION ALL approach
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 2, logical reads 1086,

		

So since the product Id query uses fewer logical reads, the UNION ALL approach is more efficient than the OR condition. What about when the number of rows gets smaller and smaller? In this case, I will use a fake productId (so there are 0 rows returned) and a much smaller amount of data:

			
EXED dbo.Testharness_TwoIndexedQueries @ProductId = 0, -- int
           @UnitPriceGT = 10 -- money

This is the text output, and you can see in the last step that this has a much better IO:

			
-----------------------------------------------------------
Parameters
ProductId=0 and UnitPrice > 10.00
-----------------------------------------------------------
-----------------------------------------------------------
All rows - show cost of full table scan
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, 
-----------------------------------------------------------
ProductId = 
-----------------------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical r
Table 'SalesOrderDetail'. Scan count 1, logical reads 2
-----------------------------------------------------------
UnitPrice less than 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 270
-----------------------------------------------------------
Simple OR condition
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241, 
-----------------------------------------------------------
UNION ALL approach
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 2, logical reads 272
Table 'Worktable'. Scan count 0, logical reads 0

		

Now you can see the number of rows is really small, the UNION ALL approach is far more efficient than the OR condition. So if you can be sure that you will always have a selective index and an input that returns a relatively small number or rows, UNION ALL is a great idea.

But the story does not stop here, because while you can get more selective values, the opposite is true.

The Danger Zone

The danger zone starts when you have a non-selective index or an input that returns a large number of rows. Let’s test with a non-selective ProductId and a high UnitPrice threshold:

			
EXEC dbo.Testharness_TwoIndexedQueries @ProductId = 870,
                @UnitPriceGT = 10000; -- money

Now you can see:

			
-----------------------------------------------------------
Parameters
ProductId=870 and UnitPrice > 10000.00
-----------------------------------------------------------
-----------------------------------------------------------
All rows - show cost of full table scan
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
ProductId = 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
UnitPrice less than 
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
Simple OR condition
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 1, logical reads 1241,
-----------------------------------------------------------
UNION ALL approach
-----------------------------------------------------------
Table 'SalesOrderDetail'. Scan count 2, logical reads 2482,

		

You have now doubled the logical reads because now the work is done in 2 separate full table scans instead of 1. Whoops.

So if you cant take strong enough control over parameter values, this could definitely a negative for performance rather than the value you were expecting.

Example with Larger Table

In order to get a much larger set of data, I grabbed a copy of StackOverflow’s database from 2013 from Brent Ozar’s site:. The Votes table has 52,928,720 rows which is a pretty good size on my query testing rig.

To simulate a similar style of query as the original post (and the AdventureWorks example), I wrote the following query. I did want to include a very selective query path (in this case, using the primary key column: id) alont with a less selective query path.

Note, for these queries, I turned on the setting to discard results so I wouldn’t have to wait for so many rows output. This will not affect the IO it takes to create the output, just that the client doesn’t stream the output to you.

I also wanted to show that it is very possible to reuse a single index in a query over and over with the id column. I will use the same id values that were picked at random, and then two VoteTypeId values that are on the opposite sides of the range. 733 for 4, and 2,039,371 rows for 10.

			
SELECT *
FROM   dbo.Votes
WHERE  id = 11846083
  OR   id = 28341294
  OR   id = 5086021
  OR   id = 32039430
  OR   VoteTypeId = 4; --733 matching rows

		

The plan for this query is:

You can see all of these paths use an index. One of them does use a Key Lookup to find the rows. You might also notice that there is only one Clustered Index Seek operator, but if you hover over it you can see that 4 rows are found:

And you will see the values from your query as scalar values. Later, when I output the IO stats, this will show up as 4 scans.

Next running the UNION ALL version of this query:

			
SELECT *
FROM   dbo.Votes
WHERE  id = 11846083
  OR   id = 28341294
  OR   id = 5086021
  OR   id = 32039430
UNION ALL
SELECT *
FROM   dbo.Votes
WHERE  VoteTypeId = 4 
  AND NOT (id = 11846083
  OR   id = 28341294
  OR   id = 5086021
  OR   id = 32039430); 

		

You will note that the query plan looks pretty much the same:

Just slightly rewritten. But what if the VoteTypeId is not very selective?

			
SELECT *
FROM   dbo.Votes
WHERE  id = 11846083
  OR   id = 28341294
  OR   id = 5086021
  OR   id = 32039430
  OR   VoteTypeId = 10 --2039371 matching rows  

		

Now this query uses a table scan:

And just like before, when one of the branches is costly, the costs are going to be greater:

			
SELECT *
FROM   dbo.Votes
WHERE  id = 11846083
  OR   id = 28341294
  OR   id = 5086021
  OR   id = 32039430
UNION ALL
SELECT *
FROM   dbo.Votes
WHERE  VoteTypeId = 10 
  AND NOT (id = 11846083
  OR   id = 28341294
  OR   id = 5086021
  OR   id = 32039430);

		

So now the plan is a Clustered Index Scan and the index seeks. Which n this case isn’t a lot more work, but it could be as we saw earlier (and just like before, they are suggesting a really large index that I won’t add this time):

And then in the downloads, there is a script that does what the previous one did with the TestHarness for the different version of this query again. Execute it with the more selective value of 3 for VoteTypeId:

			
EXEC Testharness_TwoNewIndexedQueries
@id1 = 11846083,
@id2 = 28341294,
@id3 = 5086021,
@id4 = 32039430,
@VoteTypeId = 4 --733 matching rows

		

And it does beat the table scan version:

			
-----------------------------------------------------------
ParametersSummary
Parameters ID1:11846083 ID2: 28341294 ID3: 5086021
 ID4: 32039430 voteTypeId: 4
-----------------------------------------------------------
-----------------------------------------------------------
All rows - show cost of full table scan
-----------------------------------------------------------
Table 'Votes'. Scan count 1, logical reads 243698
-----------------------------------------------------------
 Four id = OR condition
-----------------------------------------------------------
Table 'Votes'. Scan count 4, logical reads 16
-----------------------------------------------------------
VoteType = a value
-----------------------------------------------------------
Table 'Votes'. Scan count 1, logical reads 2995
-----------------------------------------------------------
Simple OR condition
-----------------------------------------------------------
Table 'Votes'. Scan count 1, logical reads 3028
-----------------------------------------------------------
UNION ALL approach
-----------------------------------------------------------
Table 'Votes'. Scan count 6, logical reads 3013

		

But not by that much. And if your one query turns into a table scan like with VoteTypeId = 10:

			
EXEC Testharness_TwoNewIndexedQueries
@id1 = 11846083, --same rows
@id2 = 28341294,
@id3 = 5086021,
@id4 = 32039430,
@VoteTypeId = 10 --2039371 matching rows 

		

Well then you will see that it is now worse than the table scan. And in this particular case, there is no worries with the id column query.

			
-----------------------------------------------------------
Parameters
Parameters ID1:11846083 ID2: 28341294 ID3: 5086021
 ID4: 32039430 VoteTypeId: 10
-----------------------------------------------------------
-----------------------------------------------------------
All rows - show cost of full table scan
-----------------------------------------------------------
Table 'Votes'. Scan count 1, logical reads 243698
-----------------------------------------------------------
 Four id = OR condition
-----------------------------------------------------------
Table 'Votes'. Scan count 4, logical reads 16
-----------------------------------------------------------
Votetype = a value
-----------------------------------------------------------
Table 'Votes'. Scan count 13, logical reads 246985
-----------------------------------------------------------
Simple OR condition
-----------------------------------------------------------
Table 'Votes'. Scan count 13, logical reads 246455
-----------------------------------------------------------
UNION ALL approach
-----------------------------------------------------------
Table 'Votes'. Scan count 17, logical reads 246296

		

The Bottom Line

Query tuning is a very complicated process inside the engine, and while trying to squeeze out a bit more performance is never a bad thing, it has a lot of costs. First off, you will burn a lot of cycles trying out these plans to save query processing time. So I probably wouldn’t try this on most any ad-hoc query that I was going to run often.

If this was a query you execute regularly and need to fetch rows based on an OR filter expression, this is a technique you can have in your back pocket that could save you considerable IO, but you need to make very sure that is the case, or you could end up spending more IO and even more CPU.

One response to “An interesting concept: Splitting queries with OR for performance”

happydba

2026-06-24

I’ve fixed queries with horrendous where clauses using this method – think: many multiple sets of combinations of ands and ors, tens of lines long. Execution went from minutes to seconds. It can be a great method 😀

Loading…

Drsql's Database Musings