Is a View Faster Than a Simple Query

Is a view faster than a simple query?

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.

Is querying over a view slower than executing SQL directly?

From MSDN:
View resolution

When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

There should not be any different performance. Views helps you organize, not any performance enhancement. Unless you are using indexed views.

Only the definition of a nonindexed view is stored, not the rows of the view. The query optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.

Is a MySQL view faster than a normal query?

No, a view is simply a stored text query. You can apply WHERE and ORDER against it, the execution plan will be calculated with those clauses taken into consideration.

When to use a View instead of a Table?

Oh there are many differences you will need to consider

Views for selection:

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. Views can model complex joins easily.
3. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
4. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
5. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.

Views for insertion/updates:

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view

Drawbacks

1. You lose information about relations (primary keys, foreign keys)
2. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you

View takes longer than query

You can speed up both by replacing the aggregation and join with a window function:

select t.id, t.rev, t.contents
from (select t.id, t.rev, t.contents, MAX(rev) over (partition by id) as maxrev
      from MyTable t
     ) t
where t.rev = t.maxrev;

The reason for the difference in performance between the view and the query is probably due to compilation. The view is compiled (and optimized) the first time it is run. Since then, the data may have changed or the way you are using it may have changed. You can recompile the view if the data has changed for a better execution plan.

Performance of Tables vs. Views

Assuming the question is about non-materialized views -- Really depends on the query that the view is based on, and what is being done to it. Sometimes, the predicates can be pushed into the view query by the optimizer. If not, then it wouldn't be as good as against the table itself. Views are built on top of tables -- why would you expect that the performance would be better?

Layering views, where you build one view on top of another, is a bad practice because you won't know about issues until run time. It's also less of a chance that predicate pushing will occur with layered views.

Views can also be updateable -- they aren't a reliable means to restricting access to resources if someone has INSERT/UPDATE/DELETE privileges on the underlying tables.

Materialized views are as good as tables, but are notoriously restrictive in what they support.

Why is the != operator faster than the = operator?

The difference in the first step is understandable : = allow SQLite to look up the rows for the given value (SEARCH) while != requires it to go through all the values (SCAN)

The question is : Why does it create a temporary (AUTOMATIC) index (which happen to be very effective) in the first case, not in the second ?

I think it's because, without additional informations, the query planner assumes that an = clause will yield fewer matches than a !=, and the cost of building a temporary index is not justified.

The solution is to let SQLite gather information with ANALYZE. The query plan becomes :

QUERY PLAN
|--SEARCH TABLE main USING COVERING INDEX idx_main (category=?)
`--SEARCH TABLE side USING AUTOMATIC COVERING INDEX (id_main=?)

Another solution is create the required index, so that SQLite doesn't have to build it.

CREATE INDEX idx_side2 ON side (id_main);

With the following query plan (even without analyse) :

QUERY PLAN
|--SEARCH TABLE main USING COVERING INDEX idx_main (category=?)
`--SEARCH TABLE side USING COVERING INDEX idx_side2 (id_main=?)

---

Related Topics