Automatic DBA

OK, I have a serious pet peeve: Views calling views. Who the heck started that? Why would we bury simple code so deep that you can’t immediately see an issue? How can you properly optimize the thing?!???

I’ve had developers give me quite a few reasons why they stack views (views calling views). The reasons generally fall into these categories:

• It reduces the code we have to write.
• Easier to maintain.
• Provides a layer of abstraction.

Views are not an arbitrary feature that databases have; they serve a purpose. Views exist to:

• Simplify security.
• Simplify coding.
• Simplify presentation.

The key word here is SIMPLIFY….

sim·pli·fy (sîm′plê-fì) tr.v. sim·pli·fied, sim·pli·fy·ing, sim·pli·fies

To make simple or simpler, as:

a. To reduce in complexity or extent.

b. To reduce to fundamental parts.

c. To make easier to understand.

So, in what way is an architecture where views are calling views simplifying?

Reasons why stacking views are misguided:

SQL is not OO. Having stacks of views does not make the code more efficient or faster. Any measurement thus would be coincidental.

• Views are compiled base on position. Changing a referenced view could possibly affect referencing views. To prevent this you have to refresh the views in the order of referencing.
• Dependency verification is not straightforward. You might not see all the dependencies using the standard system views that detail dependencies.
• Code obfuscation: Views abstract complexity (i.e., simplification), so when you use an abstraction to create another abstraction you start to lose sight of what is really going on (i.e., add complexity).
• Duplicate references: Once the complexity is hidden in the code, references to the same table or column are used multiple times without it being readily acknowledgeable.
• It is difficult to properly optimize a view embedded in another view; ultimately you end up trying to optimize multiple views w/o understanding the effect on other referencing objects.

This is by no means an exhaustive list and each situation is a little different, but in general this covers most of the issues. Views, as with any feature, should not be abused. Just because something is possible doesn’t make it a good idea.