适合在排名函数中排序的索引 [英] Suitable indexes for sorting in ranking functions

问题描述

我有一个表格，用于保存项目之间的父子关系.那些可以随着时间的推移而改变，并且有必要保留完整的历史记录，以便我可以随时查询关系.

I have a table which keeps parent-child-relations between items. Those can be changed over time, and it is necessary to keep a complete history so that I can query how the relations were at any time.

表格是这样的(我删除了一些列和主键等以减少噪音):

The table is something like this (I removed some columns and the primary key etc. to reduce noise):

CREATE TABLE [tblRelation](
    [dtCreated] [datetime] NOT NULL,
    [uidNode] [uniqueidentifier] NOT NULL,
    [uidParentNode] [uniqueidentifier] NOT NULL
)

我在特定时间获取关系的查询是这样的(假设@dt 是具有所需日期的日期时间):

My query to get the relations at a specific time is like this (assume @dt is a datetime with the desired date):

SELECT *
  FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY r.uidNode ORDER BY r.dtCreated DESC) ix, r.*
      FROM [tblRelation] r
      WHERE (r.dtCreated < @dt)
    ) r
  WHERE r.ix = 1

此查询运行良好.但是，性能还没有我想要的那么好.在查看执行计划时，基本上可以归结为聚簇索引扫描(成本的 36%)和排序(成本的 63%).

This query works well. However, the performance is not yet as good as I would like. When looking at the execution plan, it basically boils down to a clustered index scan (36% of cost) and a sort (63% of cost).

我应该使用什么索引来加快查询速度?或者有没有更好的方法来在这个表上执行这个查询?

What indexes should I use to make this query faster? Or is there a better way altogether to perform this query on this table?

推荐答案

此查询的理想索引应该是关键列 uidNode、dtCreated 和所有剩余的包含列当您返回 r.* 时，表中的列使索引覆盖.如果查询通常只返回相对较少的行数(这似乎是由于 WHERE r.ix = 1 过滤器的原因)，则可能不值得进行索引覆盖，但因为成本键查找可能不会超过大索引对 CUD 语句的负面影响.

The ideal index for this query would be with key columns uidNode, dtCreated and included columns all remaining columns in the table to make the index covering as you are returning r.*. If the query will generally only be returning a relatively small number of rows (as seems likely due to the WHERE r.ix = 1 filter) it might not be worthwhile making the index covering though as the cost of the key lookups might not outweigh the negative effects of the large index on CUD statements.

这篇关于适合在排名函数中排序的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！