Sql Server 2008 Nesting Views

Question

Is there a general best practice on whether or not to use nested views? Is there a performance hit when using nested views? Is there a best practice that says there really isn't a performance hit until you go 4 or more layers deep?

The reason I'm asking this is because I'm struggling with whether or not to use them. It is not unusual to get a report request of which the only way I can get access to that information is by joining 20 or more tables together. Fields are not returned from all the tables but are needed to select the correct data. In this case I like nesting the views and reusing the lower level views for other reports because if a change to the logic is needed I just update one view and all reports are updated. Many of the tables I work with contain millions and millions of records.

However perhaps this is not a good practice. Do you mind sharing your thoughts on this?

Answer 1

I would avoid this at all costs. First once you nest views they cannot be indexed. Next, since they have to fully materialize the underlying views to get to the next layer. So you could be materializing multi-millions of records to get an end result of 5 records. We very nearly lost a multimillion dollar client because performance was so abysmal when our devs did this on one database (not a database I had input into the design of).

Finally I have found that these sorts of layers are much, much harder to maintain when you need to make a change. It's no fun to track through 12 layers of views to find the one you need to fix. We also ran into an issue because devs found it easier just to add another layer than fix the underlying layers and then were trying to access too many tables in one query and way too many of those tables were the same multi-million record table being accessed 7 or 8 times in different layers of the views.

There is no circumstance where I would allow more than one layer in a view in a database I manage and I'd be angry if you did that.

Answer 2

Other options to consider: Indexed Views -- Can be dangerous to use if not used correctly but the performance gains can be amazing.

Analytics -- such as grouping sets

procedures & temp tables -- Get the data you need via procedure write it out to temp tables select from temp tables.

Overall I don't like the performance hit of view on view on view or nested views.

Generally you can generate one view using the correct joins between tables which contains all the information your after and filter out the data using criteria.