Performance Tuning for Views

While views are often convenient to use, especially for restricting users from seeing data they should not see, they aren’t always good for performance. So if database performance is your goal, avoid using views (SQL Server 2000/2005 Indexed Views are another story).

Views can slow down queries for several different reasons. For example, let’s look at these two SELECT statements:

SELECT * FROM table_name

SELECT * FROM view_name

Which is faster? If you test it, you will find that the first SELECT statement is faster, although the execution plan for both of them will be the same. How can that be? This is because it takes SQL Server extra work (such as looking up data in the system tables) before it can execute the view. This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don’t, because some of the work SQL Server is doing is hidden.

Another way views can hurt performance is when JOINs or UNIONs are used, and you don’t intend to use all of the columns. This results in SQL Server performing unnecessary work (such as an unnecessary JOIN or UNION), slowing down the performance.

Views, like stored procedures, once they are run the first time, are optimized and their execution plan is stored in cache in case they need to be reused. But this is not reason enough to use a view.

Views, besides hurting performance, are not all that flexible when you are working with them. For example, they can’t be changed on the fly, they can’t be used to sort data, and using them for INSERTs, UPDATEs and DELETEs is problematic. In addition, while views can be nested, this just compounds their problems, so avoid doing this.

Instead of using views, use stored procedures instead. They are much more flexible and they offer better performance. [7.0, 2000, 2005] Updated 7-10-2006




Related Articles :

  • No Related Articles Found
Uncategorized

One Response to “Performance Tuning for Views”

  1. It is not quite correct… They can sort data by using top n in select list. They are also very usefull to replace complex joins (imaging that you need to type join of 5 tables each time you need to analyze data). As for the performance difference, database engine has to look at the metadata to expand definition of view. Do you think that it takes lot of time to do it? Can you give some example to support what you are claiming?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |