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

About these ads

18 Responses to The effects UNION in a SQL query

  1. Pingback: SqlServerKudos

  2. Virgil says:

    Interesting article, I have a question :

    If I have a table / view like this :

    id1 , attribute fields line 1
    id1 , attribute fields line 2
    id2 , attribute fields line 3
    id2 , attribute fields line 4

    idn , attribute fields line j
    idn , attribute fields line j+1
    idn , attribute fields line j+2

    what would be the cheapest way to show only one line per id ? And I really don’t care which would be that line, all I care for this is performance…

    Thank you very much!

  3. Pingback: Dew Drop – August 7, 2009 | Alvin Ashcraft's Morning Dew

  4. mesut says:

    I solved some performance problems by using “Union All”. I was lucky because there was no duplicity problems.

    I realized that our all Union queries were just “Union”. So, there was a huge performance tuning potential that our developers didn’t notice before.

  5. Joe Webb says:

    mesut – I’m glad you found this useful; thanks for letting me know.

    I’ve got a couple of more blogs coming soon that focus on improving performance by making relatively minor changes to your code like this. Stay tuned.

    Joe

  6. Joe Webb says:

    Virgil –

    That’s a great question. The best way to determine optimal performance for you unique and specific situation is to develop 2-4 queries and then look at the response times and execution plans.

    HTH…

    Joe

  7. Pingback: Log Buffer #158: a Carnival of the Vanities for DBAs | Pythian Group Blog

  8. shyam says:

    How to write a query without using JOIN Statement.

  9. Pingback: My Most Popular Posts From 2010 « WebbTech Solutions

  10. Praveen says:

    Nice article, Incase if I use ORDERBY with the union all query, will it give me any performance increase. For eg: (select * from table1 where year=2013) UNION (select * from table2 where year=2013) ORDER BY number.

    Hope that I will get response.

  11. Hey just wanted to give you a quick heads up. The words in your content seem
    to be running off the screen in Chrome. I’m not sure if this is a
    format issue or something to do with internet browser compatibility
    but I figured I’d post to let you know. The design
    look great though! Hope you get the problem resolved soon.
    Many thanks

  12. Greetings! Very useful advice within this article!
    It is the little changes that make the largest changes.
    Thanks a lot for sharing!

  13. Rhonda says:

    Hey there, I think your website might be having browser compatibility issues.
    When I look at your blog in Firefox, it looks fine but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that, awesome blog!

  14. Pretty nice post. I just stumbled upon your weblog and wanted to say
    that I’ve truly loved surfing around your blog posts.

    In any case I will be subscribing for your feed and I’m hoping you write once
    more very soon!

  15. call center says:

    I’m not that much of a internet reader to be honest but your sites really nice, keep it up!
    I’ll go ahead and bookmark your site to come
    back later on. All the best

  16. This website was… how do you say it? Relevant!!
    Finally I’ve found something which helped me. Thank you!

  17. Edwardo says:

    Hello there! I could have sworn I’ve been to this website before but after checking through some of the post I realized
    it’s new to me. Anyhow, I’m definitely happy I found it and
    I’ll be bookmarking and checking back often!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers