The effects UNION in a SQL query

In a recent blog, I shared some of the natural but sometimes unanticipated consequences of adding DISTINCT to a SELECT statement. Adding DISTINCT ensures that you don’t have duplicated rows in your result set, but that assurance comes at a price. Performance is typically impacted negatively when you add DISTINCT to a query. You can read the blog and see the proof here.

But there is another operator that can have some not-so-obvious side affects. Let’s a have look how UNION can affect a query’s performance.

A brief review

The UNION operator combines the results from two completely separate queries into a single result set. For example, let’s say you want to retrieve all customers and all employees in a single list. You could individually issue two separate queries and then combine the results in the presentation layer or in Microsoft Excel to produce your list.

But you can also let SQL Server do the work for you. For example:

SELECT FirstName, LastName
FROM DimCustomer
UNION
SELECT FirstName, LastName
FROM DimEmployee

There are a couple of requirements for using the UNION operator. Each of the two queries that are combined using the UNION operator must have the same number of columns. Furthermore, the columns must be of compatible datatypes. You can read more about the UNION operator on MSDN.

Running with UNION

So, how can this be bad? It’s just running two queries and concatenating the results, right? Let’s have a look at the query execution plan to see what’s going on behind the scenes.

As you can see from the query and execution plan in the graphic above, each of the two queries are executed separately and then concatenated together into one result set. But notice that there’s another operation before results are returned; there’s a Distinct Sort operation which accounts for 61% of the overall cost of the query. What’s that doing in there?

When you use UNION to combine the results from two queries, SQL Server ensures that the combined result set does not contain any duplicated rows. Or put another way, if one of the rows in the first query is identical to a row produced in the second query, only one of the two rows will be returned in the results.

This sounds a bit confusing or at least wordy. So let’s consider our example again. If an employee had purchased something from our company, that person would be an employee and a customer. So each of the two queries in our example would return a row for that person. Using UNION, however, that person would only be listed once in the combined result set. UNION returns distinct rows.

Does this sound familiar? It should; this is essentially the same thing that the DISTINCT keyword does in a SELECT statement.

Before moving on, let’s look at the subtree cost for the entire query. The following graphic shows a subtree cost of 2.01387.

Running with UNION ALL

What if we don’t care about duplicates in our result set? What if we know that none of our employees would ever buy one of our products? Or, what if we are going to handle the duplicates in the presentation layer? Can we optimize the query so that the Distinct Sort doesn’t occur?

Sure. Using UNION ALL rather than UNION tells SQL Server to skip the Distinct Sort step.

By changing the query ever so slightly, using UNION ALL rather than UNION, we tell SQL Server that we don’t mind if there are duplicates in the result set and that it’s ok to skip the Distinct Sort.

What does this do to the over all cost of the query? Let’s look at the new subtree cost for the SELECT statement.

The overall cost decreased to 0.77873 from 2.01387; that’s an appreciable amount, particularly if this query is frequently run throughout the day.

Do you regularly use UNION in your queries? If so, I’d love to hear your experiences with UNION vs UNION ALL and how it’s affect performance.

Joe