A few jobs ago, I was taking over as the DBA at a local real estate finance company, and it was my first dedicated DBA position. Prior to that, I had done a combination of application development and database programming. Well, not long after I started, we had one of our developers, who also did some database programming, quit and I had to sit down with him for a “transfer of knowledge”.
What I quickly learned was that this developer did not have a grasp on how our MSSQL database server worked. Looking at his code, I began to see a number of nested views in his views called by the application, and I asked him why he used those. He told me “views are pre-compiled in the database, so that makes them run faster”, all while the views took 15-45 seconds to pull back their data with nesting as many as four levels deep. The truth is that when you use nested views in 90% of databases, each view needs to execute independently, and only once a nested view has retrieved it's data can the parent view begin processing. However, when I tried to correct this developer, who was about 20 years my elder, he dismissed me as a young programmer who didn't know what he was talking about. Instead of arguing with a guy I probably wouldn't see again after the end of the week, I just let it go and happily went about rewriting his views after he left and getting their execution time to be a fraction of what they had been.
The point of this isn't to brag though, but to educate those who will listen. Nesting views can be a bad idea for a number of reasons:
- As mentioned above, nested views execute in a cascaded fashion. If you have a large view and nest it in another view to filter it's contents down, it will always take at least as long as the initial view to execute. This is because the parent view doesn't know what data it's working with until the nested view has returned the data.
- Nested views often are written from an Object Oriented perspective, trying to have a base object that can be used in a number of other parent views. However, this often means that the nested view is pulling information that the parent view doesn't need because it is trying to accommodate a number of similar but differing parent views, which just chews up processing time.
- While using nested views often cleans up the appearance of the code in the view, it makes them more difficult to read because each nested view needs to be opened and evaluated to find the source of the data.
- Using nested views can become a slippery slope when code changes need to be made as well. Every change you make to a nested view will impact every parent view involved.
While not every database handles nested views in this manner (some sophisticated databases will create optimized execution plans for the view basically just using your code as a guide), the majority of them work this way. And even in the cases that don't work this way, we still have to worry about points 3 & 4, and how changes will impact parent views.
In general, your best bet is to not use nested views, as they are a bad database practice that have consequences that most developers and DBAs do not foresee. Even if it means you have to make the same update to code in multiple views, at least you know exactly what you're impacting with that change.