SQL 视图是否比简单查询更快？

Is a view faster than a simple query?

提问by JohnIdol

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.

与简单查询相比，视图是否使用某种缓存使其更快，我并不完全清楚。

回答by Mark Brittingham

Yes, views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries.

是的，视图可以分配一个聚集索引，当它们这样做时，它们将存储可以加速结果查询的临时结果。

Update: At least three people have voted me down on this one. With all due respect, I think that they are just wrong; Microsoft's own documentation makes it very clear that Views can improve performance.

更新：至少有三个人对我投了反对票。恕我直言，我认为他们错了；Microsoft 自己的文档非常清楚地表明 Views 可以提高性能。

First, simple views are expanded in place and so do not directly contribute to performance improvements - that much is true. However,indexed views can dramaticallyimprove 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 queryas 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.

更新 2：答案受到批评，理由是提供性能优势的是“索引”，而不是“视图”。然而，这很容易被驳倒。

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!

假设我们是一个小国的软件公司；我将以立陶宛为例。我们在全球范围内销售软件并将我们的记录保存在 SQL Server 数据库中。我们非常成功，因此，在几年内，我们拥有 1,000,000 多条记录。但是，出于税收目的，我们经常需要报告销售情况，而且我们发现我们仅在本国销售了 100 份软件。通过创建仅包含立陶宛记录的索引视图，我们可以将我们需要的记录保存在 MS 文档中所述的索引缓存中。当我们运行 2008 年立陶宛销售报告时，我们的查询将搜索深度仅为 7 的索引（Log2(100) 和一些未使用的叶子）。如果我们在没有 VIEW 的情况下做同样的事情而只依赖表中的索引，我们将不得不遍历搜索深度为 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.

显然，与单独使用索引相比，视图本身将为我们提供性能优势（3 倍）。我已经尝试使用一个真实世界的例子，但您会注意到立陶宛销售的简单列表会给我们带来更大的优势。

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.

请注意，我只是在示例中使用了直 b 树。虽然我相当肯定 SQL Server 使用了 b 树的某些变体，但我不知道详细信息。尽管如此，这一点仍然成立。

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:

更新 3：关于索引视图是否只使用放置在基础表上的索引的问题出现了。也就是说，解释为：“索引视图只是标准索引的等价物，它不提供任何新的或独特的视图。” 当然，如果这是真的，那么上面的分析就是错误的！让我引用 Microsoft 文档中的一段话来说明为什么我认为这种批评是无效的或不正确的：

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 notjust a cached SQL Select that happens to use an index defined on the main table. Thus, I continue to stand by this answer.

有关于物理存储和有关指标是如何在视图中创建的文档中的其他信息数据的持久以上报价在一起，我认为这是肯定地说，索引视图是不是只是一个缓存的SQL选择恰好使用在主表上定义的索引。因此，我继续支持这个答案。

回答by BradC

Generally speaking, no.Views are primarily used for convenience and security, not for speed improvements.

一般来说，没有。视图主要用于方便和安全，而不是为了提高速度。

That said, SQL Server 2000 and above do have a special feature called Indexed Viewsthat cangreatly improve performance, but you have to create indexed views following a very specific set of guidelines.

也就是说，SQL Server 2000 和更高版本确实有一个称为索引视图的特殊功能，可以极大地提高性能，但是您必须按照一组非常具体的准则创建索引视图。

There is an important reference in Books Online in regards to view resolution.

联机丛书中有一个关于视图分辨率的重要参考。

Here is an article that describes the benefits and creation of indexed views:

这是一篇描述索引视图的好处和创建的文章：

For many years, Microsoft? SQL Server? has supported the ability to create virtual tables known as views. Historically, these views served these main purposes:

• To provide a security mechanism that restricts users to a certain subset of data in one or more base tables.

• To provide a mechanism that allows developers to customize how users can logically view the data stored in base tables.

With SQL Server 2000, the functionality of SQL Server views was expanded to provide system performance benefits. It is possible to create a unique clustered index on a view, as well as nonclustered indexes, to improve data access performance on the most complex queries. In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view.

多年来，微软？SQL服务器？支持创建称为视图的虚拟表的能力。从历史上看，这些观点服务于以下主要目的：

• 提供一种安全机制，将用户限制在一个或多个基表中的特定数据子集。

• 提供一种机制，允许开发人员自定义用户如何从逻辑上查看存储在基表中的数据。

在 SQL Server 2000 中，SQL Server 视图的功能得到了扩展，以提供系统性能优势。可以在视图上创建唯一的聚集索引以及非聚集索引，以提高最复杂查询的数据访问性能。在 SQL Server 2000 和 2005 中，具有唯一聚集索引的视图称为索引视图。

回答by Charles Bretana

In SQL Server at least, Query plans are stored in the plan cache for both views and ordinary SQL queries, based on query/view parameters. For both, they are dropped from the cache when they have been unused for a long enough period and the space is needed for some other newly submitted query. After which, if the same query is issued, it is recompiled and the plan is put back into the cache. So no, there is no difference, given that you are reusing the same SQL query and the same view with the same frequency.

至少在 SQL Server 中，基于查询/视图参数，查询计划存储在视图和普通 SQL 查询的计划缓存中。对于这两种情况，当它们已被使用足够长的时间并且其他一些新提交的查询需要空间时，它们将从缓存中删除。之后，如果发出相同的查询，则重新编译并将计划放回缓存中。所以不，没有区别，因为您以相同的频率重用相同的 SQL 查询和相同的视图。

