I have 2 tables, Items and ItemDetails. Generally any query on this data is performed through the Items_join_ItemDetails view, which joins the two tables on the common ItemID field.
我有2个表,Items和ItemDetails。通常,对此数据的任何查询都是通过Items_join_ItemDetails视图执行的,该视图将公共ItemID字段上的两个表连接起来。
Currently ItemDetails has a clustered index on (Date, ItemID) and a non-clustered index on just ItemID. Items has a clustered index on ItemID.
目前,ItemDetails在(Date,ItemID)上有一个聚簇索引,在ItemID上有一个非聚集索引。 Items在ItemID上有一个聚簇索引。
When the view is queried, it is almost always for a date range, which is why the clustered index is on Date for ItemDetails. However, this means that the view has to join using the non-clustered ItemID index for ItemDetails.
查询视图时,几乎总是针对日期范围,这就是聚集索引在ItemDetails的Date上的原因。但是,这意味着视图必须使用ItemDetails的非聚集ItemID索引进行连接。
Would performance be better if I switched the clustered and non-clustered indexes for ItemDetails? I can see how this would help the join operation, however I can also see how it would hurt the date filtering by queries.
如果我为ItemDetails切换聚簇索引和非聚簇索引,性能会更好吗?我可以看到这将如何帮助连接操作,但是我也可以看到它会如何影响查询的日期过滤。
1 个解决方案
#1
3
The clustered index is over 2 columns so the join on ItemID should come from this index. The NC index on ItemID alone may be unused, at least for this query.
聚簇索引超过2列,因此ItemID上的连接应来自此索引。仅ItemID上的NC索引可能未使用,至少对于此查询而言。
What is more important is to ensure the indexes are covering: all columns needed are in the indexes.
更重要的是确保索引覆盖:所需的所有列都在索引中。
Do you have any performance issues?
你有任何性能问题吗?
We'd also need the table and view definitions to make further suggestions.
我们还需要表和视图定义来提出进一步的建议。
#1
3
The clustered index is over 2 columns so the join on ItemID should come from this index. The NC index on ItemID alone may be unused, at least for this query.
聚簇索引超过2列,因此ItemID上的连接应来自此索引。仅ItemID上的NC索引可能未使用,至少对于此查询而言。
What is more important is to ensure the indexes are covering: all columns needed are in the indexes.
更重要的是确保索引覆盖:所需的所有列都在索引中。
Do you have any performance issues?
你有任何性能问题吗?
We'd also need the table and view definitions to make further suggestions.
我们还需要表和视图定义来提出进一步的建议。