Is a
select * from myView
faster than the query itself to create the view (in order to have the same resultSet):
select * from ([query to create same resultSet as myView])
?
It’s not totally clear to me if the view uses some sort of caching making it faster compared to a simple query.
Advertisement
Answer
Yes, views can have a clustered index assigned and, when they do, they’ll store temporary results that can speed up resulting queries.
Microsoft’s own documentation makes it very clear that Views can improve performance.
First, most views that people create are simple views and do not use this feature, and are therefore no different to querying the base tables directly. Simple views are expanded in place and so do not directly contribute to performance improvements – that much is true. However, indexed views can dramatically improve performance.
Let me go directly to the documentation:
After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.
Second, these indexed views can work even when they are not directly referenced by another query as the optimizer will use them in place of a table reference when appropriate.
Again, the documentation:
The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.
This documentation, as well as charts demonstrating performance improvements, can be found here.
Update 2: the answer has been criticized on the basis that it is the “index” that provides the performance advantage, not the “View.” However, this is easily refuted.
Let us say that we are a software company in a small country; I’ll use Lithuania as an example. We sell software worldwide and keep our records in a SQL Server database. We’re very successful and so, in a few years, we have 1,000,000+ records. However, we often need to report sales for tax purposes and we find that we’ve only sold 100 copies of our software in our home country. By creating an indexed view of just the Lithuanian records, we get to keep the records we need in an indexed cache as described in the MS documentation. When we run our reports for Lithuanian sales in 2008, our query will search through an index with a depth of just 7 (Log2(100) with some unused leaves). If we were to do the same without the VIEW and just relying on an index into the table, we’d have to traverse an index tree with a search depth of 21!
Clearly, the View itself would provide us with a performance advantage (3x) over the simple use of the index alone. I’ve tried to use a real-world example but you’ll note that a simple list of Lithuanian sales would give us an even greater advantage.
Note that I’m just using a straight b-tree for my example. While I’m fairly certain that SQL Server uses some variant of a b-tree, I don’t know the details. Nonetheless, the point holds.
Update 3: The question has come up about whether an Indexed View just uses an index placed on the underlying table. That is, to paraphrase: “an indexed view is just the equivalent of a standard index and it offers nothing new or unique to a view.” If this was true, of course, then the above analysis would be incorrect! Let me provide a quote from the Microsoft documentation that demonstrate why I think this criticism is not valid or true:
Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes.
Together with the above quote regarding the persistence of data in physical storage and other information in the documentation about how indices are created on Views, I think it is safe to say that an Indexed View is not just a cached SQL Select that happens to use an index defined on the main table. Thus, I continue to stand by this answer.