使用视图提高查询性能
我在 SQL Server 数据库中有一个包含 10 多百万条记录的大表。该表包含美国所有 50 个州的某些类型的数据。因此，如果我从该表创建 50 个视图（每个州一个），从我的应用程序进行查询的性能是否会提高？其他建议？
I have a large table with 10+ millions records in a SQL Server database. The table contains certain type of data for all 50 states in the US. So if I create 50 views, one for each state, from this table, would the performance of making queries from my application be improved? Other suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
不。视图是一个可扩展的宏，因此相同的表无论如何都会出现在计划中。
除非它被索引了。 50 个索引视图很可能是多余的。
如果 5000 万行的性能很慢（实际上并没有那么多），那么这就是索引问题。
编辑：
首先，我将使用 加权缺失索引 dmv 查询 看看哪里最物有所值
No. A view is a macro that expands so the same tables end up in the plan anyway.
Unless it's indexed. 50 indexed views is most likely overkill.
If you have slow performance with 50 million rows (it's not that much really) then it's an indexing problem.
Edit:
To start with, I'd use the weighted missing index dmv query to see where you get the most bang for your buck
普通（非索引）视图无法提高性能 - 它们可以被视为 SELECT 查询的“简写”或“别名”，因为它们下面没有物理结构。
索引视图是一个不同的野兽，但到目前为止您似乎并不需要它们。
您需要的是在表上创建适当的索引，并且可能需要重新设计表（例如，将其拆分为多个表）。
如果您需要更具体的建议，请在此处发布表结构和一些典型查询的示例（您想要优化的查询）。
Normal (non-indexed) views cannot improve performance - they could be thought of as 'shorthands' or 'aliases' for SELECT queries, as they have no physical structure under them.
Indexed views are a different beast, but it doesn't seem that you need them so far.
What you need is to create appropriate indexes on your table, and, possibly, to redesign the table (for instance, split it to several tables).
If you want more specific advice, post here the table structure and some examples of typical queries (the ones you want to optimise for).
您走在正确的道路上：
首先确保对数据建立索引以反映您需要快速读取的内容。然后，由于过滤确实使其速度更快，因此我会考虑通过允许通过存储过程（带有状态参数）访问数据来要求对状态进行过滤。
You're on the right track:
Start with making sure you index the data to reflect the reads you need to be fast. Then, since filtering does make it faster, I would consider require filtering on state by allowing access to the data via a stored procedure (with a parameter for state).
如果执行计划中有正确的索引和使用情况，那么最大的问题是内存缓存的数量和磁盘的读取速度。创建视图不会修复任何这些问题，它仍然是同一磁盘/缓存上的相同数据，只是引用它的逻辑方式不同。
If you have the proper index and usage in your execution plans, then the big issues is the quantity of memory cache and read speed of the disk. Creating views won't fix any of those, it is still the same data on the same disk/cache, just a different logical way of referring to it.
一个简单的建议：
One simple suggestion:
这是一个非常小的数据库，如果您遇到性能问题，您的索引错误，您的数据库设计很糟糕，或者您设计了性能不佳的查询。如果设计正确，SQL Server 可以毫不费力地处理数万亿条记录。
顺便说一句，如果您使用调用视图的视图，视图可能会成为性能杀手。
That is such a tiny database that if you are having performance problems your indexing wrong, you databases design is bad or you have designed poorly performing queries. SQL server can handle trillions of records without breaking a sweat if designed correctly.
Views BTW can be performance Killers if you use views that call views.