SQL Server 日期时间与 Int 关键性能 [英] SQL Server Datetime vs Int key performance

问题描述

适合您的数据库设计/性能专家.

For you database design/performance gurus out there.

如果您有一个旨在跟踪财政年度期间财务数据的数据库，那么执行日期范围类型搜索(例如 X 和 Y 之间的 PaymentDate)是否更好/性能更高/更清晰，还是保留一个 int 键更好基于其中定义了财政年度期间的表，并用付款日期和该键标记付款表，因此 where 子句是 where FiscalPeriodID = X?

If you have a database that is intended to track financial data for fiscal year periods, is it better/more performance/more clear to do daterange type searches like PaymentDate Between X and Y or is it better to keep a int-key based table with fiscal year periods defined in it and tag the payment table with the payment date and that key, so the where clause is where FiscalPeriodID = X?

我敢肯定，对于较小的数据集，这无关紧要，但我们假设这些数据将包含数百万行.

I'm sure for smaller datasets it doesn't matter, but let's assume this data will be in the millions of rows.

推荐答案

我每天处理数百万行的仓库，我们发现智能日期键是正确的方法.这是 YYYYMMDD 的格式.因此，要查找 2008 年的全部内容，您需要执行以下操作:

I deal with warehouses in the millions of rows on a daily basis, and we find that smart date keys are the way to go. This is in the format of YYYYMMDD. So to find all of 2008, you'd do:

select
    *
from
    gl
where
    postdate between 20080101 and 20081231

使用索引列，速度非常快，甚至跨越 10 亿行.这也指向一个日期表，因此我们可以添加星期几、月份名称或任何其他关于我们通过该连接获得的日期的信息.

With an indexed column this is phenomenally fast, even across one billion rows. This is also pointing to a date table, so we can tack on day of week, month names, or whatever else information about dates we have with that join.

当然，这些仓库通常是为了支持 SSAS 多维数据集(OLAP 数据库)而构建的，因此日期表成为我们的日期维度.在 int 上加入比在 datetime 上加入要快得多.

Of course, these warehouses are usually built to support SSAS cubes (OLAP databases), and so that date table becomes our date dimension. It's much faster to join on an int than a datetime.

这篇关于SQL Server 日期时间与 Int 关键性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！