database 查询与查看

Query vs. View

提问by LuRsT

I want to know what is the difference between a query and a view in terms of performance. And if a view is costly, what else besides a query could I do to improve performance?

我想知道查询和视图在性能方面有什么区别。如果视图成本很高，除了查询之外，我还能做什么来提高性能？

采纳答案by Dave Markle

Views and ad-hoc queries, in the simple case, are nearly identical in terms of performance. So much so that when you program with a view, you should think of it as though the text of the view definition were being cut and pasted into your parent query.

在简单的情况下，视图和即席查询在性能方面几乎相同。如此之多以至于当您使用视图编程时，您应该将其视为视图定义的文本被剪切并粘贴到您的父查询中。

HLGEM points out in his answer that certain editions of SQL Server allows you to "index" views -- in this case, behind the scenes SQL Server maintains the same structures that underlie a table, making an indexed view and a table very similar in terms of performance.

HLGEM 在他的回答中指出，某些版本的 SQL Server 允许您“索引”视图——在这种情况下，SQL Server 在幕后维护作为表基础的相同结构，使得索引视图和表在术语上非常相似的性能。

In SQL Server, though you can generally nest views fairly liberally without running into performance problems, it can make things more difficult to understand and debug.

在 SQL Server 中，虽然您通常可以相当自由地嵌套视图而不会遇到性能问题，但它会使事情更难以理解和调试。

回答by HLGEM

I can't speak for all databases, but in SQL Server you cannot index views unless you have an Enterprise version. An unindexed view can be significantly poorer in terms of performance than a query especially if you are writing a query against it to add some where conditions. Indexed views generally can perform fairly well. An indexed view can also be against multiple fields which are in differnt tables and that may imporve performance over the ad hoc query. (It may not too, in performance tuning, you must always test against your particular circumstances.)

我不能代表所有数据库，但在 SQL Server 中，除非您有企业版，否则您无法索引视图。未索引的视图在性能方面可能比查询差很多，尤其是当您针对它编写查询以添加一些 where 条件时。索引视图通常可以很好地执行。索引视图还可以针对不同表中的多个字段，这可能会提高临时查询的性能。（可能不是，在性能调优中，您必须始终针对您的特定情况进行测试。）

One point against views is that they do not allow for run-time selection of where criteria. So often you end up with both a view and a query.

反对意见的一点是它们不允许在运行时选择 where 条件。通常，您最终会同时获得视图和查询。

Views can be more easily maintained (Just add that new table in a join and everything accessing financial reports has it available) but they are much more difficult to performance tune. This is in part because they tend to be over generalized and thus are slower than their counterparts which only return the minimum necessary. And yes as Jonathan said, you can far too easily get into joining together views for a report into a mess which joins to the same large tables many more times than need be and is very slow.

视图可以更容易地维护（只需在联接中添加新表，访问财务报告的所有内容都可用），但性能调整要困难得多。这部分是因为它们往往过于泛化，因此比只返回必要的最小值的对应物要慢。是的，正如乔纳森所说，您可以很容易地将报表的视图连接在一起，导致连接到同一个大表的次数比需要的多得多，而且速度非常慢。

Two places where views shine though is: Making sure that complex relationships are always correctly described. This is one reason why report writers tend to favor them. Limiting access to a subset of records

视图的两个亮点是： 确保始终正确描述复杂的关系。这就是报告作者倾向于支持他们的原因之一。限制对记录子集的访问

There are also limitations on the type of queries that can be done for a view vice an ad hoc query or a stored proc. For instance you can't use an if statement (or other procedural type code such as looping) or as noted above you cannot provide run-time values for the where criteria.

对于视图、即席查询或存储过程可以执行的查询类型也有限制。例如，您不能使用 if 语句（或其他过程类型代码，例如循环），或者如上所述，您不能为 where 条件提供运行时值。

One place where views are often significantly slower is when they call other views. The underlying views need to be fully realized in some databases and thus you might need to callup 4,459,203 records to see the 10 you are ultimately interested in. Start to layer this more than once and it can get very slow, very fast; views that call views are simply a poor practice.

视图通常显着变慢的一个地方是当它们调用其他视图时。底层视图需要在某些数据库中完全实现，因此您可能需要调用 4,459,203 条记录来查看您最终感兴趣的 10 条记录。开始多次分层，它会变得非常慢，非常快；调用视图的视图只是一种糟糕的做法。

回答by Scott

In SQL Server I believe that the performance difference between views and queries is negligible. What I would recommend doing to improve performance is to create another table that holds the results of the view. You could perhaps create a staging table where new data is held and then a stored procedure can be run at some interval that populates the working table with the new information. A trigger might be good for this purpose. Depending on the requirements of your application this design may or may not be suitable. If you are working with near real-time data, this approach will lead to concurrency issues...

