A colleague introduced the term "convenience view" to me and that term resonated with me ever since. A convenience view is one of those database objects intended to make life easier for people to access data without actually understanding the nuances and relationships of those data. Convenience views frequently join multiple tables together so data users will not need to code or optimize those joins. Convenience views may also include business logic which transforms data so end users will not need to code or argue those transformations. The concept seems noble enough. Who opposes simple data access, optimized joins and centralized business rules? Just like that store that sells everything right off the interstate, convenience comes at a cost.
One obvious cost is all of those optimized joins. Sure each individual join may be optimized, but the database engine has to collectively consider all available join options before an execution plan is chosen. I have seen a convenience view with literally dozens of table joins. When the optimizer reused a current execution plan, query performance was tolerable. When a cached execution plan was not available, the optimizer would spend many valuable seconds just formulating a plan even if the user only queries a small portion of the conveniently available data. Yes, we can argue an optimizer typically makes decisions faster than a data analyst types queries, but are those the two best options in our query cost equation? What DBA ever heard the complaint, "My query sometimes performs just fine, but other times my query runs forever"? How many data analysts ever heard, "The DBA cannot replicate your problem"? How many developers ever said, "No problem, I can fix that"?
The frequency of those statements concerns me more than query costs. The greatest risk from convenience views is masking the very data the views are intended to make accessible. The DBA looks at the execution plan and decides the plan looks acceptable. The data analyst thinks centralizing optimized table joins and business rules belongs in the database. The developer believes she can fix the problem without adversely impacting the project timeline. I support each of those individual's assessments.
Until another DBA, data analyst and developer on another project solves their own set of problems for the same underlying data.
I have seen OLTP environments with daisy-chained views many layers deep under the guise that the business logic is modularized. I have seen data warehouse environments with views built on views so no one can explain the lineage of the data being analyzed. Too frequently, not until the business is significantly impacted will technology and business leaders start to act.
Data utilization is impeded because queries perform poorly. Business initiatives are not embraced because no one can explain the risks associated with the very data access paths proudly touted earlier.
Does this progression sound familiar to anyone?
Of course, I still take interstate exits on some of my road trips. That convenience store at the bottom of the exit ramp provides the gasoline, snacks and caffeine I need right then. Sure I may arrive at my destination a few minutes later because I stopped and I might spend a few extra dollars at that store, but I could not beat the convenience of a store that was so close to my path.
Unless I filled up my gas tank before I left. Maybe I grabbed a healthy snack from home. Perhaps I even got plenty of rest the night before I set out so I did not need that caffeine shot. Perhaps those scenarios are called planning. Understanding and documenting the requirements as well as the design of a database solution are others ways of planning that allow us to recognize and avoid the long term costs of convenience.
There is a place for convenience in both road trips and database projects. The key to successfully navigating each is honestly understanding the costs of convenience and planning accordingly before we simply lapse into a convenient path from an immediate position.