What Are Your Nested Views Doing?

Nested views--views that are called by another view--invariably start off with the best of intentions. Theoretically, there's nothing wrong with using a nested view as long as you're aware of the performance implications. Theoretically, the optimizer eliminates the intermediate abstraction of a nested view, ending up with the same query execution plan it would have devised if nested views hadn't been involved. But in practice, nested views create more problems than they solve.

Let's look at example of how a nested view is born. Joe, a well-meaning developer, creates a perfectly good view that encapsulates a complex business rule--CustomerRule123. Joe's view, ShowCustomerInformation, joins three tables. A few weeks later, Sue sees the view and thinks, "Perfect. That's almost the exact data set I need for a report. I'll use ShowCustomerInformation as my base schema and simply add the other two joins I need." Sue thinks she can recreate the entire join in a new, top-level view. And she assumes that encapsulating the first view is beneficial in that it lets her report automatically pick up any changes to the logic in CustomerRule123. No problems, so far.

Alas, a few months later, someone else creates a new view, BeatsTheHeckOutOfMeData, that's now the first-level view, and Joe's original view, ShowCustomerInformation, is now buried five views down. This new view returns a result set that answers a simple query. However, if you print all the code that each nested view contains, you'll get 8 pages of SQL, and you'll see that the tangle of nested views joins multiple instances of tables together in ways that yield a horrible execution plan. This monster view takes 8 seconds to run.

Careful analysis might show that you could write the query based on BeatsTheHeckOutOfMeData as a clean four-way join that eliminates several messy and expensive rules--and returns in 250ms. But because of developer turnover during the past few months, nobody knows what BeatsTheHeckOutOfMeData is doing. The developers know only that the view returns the data users need. No one has time to detangle the messy web of nested views, and no one wants to change the inner views and risk breaking something somewhere else in the application. So, the monolithic monstrosity called BeatsTheHeckOutOfMeData lives on.

This example might seem extreme, but I often see similar situations at customer sites that regularly use nested views. When I'm called into help solve performance problems at such sites, I dig into my bag of tricks to find the queries that are consuming the most resources. I then run across an innocuous-looking query such as

   SELECT CustomerId, PlusABunchOfOtherColumns   FROM BeatsTheHeckOutOfMeData   WHERE Id  @TargetId

and I think to myself, "That looks like an easy enough query to tune." Imagine my disappointment when I understand the mess I've gotten myself into. My heart sinks even further when I ask what the nested view does and the developer I'm working with says, "Beats the heck out of me."

Views are an incredibly powerful tool in the database world when they're in the right hands. And nested views aren't inherently evil provided you consider the implications of the scenario I've outlined for you here. I'd rather avoid the problems caused by granting developers the right to directly create nested views. Trained DBAs and architects who can weigh the pros and cons of using a deeply nested view in a controlled manner might use them wisely. But I prefer to make nested views off-limits to the core development team for projects I have architectural responsibility over.