slow run on query when is joined with another view

robertdsgt 2014-05-01 18:21:53

Hi if any one can help.
When I run this query it takes 37 s to execute,
When I join it with another view it takes 11 mins to execute.
But the other view only takes 3 s to execute alone.
Would can be the issue?
Thanks in advance

Jonathan Kehayias 2014-05-01 18:26:52
What are the view definitions in DDL and what is the execution plan for the views joined together? It is not uncommon for nested views to cause performance problems when joined together, even when separately they don't due to the view expansion and how the optimizer decides to rewrite the JOIN orders during optimization. We'd need a lot more information to be able to help much further here.
robertdsgt 2014-05-01 18:36:58
Thanks Jonathan, will do.
btw; very good presentation in Florida (sqldev), I have learned alot. Thanks
Kevin 2014-05-02 19:13:09
I'd like to see the query of the TWO nested views together. Because the Join Diagram tab, for this one plan is just horrifying. =^D

The thing to keep in mind is that if it takes 3s to execute one operation against your second view, joining that operation to another view isn't additive, it's MULTIPLICATIVE.

So depending on where in the 1st view you join to the 2nd, you might need to multiply the 3s of the 2nd view by the number of estimated rows for the join operation in the 1st view. Most of those operations estimate 5k to 6k rows.

Is there any chance you can create intermediate temp tables rather that doing this in a single huge, multipart view?