t在 T-SQL 中设置和查询版本控制记录的最佳做法

Question

我正在尝试优化我的SQL查询，我总是回到这个问题，我希望深入了解如何最好地优化这个问题。

为简洁起见，假设我有一个简单的员工表：

tbl_employees

Id     HiredDateTime
------------------
1      ...        
2      ...      

这在另一个表中为每个员工提供了版本控制信息：

tbl_emplyees_versioned

Id     Version   Name     HourlyWage
-------------------------------
1      1         Bob      10
1      2         Bob      20
1      3         Bob      30
2      1         Dan      10
2      2         Dan      20

这就是在视图中检索最新版本记录的方式：

Select tbl_employees.Id, employees_LatestVersion.Name, employees_LatestVersion.HourlyWage, employees_LatestVersion.Version
From tbl_employees
Inner Join tbl_employees_versioned
 ON tbl_employees.Id = tbl_employees_versioned.Id
CROSS APPLY 
   (SELECT Id, Max(Version) AS Version
    FROM tbl_employees_versioned AS employees_LatestVersion
    WHERE Id = tbl_employees_versioned.Id
    GROUP BY Id) AS employees_LatestVersion

要获得这样的响应，请执行以下操作：

Id     Version   Name     HourlyWage
-------------------------------
1      3         Bob      30
2      2         Dan      20

当拉取包含超过 500 条员工记录的查询时，每个记录都有几个版本，此查询开始阻塞并需要几秒钟才能运行。

马上

就有几个罢工，但我不确定如何克服它们。

1. 显然，交叉应用会增加一些性能损失。处理此类版本化信息时是否有最佳做法？有没有更好的方法来获得最高版本的记录？

2. 版本化表没有聚集索引，因为 Id 和版本都不是唯一的。将它们连接在一起，但它不是那样工作的。相反，有一个非聚集索引用于 Id，另一个用于版本。是否有更好的方法来索引此表以获得任何性能提升？索引视图真的会在这里有所帮助吗？

Answer 1

我认为构建数据的最佳方法是使用开始日期和结束日期。 因此，原始表的数据结构如下所示：

create table tbl_EmployeesHistory (
    EmployeeHistoryId int,
    EffDate date not null,
    EndDate date,
    -- Fields that describe the employee during this time
)

然后，您可以使用视图查看当前版本：

create view vw_Employees as
    select *
    from tbl_EmployeesHistory
    where EndDate is NULL

在某些情况下，如果允许将来结束日期，则 where 子句将是：

where coalesce(EndDate, getdate()) >= getdate()

或者，在这种情况下，您可以将 EndDate 默认为遥远的未来日期，例如"01-o1-9999"。 您可以将其添加为创建表语句中的默认值，使该列不为 null，然后您始终可以使用该语句：

where getdate() between EffDate and EndDate

正如Martin在他的评论中指出的那样，coalesce（）可能会阻碍索引的使用（它在SQL Server中确实如此），而这没有这个问题。

这称为缓慢变化的维度。 Ralph Kimball在他的数据仓库书籍中详细讨论了这个概念。

Answer 2

以下是查看

每个员工的最新版本的一种方法：

Select Id, Name, HourlyWage, Version
FROM (
  Select E.Id, V.Name, V.HourlyWage, V.Version,
   row_number() OVER (PARTITION BY V.ID ORDER BY V.Version DESC) as nRow
  From tbl_employees E
  Inner Join tbl_employees_versioned V ON E.Id = V.Id
) A
WHERE A.nRow = 1

我怀疑这会比您以前的解决方案表现得更好。跨 Id 和 Version 的索引tbl_employees_versioned很可能也会有所帮助。

另请注意，仅当您选择不在tbl_employees_versioned中的字段时，才需要加入tbl_employees。