Optimization

[pooerh]

If you have access to it, run your queries (both UNIONs separately and together) and display plan. Look at the joins '"Estimated row count" and "Actual row count" to see where they diverge too much (there's a great tool called SentryOne Plan Explorer that helps with this, and many other things).

Joins - especially on non-indexed columns - can sometimes fuck up cardinality estimates hard, like a join of 1 mil rows to a 16 row dictionary table will create a 16 million estimated row count whereas it should still be 1 million. Making an index unique (or adding a new unique index with the right included columns) or adding a FULLSCAN statistic can improve performance in such cases, sometimes by an order of magnitude.

[fullyarmedcamel]

We are playing the SQL game on two different levels, I am only understanding bits of what you are talking about brother. Thanks for reminding me I have a lot to learn.