I've a query that performs join on many tables which is resulting in poor performance.
我有一个查询，在许多表上执行连接，导致性能不佳。
To improve the performance, I've created an indexed view and I see a significant improvement in the performance of the query on view with date filter. However, my concern is about the storage of the index. From what I have read, the unique clustered index is stored on SQL Server. Does it mean it stores separately the entire data resulting as part of joins within the view? If so, if I've included all columns from tables that are part of the joins in the view, would the disk space on the server consumed be approx double the disk space without indexed view? And every time I enter data into underlying tables, the data is duplicated for the indexed view?
为了提高性能，我创建了一个索引视图，我发现使用日期过滤器查看查询的性能有了显着改善。但是，我关心的是索引的存储。根据我的阅读，唯一的聚簇索引存储在SQL Server上。这是否意味着它将整个数据作为连接的一部分单独存储在视图中？如果是这样，如果我在视图中包含了作为连接一部分的表中的所有列，那么服务器上的磁盘空间是否会大约是没有索引视图的磁盘空间的两倍？每次我将数据输入基础表时，索引视图的数据都会重复？
3 个解决方案
#1
4
Yes, that is correct. An indexed view persists all data in the view separately from the source tables. Depending on the columns and joins, the data is duplicated, and can actually be many times larger than the source tables.
对，那是正确的。索引视图将视图中的所有数据与源表分开。根据列和连接，数据是重复的，实际上可能比源表大很多倍。
#2
5
That is correct. An indexed view is basically an additional table that contains a copy of all the data in a sorted way. That's what makes it so fast, but as everything in SQL Server land, it comes at a price - in this case the additional storage required and the additional time required to keep all the copies of the data in sync.
那是对的。索引视图基本上是一个附加表，它以排序方式包含所有数据的副本。这就是它如此快速的原因，但随着SQL Server中的所有内容的出现，它需要付出代价 - 在这种情况下需要额外的存储空间以及保持数据的所有副本同步所需的额外时间。
The same is true for a normal index on a table. It is also a copy of the index keys (plus some information of where to find the original row), that needs additional storage and additional time during updates to be maintained.
对于表上的普通索引也是如此。它也是索引键的副本（加上在哪里可以找到原始行的一些信息），需要额外的存储空间以及维护更新期间的额外时间。
To figure out if adding an index on a table or view makes sense, requires you to look at the entire system and see if the performance improvement for the one query is worth the performance degradation of other queries.
要确定在表或视图上添加索引是否有意义，需要您查看整个系统，并查看一个查询的性能改进是否值得其他查询的性能下降。
In your case you should also (first) check if additional indexes on the underlying tables might help your queries.
在您的情况下，您还应该（首先）检查基础表上的其他索引是否可以帮助您进行查询。
#3
3
Pretty much, yeah. You've made a trade-off where you get better performance in return for some additional effort by the engine, plus the additional storage needed to persist it.
差不多，是的。你已经做了一个权衡，你可以获得更好的性能，以换取引擎的额外工作量，以及持续存储所需的额外存储空间。
#1
4
Yes, that is correct. An indexed view persists all data in the view separately from the source tables. Depending on the columns and joins, the data is duplicated, and can actually be many times larger than the source tables.
对，那是正确的。索引视图将视图中的所有数据与源表分开。根据列和连接，数据是重复的，实际上可能比源表大很多倍。
#2
5
That is correct. An indexed view is basically an additional table that contains a copy of all the data in a sorted way. That's what makes it so fast, but as everything in SQL Server land, it comes at a price - in this case the additional storage required and the additional time required to keep all the copies of the data in sync.
那是对的。索引视图基本上是一个附加表，它以排序方式包含所有数据的副本。这就是它如此快速的原因，但随着SQL Server中的所有内容的出现，它需要付出代价 - 在这种情况下需要额外的存储空间以及保持数据的所有副本同步所需的额外时间。
The same is true for a normal index on a table. It is also a copy of the index keys (plus some information of where to find the original row), that needs additional storage and additional time during updates to be maintained.
对于表上的普通索引也是如此。它也是索引键的副本（加上在哪里可以找到原始行的一些信息），需要额外的存储空间以及维护更新期间的额外时间。
To figure out if adding an index on a table or view makes sense, requires you to look at the entire system and see if the performance improvement for the one query is worth the performance degradation of other queries.
要确定在表或视图上添加索引是否有意义，需要您查看整个系统，并查看一个查询的性能改进是否值得其他查询的性能下降。
In your case you should also (first) check if additional indexes on the underlying tables might help your queries.
在您的情况下，您还应该（首先）检查基础表上的其他索引是否可以帮助您进行查询。
#3
3
Pretty much, yeah. You've made a trade-off where you get better performance in return for some additional effort by the engine, plus the additional storage needed to persist it.
差不多，是的。你已经做了一个权衡，你可以获得更好的性能，以换取引擎的额外工作量，以及持续存储所需的额外存储空间。