Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Nested views causing query performance woes....

Just ran into an interesting issue AGAIN. It seems that when you nest views in a query you are opening the door for massive performance problems. I have seen this in the past, but it appears intermittently and apparently only in cases where you are using complex SQL. The problem goes something like this: When you start to use views to solve a particularly hairy SQL problem and you happen to NEST your views (that is, having one view that calls another view) you sometimes see a MASSIVE slowdown in performance. I have seen this in a couple of cases: in one case a report went from 5 minutes up to 1 hour execution time, and in the other case it went from sub-second performance to 4 minutes. In both cases you could solve the problem simply by "decomposing" the nested view and including its full SQL definition inside the calling query. Just doing this alone would take the query back to its quick performance. Upon analysis of the query execution plans, it seems that the SQL Query Optimizer is getting confused and it returns the FULL data set of the view internally, only to filter out most of the data later...clearly a very bad optimization decision. However, it looks like the nested views "blind" the optimizer to the better choices available to it. In the latest case I saw, for example, there was a view which joined 2 tables with 850,000 rows apiece and then also called another view inside this view. The SP which called the outer view was joining the results of the 850,000 row view to a 1,500 row table. However, the Query Optimizer was pulling in all 850,000 rows first, doing some 3 or 4 query operations on it, and then finally filtering the rowset down to 1,500 rows afterwards. When I decomposed the view as described above the Optimizer went back to its old self and was able to filter the rowset down in the initial operator instead of pulling down all 850,000 rows. Predictably, performance improved by several orders of magnitude. I have no idea why the Query Optimizer does this, but would like to see if anyone, especially from Microsoft, has any insight on this and if this problem is going to be fixed in the future. I seem to remember that for SQL 2005 there was a feature called "Statistics on Views" that was later dropped and never made it into RTM. I don't believe it made it into SQL 2008 either. I wonder if this would fix my issue above..?

Comments

Posted by Anonymous on 27 April 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Nested views causing query performance woes.... - Sam Bendayan's 2 Cents         [sqlservercentral.com]        on Topsy.com

Posted by Megistal on 14 June 2010

Hello Sam,

I've also notice that kind of behavior. When replacing views with base table the optimizer was clearly able to fetch only the needed rows and avoid large table scan.

This is a serious issue for use because views are used for maintaining complex query and avoid duplicating them through out the database. However the performance somewhat degrade sometimes because of that.

The only few workaround were (apart putting back base tables) indexing or rework the query for changing the plan (does not always work). Query hint are almost never an option so far.

Other than that, hasn't find any workaround so far to prevent this.

Leave a Comment

Please register or log in to leave a comment.