在 SQL Server 中，我认为视图和查询之间的性能差异可以忽略不计。为了提高性能，我建议做的是创建另一个包含视图结果的表。您或许可以创建一个暂存表，其中保存新数据，然后可以在某个时间间隔运行存储过程，用新信息填充工作表。触发器可能适用于此目的。根据您的应用要求，这种设计可能适合也可能不适合。如果您正在处理近乎实时的数据，这种方法将导致并发问题......

One other thing to look into, is to make absolutely sure that the base tables you are using to construct your view are indexed correctly, and that the query itself is optimized. Finally, I believe it is possible in SQL Server enterprise to create indexed views although I have not used them before.

要研究的另一件事是绝对确保用于构造视图的基表已正确编制索引，并且查询本身已优化。最后，我相信在 SQL Server 企业中创建索引视图是可能的，尽管我以前没有使用过它们。

回答by Jonathan Leffler

A view is barely more expensive to the computer than writing out the query longhand. A view can save the programmer/user a lot of time writing the same query out time after time, and getting it wrong, and so on. The view may also be the only way to access the data if views are also used to enforce authorization (access control) on the underlying tables.

对于计算机来说，视图比手写查询更昂贵。视图可以为程序员/用户节省大量时间，因为它们可以一次又一次地编写相同的查询，并且会出错，等等。如果视图还用于对基础表强制执行授权（访问控制），则视图也可能是访问数据的唯一方式。

If the query does not perform well, you need to review how the query is formed, and whether the tables all have the appropriate indexes on them. If your system needs accurate statistics for the optimizer to perform well, have you updated those statistics sufficiently recently?

如果查询执行得不好，您需要查看查询的形成方式，以及表上是否都有适当的索引。如果您的系统需要准确的统计信息才能使优化器正常运行，您最近是否充分更新了这些统计信息？

Once upon a long time ago, I came across a system where a query generator had created one query that listed seventeen tables in a single FROM clause, including several LEFT OUTER JOIN of a table with itself. And, in fact, closer scrutiny revealed that several of the 'tables' were in fact multi-table views, and some of these also involved self outer joins, and were themselves involved in self outer joins of the view. To say "ghastly" is an understatement. There was a lot of cleanup possible to improve the performance of that query - eliminating unnecessary outer joins, self joins, and so on. (It actually pre-dated the explicit join notation of SQL-92 - I said a long time ago - so the outer join syntax was DBMS-specific.)

很久以前，我遇到过一个系统，其中查询生成器创建了一个查询，该查询在单个 FROM 子句中列出了 17 个表，其中包括一个表与自身的几个 LEFT OUTER JOIN。而且，事实上，更仔细的表明，其中一些“表”实际上是多表视图，其中一些还涉及自外连接，并且它们本身也涉及视图的自外连接。说“可怕”是轻描淡写。有很多清理可能可以提高该查询的性能 - 消除不必要的外连接、自连接等。（它实际上早于 SQL-92 的显式连接表示法——我很久以前说过——所以外连接语法是特定于 DBMS 的。）

回答by Hyman Ryan

Views promote code reuse and can abstract away database complexity to give a more coherent 'business' model of data. However they are not nearly as tunable. You may find yourself in a position where you need to provide join hints or other low level optimisations and many DBA's that i have worked with do not like them being applied to views as they may then be reused across many queries, the opinion being that these types of hints should be employed as sparingly as possible. I like using views myself.

视图促进代码重用，并可以抽象出数据库的复杂性，以提供更连贯的数据“业务”模型。然而，它们几乎不可调。您可能会发现自己处于需要提供连接提示或其他低级优化的位置，并且与我一起工作的许多 DBA 不喜欢将它们应用于视图，因为它们可能会在许多查询中重复使用，意见是这些应尽可能少地使用提示类型。我喜欢自己使用视图。

回答by Daniel M

If they do exactly the same thing a view might be slightly faster on first execution as the database server will have a precompiled execution plan for it. Depends on your server though.

如果它们做完全相同的事情，则视图在第一次执行时可能会稍微快一点，因为数据库服务器将有一个预编译的执行计划。但这取决于您的服务器。

Empasis on might and slightly...

强调力量和轻微......

回答by Giovanni Galbo

A view is still a query, it just abstracts certain parts of it so that your queries can be simplified (if they do similar things) and to maximize reuse.

视图仍然是一个查询，它只是抽象了它的某些部分，以便您的查询可以被简化（如果它们做类似的事情）并最大限度地重用。

回答by David Pike

If you mean network performance then working from a local cache (as with ADO.Net DataSets) would reduce network traffic- but could cause problems with locking. Just a thought.

如果您的意思是网络性能，那么从本地缓存（如 ADO.Net 数据集）工作会减少网络流量 - 但可能会导致锁定问题。只是一个想法。