Re: Slow Union Query Opimization. Need help.

[Anthony Willard]

My first observation is that you are not grouping by all non-aggregrated columns. I've found this to be a troublesome practice - include all non-aggregated columns in your GROUP BY clause. This may be causing you some performance hit(s).

As for performance with large data, do you really need 5000+ rows on a query? Maybe you should limit your selection to a smaller set and then you could avoid the "large query" issues.

I'm not sure if derived tables actually get proper optimizer treatment with regard to indexes. You should try putting your data into a temporary table and then build an index or two that can aid with your final output. Divide and conquer.

Lastly, the larger the data fields you return, the larger the impact for "temporary" operations. For instance, if you are returning 10 fields that are 50 bytes each (ie char(50)), that's 500 bytes per row. Multiply that by 5000 rows and you get 2.5 million bytes! And that's just the data, not including the overhead. That's a lot of data to shuffle around.

HTH.

Anthony