两个日期列的SARGable WHERE子句

对于我来说，我有一个关于可保存性的有趣问题。在这种情况下，它是关于两个日期列之间的差异使用谓词。设置如下：

USE [tempdb]
SET NOCOUNT ON  

IF OBJECT_ID('tempdb..#sargme') IS NOT NULL
BEGIN
DROP TABLE #sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO #sargme
FROM sys.[messages] AS [m]

ALTER TABLE [#sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [#sargme] ([DateCol1], [DateCol2])

我经常看到的是这样的：

/*definitely not sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48;

...这绝对不可救药。它导致索引扫描，读取所有1000行，不好。估计行很臭。您永远不会将其投入生产。

如果能够实现CTE，那将是很好的，因为这将帮助我们使它变得更好，更易于维护从技术上讲。但是，没有，我们得到了与上层相同的执行计划。

/*would be nice if it were sargable*/
WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [#sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

当然，由于我们没有使用常量，因此该代码没有任何改变，甚至没有可节省一半。没有什么好玩的。相同的执行计划。

/*not even half sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

如果您感到幸运，并且遵循了连接字符串中的所有ANSI SET选项，则可以添加一个计算列，然后对其进行搜索...

ALTER TABLE [#sargme] ADD [ddiff] AS 
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [#sargme] AS [s]
WHERE [ddiff] >= 48

这将为您提供三个查询的索引查找。奇怪的是，我们向DateCol1添加48天。在DATEDIFF子句中使用WHERE的查询，在CTE中进行的查询以及在计算列上带有谓词的最终查询都为您提供了一个更好的计划，并且估计值也更好。 />
这带来了我一个问题：在单个查询中，是否存在一种可以执行此搜索的SARGable方法？

没有临时表，没有表变量，没有改变表结构，也没有视图。

我可以使用自联接，CTE，子查询或对数据进行多次传递。可以与任何版本的SQL Server一起使用。

避免计算列是人为的限制，因为我对查询解决方案的兴趣比其他任何事物都要重要。

#1 楼

只需快速添加它，使其作为答案即可存在（尽管我知道这不是您想要的答案）。

带索引的计算列通常是解决此类问题的正确方法。

：

使谓词成为可索引的表达式
允许创建自动统计信息以更好地估计基数
不需要在索引中占用任何空间基本表

要在最后一点清楚一点，在这种情况下不需要保留计算列：

-- Note: not PERSISTED, metadata change only
ALTER TABLE #sargme
ADD DayDiff AS DATEDIFF(DAY, DateCol1, DateCol2);

-- Index the expression
CREATE NONCLUSTERED INDEX index_name
ON #sargme (DayDiff)
INCLUDE (DateCol1, DateCol2);

现在查询：

SELECT
    S.ID,
    S.DateCol1,
    S.DateCol2,
    DATEDIFF(DAY, S.DateCol1, S.DateCol2)
FROM
    #sargme AS S
WHERE
    DATEDIFF(DAY, S.DateCol1, S.DateCol2) >= 48;

...给出以下简单计划：

如马丁·史密斯所说，如果您使用错误的set选项进行连接，则可以创建一个常规列并使用触发器维护计算值。

如果确实有要解决的问题，那么这一切都非常重要（除了代码挑战）当然，就像亚伦在回答中所说的那样。

这很有趣，但鉴于问题的限制，我不知道有什么方法可以合理地实现您想要的目标。似乎任何最佳解决方案都需要某种类型的新数据结构。我们最接近的是如上所述的非持久计算列上的索引提供的“函数索引”近似值。

#2 楼

冒着被SQL Server社区中一些知名人士嘲笑的风险，我要挺直地说，不。

为了使您的查询可保存，您必须基本上构造一个查询，该查询可以在索引的一系列连续行中查明起始行。使用索引ix_dates时，行不按DateCol1和DateCol2之间的日期差排序，因此您的目标行可以散布在索引中的任何位置。

自连接，多次通过等所有它们的共同点是至少包括一个索引扫描，尽管（嵌套循环）联接很可能会使用索引搜索。但是我看不到如何消除扫描。

为了获得更准确的行估计，没有关于日期差的统计信息。

接下来，相当丑陋的递归CTE构造从技术上消除了对整个表的扫描，尽管它引入了嵌套循环联接和（可能非常大）数量的索引查找。

DECLARE @from date, @count int;
SELECT TOP 1 @from=DateCol1 FROM #sargme ORDER BY DateCol1;
SELECT TOP 1 @count=DATEDIFF(day, @from, DateCol1) FROM #sargme WHERE DateCol1<=DATEADD(day, -48, {d '9999-12-31'}) ORDER BY DateCol1 DESC;

WITH cte AS (
    SELECT 0 AS i UNION ALL
    SELECT i+1 FROM cte WHERE i<@count)

SELECT b.*
FROM cte AS a
INNER JOIN #sargme AS b ON
    b.DateCol1=DATEADD(day, a.i, @from) AND
    b.DateCol2>=DATEADD(day, 48+a.i, @from)
OPTION (MAXRECURSION 0);

它会创建一个包含表中每个DateCol1的索引假脱机，然后对至少提前48天的DateCol1和DateCol2中的每个DateCol1和q4312079q执行索引搜索（范围扫描）。

更多IO，稍长一些执行时间，行估计仍然遥不可及，由于递归，并行化的可能性为零：我猜测如果您在相对较少的连续两个q4312079q中拥有大量值，则此查询可能有用搜索）。

#3 楼

我尝试了很多古怪的变体，但没有找到比您的任何一个更好的版本。主要问题在于，根据date1和date2的排序方式，索引看起来像这样。第一列将排成一条漂亮的架子，而它们之间的缝隙将变得非常锯齿。您希望它看起来更像一个漏斗，而不是它的实际方式：

Date1    Date2
-----    -------
*             *
*             *
*              *
 *       * 
 *        *
 *         *
  *      *
  *           *

我真的没有想到的办法可以使某个三角洲变得可寻找（或增量范围）两点之间。我的意思是执行一次查找并执行一次范围扫描，而不是针对每一行执行一次查找。在某些时候这将涉及扫描和/或排序，而这些都是您显然要避免的事情。太糟糕了，您不能在过滤的索引中使用像DATEADD / DATEDIFF这样的表达式，也不能执行任何可能的模式修改以允许对日期diff的乘积进行排序（例如在插入/更新时计算增量）。实际上，这似乎是扫描实际上是最佳检索方法的情况之一。

您说此查询没有意思，但是如果仔细看，这是迄今为止最好的一个（如果省略计算标量输出，效果会更好）：

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

原因是，与计算相比，避免DATEDIFF可能会节省一些CPU不仅是索引中的非前导键列，而且还避免了对datetimeoffset(7)的某些讨厌的隐式转换（不要问我为什么在那里，但是它们确实存在）。这是DATEDIFF版本：

<谓词> = （48）“>

这是没有DATEDIFF的那个：

<谓词> = dateadd（day，（48），[splunge]。[dbo]。[ sargme]。[DateCol1] as [s]。[DateCol1]）“>

当我将索引更改为仅包括DateCol2（和当同时存在两个索引时，SQL Server始终选择一个具有一个键的索引，而一个包含列与多键的索引）。对于此查询，由于无论如何我们都必须扫描所有行以查找范围，因此将第二个日期列作为键的一部分并以任何方式进行排序没有任何好处。而且，尽管我知道我们在这里无法寻求帮助，但是通过不对前导键列强制执行计算，而仅对第二列或包含的列执行计算，不会妨碍获得第一者的能力，这具有内在的感觉。 >
如果是我，而我放弃寻找可解决的解决方案，我知道我会选择哪种解决方案-使SQL Server完成最少工作量的解决方案（即使增量几乎不存在）。或者更好的是，我将放宽对架构更改等的限制。

所有这些有多重要？我不知道。我使表达到1000万行，并且所有上述查询变体仍在一秒钟之内完成。这是在笔记本电脑上的VM（已授予，带有SSD）上。

#4 楼

我想使WHERE子句可保留的所有方式都很复杂，并且感觉像朝着索引搜索的努力是最终目标而不是手段。因此，不，我认为（在实际中）不可能。

我不确定“不更改表结构”是否没有附加索引。这是一种完全避免索引扫描的解决方案，但是会导致很多单独的索引查找，即表中日期值的最小/最大范围中每个可能的DateCol1日期一个。（与Daniel的搜索结果不同，该搜索结果要求查找表中实际出现的每个不同的日期）。从理论上讲，它是并行b / c的候选者，它避免了递归。但老实说，很难看到一个数据分布，它比扫描和执行DATEDIFF还要快。（也许是一个非常高的DOP？）而且...代码很难看。我想这算是一项“心理锻炼”。

--Add this index to avoid the scan when determining the @MaxDate value
--CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([DateCol2]);
DECLARE @MinDate DATE, @MaxDate DATE;
SELECT @MinDate=DateCol1 FROM (SELECT TOP 1 DateCol1 FROM #sargme ORDER BY DateCol1 ASC) ss;
SELECT @MaxDate=DateCol2 FROM (SELECT TOP 1 DateCol2 FROM #sargme ORDER BY DateCol2 DESC) ss;

--Used 44 just to get a few more rows to test my logic
DECLARE @DateDiffSearchValue INT = 44, 
    @MinMaxDifference INT = DATEDIFF(DAY, @MinDate, @MaxDate);

--basic data profile in the table
SELECT [MinDate] = @MinDate, 
        [MaxDate] = @MaxDate, 
        [MinMaxDifference] = @MinMaxDifference, 
        [LastDate1SearchValue] = DATEADD(DAY, 0-@DateDiffSearchValue, @MaxDate);

;WITH rn_base AS (
SELECT [col1] = 0
        UNION ALL SELECT 0
        UNION ALL SELECT 0
        UNION ALL SELECT 0
),
rn_1 AS (
    SELECT t0.col1 FROM rn_base t0
        CROSS JOIN rn_base t1
        CROSS JOIN rn_base t2
        CROSS JOIN rn_base t3
),
rn_2 AS (
    SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM rn_1 t0
        CROSS JOIN rn_1 t1
),
candidate_searches AS (
    SELECT 
        [Date1_EqualitySearch] = DATEADD(DAY, t.rn-1, @MinDate),
        [Date2_RangeSearch] = DATEADD(DAY, t.rn-1+@DateDiffSearchValue, @MinDate)
    FROM rn_2 t
    WHERE DATEADD(DAY, t.rn-1, @MinDate) <= DATEADD(DAY, 0-@DateDiffSearchValue, @MaxDate)
    /* Of course, ignore row-number values that would result in a
       Date1_EqualitySearch value that is < @DateDiffSearchValue days before @MaxDate */
)
--select * from candidate_searches

SELECT c.*, xapp.*, dd_rows = DATEDIFF(DAY, xapp.DateCol1, xapp.DateCol2)
FROM candidate_searches c
    cross apply (
        SELECT t.*
        FROM #sargme t
        WHERE t.DateCol1 = c.date1_equalitysearch
        AND t.DateCol2 >= c.date2_rangesearch
    ) xapp
ORDER BY xapp.ID asc --xapp.DateCol1, xapp.DateCol2

#5 楼

最初由问题作者添加为问题的编辑的社区Wiki答案

让它坐了片刻，然后有一些非常聪明的人加入其中，我对此的初步想法似乎是正确的：没有理智的想法而不用增加计算或通过其他机制（即触发器）维护的列的方式编写此查询的SARGable方法。可能对任何人都不感兴趣。

首先，使用常规表而不是临时表重新运行安装程序

即使我知道他们的声誉，我想尝试多列统计信息。它们没用。
我想查看使用了哪些统计信息

这是新设置：

USE [tempdb]
SET NOCOUNT ON  

DBCC FREEPROCCACHE

IF OBJECT_ID('tempdb..sargme') IS NOT NULL
BEGIN
DROP TABLE sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO sargme
FROM sys.[messages] AS [m]

ALTER TABLE [sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [sargme] ([DateCol1], [DateCol2])

CREATE STATISTICS [s_sargme] ON [sargme] ([DateCol1], [DateCol2])

然后运行第一次查询时，它像以前一样使用ix_dates索引进行扫描。这里没有变化。这似乎是多余的，但请坚持我。

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48

再次运行CTE查询，仍然相同...

WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

好！再次运行非偶数可查询的查询：

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

现在添加计算列，然后重新运行所有三个列以及命中计算列的查询栏：

ALTER TABLE [sargme] ADD [ddiff] AS 
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [sargme] AS [s]
WHERE [ddiff] >= 48

如果你坚持我在这里，谢谢。这是该帖子中有趣的观察部分。

使用Fabiano Amorim运行带有未记录的跟踪标志的查询，以查看每个查询使用的统计信息非常酷。看到在创建计算列并建立索引之前没有计划触及统计对象，这似乎很奇怪。

哎呀，直到我只运行了几次并且得到了简单的参数化，即使只打了计算列的查询也没有碰到统计对象。因此，即使他们最初都扫描了ix_dates索引，他们仍然使用硬编码的基数估计（表的30％），而不是使用任何可用的统计信息对象。

让我大吃一惊的另一点是，当我仅添加非聚集索引时，查询计划全部扫描了HEAP，而不是在两个日期列上都使用非聚集索引。

感谢所有回答。你们都很棒。

编程黑洞网