Using a UNION or UNION ALL on two select statements makes them incredibly slower

Question

I have two queries, let's call them Query A and Query B.

Both of these queries run in under a second for the scenario I'm testing and Query A returns 1 result, and Query B returns 0 results.

If I union (or union all) these two queries, it takes over a minute to return the (expected) 1 result.

Both queries select the same columns from the same tables. I could potentially rewrite this entire thing without a union by having a highly conditional where clause but I was trying to get away from doing that.

Any ideas? I'm not sure how much of the exact query and schema I can get away with sharing, but I'm happy to provide what I can.

This is on MSSQL 2008 if it matters to anyone's response.

Answer 1

I would try looking at the execution plans within Management Studio for the individual queries, and then compare that to the execution plan for the query containing the UNION.

If there's that drastic of a difference in the execution times, I would imagine that there's something wrong with the execution plan for the UNION'd query. Identifying what's different will help point you (and maybe us) in the right direction on what the underlying problem is.

Answer 2

The separate clauses in a UNION that are very similar and on the same tables can be merged into one query by the optimiser. You can see this by the lack on UNION operator in the query plan. I've seen similar things before but rarely

What you can do is a SELECT.. INTO #temp... for the first query followed by an INSERT #temp... for the second

Now, where did I read this...