日期表/维度查询和索引 [英] Date Table/Dimension Querying and Indexes

问题描述

我正在创建一个可靠的日期表，想知道链接到它的最佳方法.主键聚集索引将位于智能日期整数键(根据Kimball规范)上，其名称为DateID.到目前为止，我一直在像这样运行查询:

I'm creating a robust date table want to know the best way to link to it. The Primary Key Clustered Index will be on the smart date integer key (per Kimball spec) with a name of DateID. Until now I have been running queries against it like so:

select Foo.orderdate -- a bunch of fields from Foo
      ,DTE.FiscalYearName
      ,DTE.FiscalPeriod
      ,DTE.FiscalYearPeriod
      ,DTE.FiscalYearWeekName
      ,DTE.FiscalWeekName  
      FROM SomeTable Foo
     INNER JOIN
       DateDatabase.dbo.MyDateTable DTE
     ON DTE.date = CAST(FLOOR(CAST(Foo.forderdate AS FLOAT)) AS DATETIME)

请记住，Date是一个非聚集索引字段，其值如下: 2000-01-01 00:00:00.000

Keep in mind that Date is a nonclustered index field with values such as: 2000-01-01 00:00:00.000

我刚想到，由于我有一个聚集的整数索引(DATEID)，也许我应该在数据库字段中转换日期时间以使其匹配并基于该字段进行链接.

It just occured to me that since I have a clustered integer index (DATEID) that perhaps I should be converting the datetime in my database field to match it and linking based upon that field.

您怎么看?

此外，根据您的第一个答案，如果我通常从日期表中提取这些字段，那么如何优化这些字段的检索类型?覆盖指数?

Also, depending on your first answer, if I am typically pulling those fields from the date table, what kind of index how can I optimize the retrieval of those fields? Covering index?

推荐答案

即使不更改数据库结构，使用如下所示的日期范围联接也可以获得更好的性能:

Even without changing the database structure, you'd get much better performance using a date range join like this:

select Foo.orderdate -- a bunch of fields from Foo 
  ,DTE.FiscalYearName 
  ,DTE.FiscalPeriod 
  ,DTE.FiscalYearPeriod 
  ,DTE.FiscalYearWeekName 
  ,DTE.FiscalWeekName   
  FROM SomeTable Foo 
 INNER JOIN 
   DateDatabase.dbo.MyDateTable DTE 
 ON Foo.forderdate >= DTE.date AND Foo.forderdate < DATEADD(dd, 1, DTE.date)

但是，如果您可以对其进行更改，以便Foo表中包含一个DateID字段，那么，是的，如果不进行任何转换后的日期值或日期范围，则可以通过与之结合来获得最佳性能.

However, if you can change it so that your Foo table includes a DateID field then, yes, you'd get the best performance by joining with that instead of any converted date value or date range.

如果您将其更改为联接在DateID上，并且DateID是MyDateTable聚集索引的第一列，则它已经覆盖了(聚集索引始终包含所有其他字段).

If you change it to join on DateID and DateID is the first column of the clustered index of the MyDateTable then it's already covering (a clustered index always includes all other fields).

这篇关于日期表/维度查询和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！