Slow running UNION ALL query
-
Hello,
I have a slow running view that unions 8 different sub-queries using UNION ALL.
Each of the 8 queries runs in <3 seconds. The view takes over an hour.
Some tips on how to fix/investigate this would be really useful.
Some info about this:
I am using WITH(NOLOCK) in each of the 8 queries.
Although there are 8 queries I am only actually accessing 3 base data tables (plus 1 lookup table that is referenced in each of the 8 queries). So I am UNIONing table with themselves hence I use WITH(NOLOCK) - not sure if it makes any difference or not.
2 of my queries are a little bit complicated in that they are in fact INNER JOINs of 2 of the other 6 queries (and all are different 2*2=4 queriesthat are being reused in my view). I'm pretty sure these are causing the problems because if I take them out a SELECT * FROM MyView takes 14 seconds. I was thinking that the queries that are JOINed should be broked out into seperate views so that I only have to write the SQL for those views once and SQL Server should be able to reuse the execution plans.
Hopefully that all makes sense...especially the last bit cos this seems to be where my problems lie.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I really should do more investigation before I post
It turns out that 1 of the 2 queries that I thought might be causing the problem actually IS causing the problem. Its doing a LEFT OUTER JOIN between a query that returns 78000 rows (on the left) with a query that returns 60000 rows.
I'm still looking into how to fix it...
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
For what it's worth (but you already know that) the amount of data returned by each statement in the view (and also size of intermediate workingsets) plays a large role on performance.
/Kenneth
-
Sure, thanks Kenneth.
It looks like I have got to the bottom of it now anyway. I have managed to tune the query that was causing all the problems and my view now returns all data in something like 12 seconds which is more than acceptable.
Thanks for the reply.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply