- Introduction
- Optimizing Loops and Cursors
- Temporary Tables and Table Variables
- Performance Issues with Views
- The Effect of Triggers
- Query Hints and Options
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Performance Issues with Views
Views can be used for a variety of purposes, some of which are less than justified for improving performance. Views present a fine way to limit the output of a query horizontally, by returning a subset of rows from a table with a WHERE clause, or vertically, by returning a subset of columns by specifying only those columns in the SELECT statement. Using views for such purposes can help optimize your queries.
Views can also be an excellent way to hide the complexity of your data model—if you have ten joins within a view, it might make writing some of your queries easier. However, when it comes to performance, views with multiple joins don't have any advantage over writing such joins in an ad hoc query. Therefore, if you join two tables to a view containing ten other joins, your performance won't be any better than if you had written a single SELECT statement with twelve joins. In fact, some developers like building views on top of other views that are built on top of other views, etc. Although it might seem that such development makes sense due to the reuse of code, it certainly can and in most cases will hurt the performance of your queries. My advice is to avoid using views that contain multiple joins on large tables and never build a view that involves a SELECT from another view.