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..?