Database views can be a key factor in a successful reporting and analytics engagement. They are both overrated and underrated. They provide great benefits but can be a key source of performance degradation. As with any tool, you use it where it fits best. In managing data access and data provided to the presentation layer—and coupled with reporting stored procedures—they can provide significant value in the data access layer/layer of abstraction that you will need to manage interaction with your data, especially in the cloud.
A database view definition is a SQL query that is presented to the user or process as if it were a table. As straightforward as that. So, why worry about it? As part of your overall strategy and design, they can be critical and very beneficial. For reports, or other queries, accessing a transactional system, operational data store, or data warehouse, a great practice is to always have the presentation layer accessing the data interact with a view or stored procedure to receive its data. You can manage what data is provided in one central location. Why? Because embedding SQL in a report or other consuming process is something to avoid—for manageability, maintenance, and consistency. This is true for on-premises environments and even more so in the cloud, with tools coming and going more quickly than ever. Why recreate the SQL, over and over, when a new consumer accesses the same data? Save it off so it can be readily reused.
Manageability
I don’t create views simply because I can. I usually want to manage data usage and data access. They are tools in the overall design for your application/data repository. Here are some reasons why:
View use cases
- Capture common queries
- Capture reporting queries/provide data to be delivered
- Provide security mechanism
- Manage and maintain code
Capture common queries
- Document SQL in query access patterns to be used.
- Manage part of the business rules used in the system
- Document logical business entities in the data, especially if not intuitive
- Even if not immediately utilized, it can be pre-tested and verified prior to use
Capture reporting queries/Provide data to be delivered
- Avoid nested SQL in your presentation layer. Use database views or stored procedures to provide the information. Make a change to the table structure and a view can hide the change to the consuming report. Change the view, not the report—as long as it’s still receiving the result set it was expecting. If you are under certain regulatory compliance, database queries may also need to be in compliance.
- For data to be exported or fed to a downstream system, create a view(s) to support that function—similar to report access. Create a series of discreate and qualified views that provide that information. Here’s an example I’ve used with a separate database schema, and then a prefix qualifying the usage, such as views like Rpt.WebsiteData_* for all views needing to feed data to the Website.
Provide security mechanism
- View objects can have database-object level permissions assigned to manage permission levels. Better if they live in a separate database schema, where we have an even a higher level of database permission on.
- Bake row-level security into the query, apply tenant ID client filtering, restrict the columns provided, or apply business level permission(s) into a view.
Ability to manage and maintain code
- You can hide the physical layer and complexity from the presentation layer. Let the reporting layer worry about presenting the data and finishing calculations vs. having to create the result set (in addition to everything else).
- A more standard location for business rules or transformations in effect.
- Impact analysis. When changing the database table, you want to know what’s affected. In SQL Server, you can do a view dependency very quickly on the table and see what objects are impacted.
- When data consumers, processes, or reports are replaced, a view gives you one less thing to recreate as it is not in-resident in the consumer.
- Database objects tend to be more durable and have more safeguards (backups, code repositories) to allow it to persist. I might be biased, but how many times have you have SQL in a 3rd party ETL tool or report get corrupted or stepped on inadvertently and had to recreate it? For myself, more than enough times.
- Centrally supported objects (e.g., database objects) tend to support maintenance change by Development teams better, especially new staff members, offshore teams, or shops dealing with keeping the lights on. These typically incur less time and confusion around what data is to be used.
Performance considerations
Generic types of views
- Simple – One to two tables
- Complex – Many table joins/complex queries
So, why do I make this distinction? Performance is always is a balancing act and one must always evaluate, especially with views. The view type will have an impact on the execution plan created by the database engine and will impact how you can use it. Simple views involve a low number of objects being accessed and have a relatively low performance hit. The more complex a query is in the view, the more extensive the execution plan required to access all objects in the query. You have to be careful to avoid performance degradation.
The most common view trap is what I call ‘the view explosion.’ For example, if a query uses five views, and each of those views has five tables as part of the view definition, the optimizer will create an execution plan accessing each object from each view reference and treating it as a subquery. Five views times five tables means we have to access those tables 25 times. Yikes! At this point, it’s better to create an overall query with the tables collapsed into it, or build a materialized view, or use an index on a view, or build a stored procedure as you get near the performance tipping point. The key is to balance your performance. An execution plan will tell you how it is going to access the tables to resolve the query.
A good rule of thumb when using views is: when you create a query, try to use a single complex view. You can get away with using multiple views in the query if the views are simple. If the query is using a complex view, try to use just one view in that query.
Conclusion
The value of database views is that they support data manageability, maintainability, consistency, economy of scale, and security. But you should always keep performance in mind. I will continue to include views as a standard component in the data architecture for the value they provide and will leverage them where they provide the most value (e.g., data access). Architects must make a lot of decisions to best structure your environment and applications to meet business and IT needs. Lastly, if you have any questions around the best way to structure your data architecture for success, please don’t hesitate to reach out to us for the answers. We’d love to help you get started.
Share on