Obviously, in general, a view, by it's very nature (That someone thought it was to be used often enough to make it into a view) is generally more likely to be "reused" than any arbitrary SQL statement.

显然，一般来说，视图的本质（有人认为它被经常使用以使其成为视图）通常比任何任意 SQL 语句更容易“重用”。

回答by Ryan Guill

EDIT: I was wrong, and you should see Marks answer above.

编辑：我错了，你应该看到上面的 Marks 回答。

I cannot speak from experience with SQL Server, but for most databases the answer would be no. The only potential benefit that you get, performance wise, from using a view is that it could potentially create some access paths based on the query. But the main reason to use a view is to simplify a query or to standardize a way of accessing some data in a table. Generally speaking, you won't get a performance benefit. I may be wrong, though.

我不能根据SQL Server 的经验发言，但对于大多数数据库，答案是否定的。在性能方面，使用视图的唯一潜在好处是它可能会根据查询创建一些访问路径。但使用视图的主要原因是为了简化查询或标准化访问表中某些数据的方式。一般来说，您不会获得性能优势。不过，我可能错了。

I would come up with a moderately more complicated example and time it yourself to see.

我会想出一个稍微复杂一点的例子，然后自己花时间看看。

回答by Otávio Décio

It may be faster if you create a materialized view (with schema binding). Non-materialized views execute just like the regular query.

如果您创建物化视图（使用模式绑定），速度可能会更快。非物化视图的执行就像常规查询一样。

回答by E.J. Brennan

My understanding is that a while back, a view would be faster because SQL Server could store an execution plan and then just use it instead of trying to figure one out on the fly. I think the performance gains nowadays is probably not as great as it once was, but I would have to guess there would be some marginal improvement to use the view.

我的理解是，不久前，视图会更快，因为 SQL Server 可以存储执行计划，然后只需使用它，而不是试图在运行中找出一个。我认为现在的性能提升可能不像以前那么好，但我不得不猜测使用该视图会有一些微小的改进。

回答by E.J. Brennan

Definitely a view is better than a nested query for SQL Server. Without knowing exactly why it is better (until I read Mark Brittingham's post), I had run some tests and experienced almost shocking performance improvements when using a view versus a nested query. After running each version of the query several hundred times in a row, the view version of the query completed in half the time. I'd say that's proof enough for me.

对于 SQL Server，视图绝对优于嵌套查询。在不知道为什么它更好的情况下（直到我阅读了 Mark Brittingham 的帖子），我运行了一些测试并且在使用视图与嵌套查询时经历了几乎令人震惊的性能改进。连续运行每个版本的查询数百次后，查询的视图版本完成了一半的时间。我会说这对我来说已经足够了。

回答by Tony Andrews

I would expect the two queries to perform identically. A view is nothing more than a stored query definition, there is no caching or storing of data for a view. The optimiser will effectively turn your first query into your second query when you run it.

我希望这两个查询执行相同。视图只不过是一个存储的查询定义，没有缓存或存储视图的数据。当您运行它时，优化器将有效地将您的第一个查询转换为您的第二个查询。

回答by Dasboot

It all depends on the situation. MS SQL Indexed views are faster than a normal view or query but indexed views can not be used in a mirrored database invironment (MS SQL).

这一切都取决于情况。MS SQL 索引视图比普通视图或查询更快，但不能在镜像数据库环境 (MS SQL) 中使用索引视图。

A view in any kind of a loop will cause serious slowdown because the view is repopulated each time it is called in the loop. Same as a query. In this situation a temporary table using # or @ to hold your data to loop through is faster than a view or a query.

任何类型的循环中的视图都会导致严重的减速，因为每次在循环中调用该视图时都会重新填充该视图。与查询相同。在这种情况下，使用 # 或 @ 来保存要循环的数据的临时表比视图或查询更快。

So it all depends on the situation.

所以这一切都取决于情况。

回答by Mohamad Reza Shahrestani

Select from a View or from a table will not make too much sense.

从视图或表中选择没有太大意义。

Of course if the View does not have unnecessary joins, fields, etc. You can check the execution plan of your queries, joins and indexes used to improve the View performance.

当然如果View没有不必要的join、字段等，你可以检查一下你的查询、join和索引的执行计划，用来提高View的性能。

You can even create index on views for faster search requirements. http://technet.microsoft.com/en-us/library/cc917715.aspx

您甚至可以在视图上创建索引以满足更快的搜索要求。http://technet.microsoft.com/en-us/library/cc917715.aspx

But if you are searching like '%...%' than the sql engine will not benefit from an index on text column. If you can force your users to make searches like '...%' than that will be fast

但是，如果您像 '%...%' 那样搜索，那么 sql 引擎将不会从文本列上的索引中受益。如果您可以强制您的用户进行诸如“...%”之类的搜索，那将会很快

referred to answer on asp forums : https://forums.asp.net/t/1697933.aspx?Which+is+faster+when+using+SELECT+query+VIEW+or+Table+

参考 asp 论坛上的答案：https: //forums.asp.net/t/1697933.aspx?Which+is+faster+when+using+SELECT+query+VIEW+or+Table+