Question

我试图优化我 SQL 查询, 我总是回来回到这个问题, 我希望得到一些洞察力如何我可以最佳优化这一点。

简洁的说,我有一个简单的雇员表:

< 强> tbl_ 雇员 < / 强 >

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

这在另一个表格中为每个雇员提供了以下信息:

< strength> tbl_ enderen- version 用户名: <% 1\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

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多名员工记录的查询时, 每位员工都有几个版本, 这个查询开始窒息, 需要几秒钟才能运行。

球棒上有一对夫妇罢工但我不确定如何克服他们

显然,Cross Apper增加了一些性能损失。处理这种版本信息是否有最佳做法? 是否有更好的方法获得最高版本的记录?
版本的表格没有集成索引, 无论是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 coalesce(EndDate, getdate()) >= getdate()

或者,在此情况下, 您可以默认 EndDate 在未来某个遥远的日期, 远如 01- o1- 9999 。您可以在创建表格语句中添加此默认值, 使列不是空的, 然后您可以总是使用该语句 :

where getdate() between EffDate and EndDate

正如Martin在其评论中指出的,联合()可能会妨碍使用指数(在SQL服务器中确实如此),但这并不有问题。

这被称为一个缓慢变化的层面。 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 和版本的 tbl_ employenes_ version 中有一个索引也很有可能有用。

另外,请注意,只要您重新选择不包含 tbl_ employenes_ version 的字段,您只需加入 tbl_ employenes 。

友情链接