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.
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
Ability to manage and maintain code
Generic types of views
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.
Business Intelligence Architect
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie | Duration | Description |
---|---|---|
cookielawinfo-checbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.