This blogpost continues my series of blogposts in the field of Microsoft SQL Server performance optimization. Last time we learned, how to build the best performing composite indexes from logical hierarchies. In this blogpost, I will show you how in T-SQL world, OR vs UNION ALL is not always what it seems. You may be surprised!
Setting the scene
In my last blogpost, I had created a Fact table (F_DATABASE_STATISTICS) and a referred dimension table (D_DATABASE) in a star schema on SQL Server 2022. For this exercise, I have set max degree of parallelism to 6 and cost threshold for parallelism as low as 5.
From our earlier exercise, in addition to the clustered identity id primary key, we have following indexes in the D_DATABASE table:
And the column cardinality is as follows:
As a reminder, here’s the composite index we have created to boost our T-SQL queries:
Querying specific column value with OR vs UNION ALL
Let’s assume that we need to select some random statistics from this table based on domain, server and instance grouped by database level to get most out from our composite index. Let’s also assume that we are querying a specific domain, server, and instance, because we want the most efficient index seek. We can write this T-SQL query in two relevant ways:
- Using OR operator
- Using UNION ALL clause
Let’s try both approaches, because we want to learn which one is performing better against the B-tree composite index - or does it even count?
Next, we’ll write the T-SQL queries to be compared as follows and execute them in query analyzer with actual query plan included:
Here are the results:
And here are the CPU time and execution time statistics:
Query with OR:
Query with UNION ALL:
As you can see, T-SQL statement with OR operator is performing 4 percent units faster than the statements with UNION ALL clause. This is because, as anticipated, with OR operator, we need to seek the composite index only once, but with UNION ALL we need to seek the composite index twice in parallel: This consumes just a little bit more time and resources than a single statement with OR operator. Hang on to your chair, things are getting interesting from now on!
Querying specific column list with OR vs UNION ALL
Let’s assume that we need to get a specific list of domains servers, and several instances (64 in total) within, still trying to hit the index seek. We’ll write the code and execute it in query analyzer:
OR query:
UNION ALL query:
And the results are:
As you can see, now our UNION ALL query cost is 20 times less than OR operator query. Why so? The answer is because if we are querying a list of items with OR operator, we need to apply CPU-expensive hash match and sort operations, wherein the thread parallelization in UNION ALL starts to pay off because we do not need that hash match sort but instead, just concatenate operator. The other bad thing in the OR query plan is that because the stream aggregates need the data rows being ordered at first, when the data isn’t in order of the index, it needs to be sorted. A combination of a hash match and sort is also a memory hog.
The query execution time itself is not much faster, but the difference in CPU (as well as memory) usage is more concrete. As can be seen - UNION ALL query barely uses the CPU. The more there are column values in the WHERE clause IN list for a composite index, the bigger the difference in resource consumption will be. You should start seeing results already just with a few values in the WHERE IN -list.
CPU time and execution time for the queries:
Excerpt from OR query CPU footprint with 500 iterations:
OR query execution time for 500 iterations in 28 seconds:
Excerpt from UNION ALL query CPU footprint with 500 iterations:
UNION ALL query execution time for 500 iterations in 21 seconds:
As can be seen, with OR query, we are having somewhat the same activity level on CPU. Total query execution time is a bit slower with OR query.
Querying specific column list with multiple OR vs UNION ALL
What if we quadruple ORs? What happens to the query performance (and plan) in such case? It is quite a challenge for the query optimizer in SQL Server. Here’s the (shortened) OR query:
Query execution time and especially the CPU time both explode at this part (best out of 5 executions):
Here’s the actual execution plan. Does not look good:
Here we can see a filter operator. It can be such a CPU intensive operator! We don’t want to typically see the filter operator in our query plans.
In contrary, let’s run exactly logically same query with UNION ALL structure:
CPU time 0 ms, query execution time 106 ms. Well that was fast! (best out of 5 executions)
Here we can see the power of parallelization in query execution plan:
Now let’s compare the actual query plans:
The judgement is clear: The OR query seems to consume about 99 x more resources than UNION ALL query! For a curiosity, let’s see what happens to the CPU with these two queries.
Excerpt from OR query CPU footprint with 50 iterations:
OR query duration for 50 iterations was 59 seconds:
UNION ALL Excerpt from OR query CPU footprint with 50 iterations (what a difference!):
UNION ALL query execution time for 50 iterations has dropped by ~30 x into 2 seconds:
As can be seen, the OR query seems to spread the query for a multitude of threads and consume dramatically more CPU than UNION ALL query. I’d assume that the Filter operator is splitting the OR query’s WHERE clause into 8 different threads, one for each OR part of the query.
Here are the average results for comparing distinct queries (apples to apples):
Lessons learned
Today we learned that OR vs UNION ALL performance is not always what it seems: it depends. UNION ALL can make a huge difference compared to OR operator in query performance and overall server resource usage. With this said, it is still good to acknowledge that too many simultaneous UNION ALL -clauses may produce context switching and CX_PACKET waits on a larger scale, so use it wisely.
The theory is only half of the meal served. Stay curious and pay attention to details. In the realm of SQL Server, never believe it before you have seen it.
Interested in SQL Server performance optimization? Read more about SQL Governor software and contact us!
Jani K. Savolainen
CEO & Chairman,
SQL Governor
