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]