主页 / dba / 问题 / 10113
Accepted
Thomas Stringer
Asked: 2012-01-04 19:10:30 +0800 CST

检索日期范围的最有效方法

  • 772

使用这样的表结构检索日期范围的最有效方法是什么？

create table SomeDateTable
(
    id int identity(1, 1) not null,
    StartDate datetime not null,
    EndDate datetime not null
)
go

假设您想要StartDate和的范围EndDate。所以换句话说，如果StartDate落在 and 之间@StartDateBegin，@StartDateEnd并且EndDate落在 and 之间@EndDateBegin，@EndDateEnd那么做一些事情。

我知道有几种方法可以解决这个问题，但最建议的是什么？

sql-server performance

2 个回答

  1. Best Answer

    一般来说，这是一个很难解决的问题，但我们可以做一些事情来帮助优化器选择一个计划。此脚本创建一个包含 10,000 行的表，其中行的已知伪随机分布来说明：

    CREATE TABLE dbo.SomeDateTable
(
    Id          INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    StartDate   DATETIME NOT NULL,
    EndDate     DATETIME NOT NULL
);
GO
SET STATISTICS XML OFF
SET NOCOUNT ON;
DECLARE
    @i  INTEGER = 1,
    @s  FLOAT = RAND(20120104),
    @e  FLOAT = RAND();

WHILE @i <= 10000
BEGIN
    INSERT dbo.SomeDateTable
        (
        StartDate, 
        EndDate
        )
    VALUES
        (
        DATEADD(DAY, @s * 365, {d '2009-01-01'}),
        DATEADD(DAY, @s * 365 + @e * 14, {d '2009-01-01'})
        )
        
    SELECT
        @s = RAND(),
        @e = RAND(),
        @i += 1
END

    第一个问题是如何索引这个表。一种选择是在列上提供两个索引DATETIME，因此优化器至少可以选择是否在StartDate或上查找EndDate。

    CREATE INDEX nc1 ON dbo.SomeDateTable (StartDate, EndDate)
CREATE INDEX nc2 ON dbo.SomeDateTable (EndDate, StartDate)

    自然，两者的不等式StartDate意味着EndDate每个索引中只有一列可以支持示例查询中的查找，但这是我们能做的最好的事情。我们可能会考虑将每个索引中的第二列设置为一个INCLUDE而不是一个键，但我们可能有其他查询可以在前导列上执行相等搜索并在第二列上执行不等式搜索。此外，我们可以通过这种方式获得更好的统计数据。反正...

    DECLARE
    @StartDateBegin DATETIME = {d '2009-08-01'},
    @StartDateEnd DATETIME = {d '2009-10-15'},
    @EndDateBegin DATETIME = {d '2009-08-05'},
    @EndDateEnd DATETIME = {d '2009-10-22'}

SELECT
    COUNT_BIG(*)
FROM dbo.SomeDateTable AS sdt
WHERE
    sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    AND sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd

    此查询使用变量，因此通常优化器会猜测选择性和分布，从而得到81 行的猜测基数估计。事实上，查询产生了 2076 行，这在更复杂的示例中可能很重要。

    在 SQL Server 2008 SP1 CU5 或更高版本（或 R2 RTM CU1）上，我们可以利用参数嵌入优化来获得更好的估计，只需添加OPTION (RECOMPILE)到SELECT上面的查询。这会导致在批处理执行之前进行编译，从而使 SQL Server 可以“查看”实际参数值并针对这些值进行优化。通过此更改，估计值提高到468 行（尽管您确实需要检查运行时计划才能看到这一点）。这个估计比 81 行要好，但仍然没有那么接近。跟踪标志 2301启用的建模扩展在某些情况下可能会有所帮助，但不适用于此查询。

    问题是两个范围搜索限定的行重叠的地方。在优化器的成本计算和基数估计组件中所做的简化假设之一是谓词是独立的（因此，如果两者都有 50% 的选择性，则假设应用两者的结果符合 50% 的 50% = 25% 的行）。如果这种相关性存在问题，我们通常可以使用多列和/或过滤统计信息来解决它。对于具有未知起点和终点的两个范围，这变得不切实际。这就是我们有时不得不求助于将查询重写为恰好产生更好估计的形式的地方：

    SELECT COUNT(*) FROM
(
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE 
        sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    INTERSECT
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt 
    WHERE
        sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd
) AS intersected (id)
OPTION (RECOMPILE)

    这种形式恰好产生 2110 行的运行时估计值（实际为 2076 行）。除非您启用了 TF 2301，在这种情况下，更高级的建模技术会看穿这个技巧并产生与以前完全相同的估计值：468 行。

    有一天，SQL Server 可能会获得对时间间隔的原生支持。如果这带有良好的统计支持，开发人员可能会害怕调优这样的查询计划。

    • 32

  2. 我不知道一种对所有数据分布都快速的解决方案，但如果您的所有范围都很短，我们通常可以加快速度。例如，如果范围短于 1 天，则不要使用此查询：

    SELECT  TaskId ,    
        TaskDescription ,
        StartedAt ,    
        FinishedAt    
FROM    dbo.Tasks    
WHERE   '20101203' BETWEEN StartedAt AND FinishedAt

    我们可以再添加一个条件：

    SELECT  TaskId ,    
        TaskDescription ,
        StartedAt ,    
        FinishedAt    
FROM    dbo.Tasks    
WHERE   '20101203' BETWEEN StartedAt AND FinishedAt
    AND StartedAt >= '20101202'
    AND FinishedAt <= '20101204' ;

    因此，查询将只扫描两天的范围，而不是扫描整个表，这样更快。如果范围可能更长，我们可以将它们存储为更短的序列。此处的详细信息：在约束的帮助下调整 SQL 查询

    • 7

相关问题