在Scaling Up Your Data Warehouse with SQL Server 2008 R2中,作者建议使用YYYYMMDD格式的整数日期键作为事实表上的聚簇索引,以帮助优化查询速度。
将您的关键日期字段转换为日期 key 的最佳方法是什么?我觉得以下方法可行,但有点草率:
select Replace(CONVERT(varchar,GETDATE(),102),'.','')
显然,我不是在使用getdate,而是在表中的日期列中将在聚合中使用的日期。
首先,您如何建议进行这种转换?我的想法可以接受吗?
其次,有人将Date键用作聚簇索引有很大的成功吗?
最佳答案
ISO long(112)可以解决问题:
SELECT CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))
出于某种原因,使用ISO 112将getdate()直接转换为int会给出41008,但是通过VARCHAR似乎可以正常工作-如果我想到更快的转换,我会进行更新。
编辑:关于仅int与varchar的争论,这是我的发现(可在我的测试台和生产服务器上重复)Varchar方法使用较少的cpu时间进行一百万次强制转换,但总体速度较慢-可以忽略不计,除非您处理数十亿行
编辑2 :修订测试用例以清除缓存和不同的日期
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH RawDates ( [Date] )
AS ( SELECT TOP 500000
DATEADD(DAY, N, GETDATE())
FROM TALLY
)
SELECT YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date])
FROM RawDates
SET STATISTICS TIME OFF
(500000 row(s) affected)
SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 255ms.
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH RawDates ( [Date] )
AS ( SELECT TOP 500000
DATEADD(DAY, N, GETDATE())
FROM TALLY
)
SELECT CONVERT(INT, CONVERT(VARCHAR(8), [Date], 112))
FROM RawDates
SET STATISTICS TIME OFF
(500000 row(s) affected)
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 602ms
关于TSQL DateTime到DateKey Int,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10091680/