主页 / dba / 问题 / 51340
Accepted
Matthew
Asked: 2013-10-11 10:42:20 +0800 CST

我应该使用强制转换或范围将日期时间加入日期吗？

  • 772

这个问题是从这里提出的优秀问题的起飞：

迄今为止的演员阵容是可悲的，但这是个好主意吗？

就我而言，我不关心该WHERE子句，而是加入一个具有类型列的事件表DATE

一个表有DATETIME2，另一个有DATE......所以我可以有效地JOIN使用 aCAST( AS DATE)或者我可以使用“传统”范围查询（> = date AND < date+1）。

我的问题是哪个更可取？这些DATETIME值几乎永远不会与谓词DATE值匹配。

我希望保持在 2M 行的数量级上，DATETIME并且低于 5k DATE（如果这个考虑有所不同）

我是否应该期望与JOIN使用该WHERE子句的行为相同？我应该更喜欢哪一个来保持扩展性能？MSSQL 2012 的答案是否会改变？

我的通用用例是将事件表视为日历表

SELECT
    events.columns
    ,SOME_AGGREGATIONS(tasks.column)
FROM
    events
LEFT OUTER JOIN
    tasks
        --This appropriately states my intent clearer
        ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn 
        --But is this more effective/scalable?
        --ON tasks.datetimecolumn >= events.datecolumn 
        --AND tasks.datetimecolumn < DATEADD(day,1,events.datecolumn)
GROUP BY
    events.columns
sql-server-2008-r2 sql-server-2012

2 个回答

  1. Best Answer

    “这取决于”。

    =迄今为止，谓词的一个优点cast是连接可以是散列或合并。范围版本将强制执行嵌套循环计划。

    如果没有有用的索引可以搜索datetimecolumn，tasks这将产生重大影响。

    设置问题中提到的5K/200万行测试数据

    CREATE TABLE events
  (
     eventId    INT IDENTITY PRIMARY KEY,
     datecolumn DATE NOT NULL,
     details    CHAR(1000) DEFAULT 'D'
  )

INSERT INTO events
            (datecolumn)
SELECT TOP 5000 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY @@SPID), GETDATE())
FROM   spt_values v1,
       spt_values v2

CREATE TABLE tasks
  (
     taskId         INT IDENTITY PRIMARY KEY,
     datetimecolumn DATETIME2 NOT NULL,
     details        CHAR(1000) DEFAULT 'D'
  );

WITH N
     AS (SELECT number
         FROM   spt_values
         WHERE  number BETWEEN 1 AND 40
                AND type = 'P')
INSERT INTO tasks
            (datetimecolumn)
SELECT DATEADD(MINUTE, number, CAST(datecolumn AS DATETIME2))
FROM   events,
       N

    然后开机

    SET STATISTICS IO ON;
SET STATISTICS TIME ON;

    并尝试CAST版本

    SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn
GROUP  BY events.eventId

    7.4秒完成

    Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 1, logical reads 28679
Table 'events'. Scan count 1, logical reads 719

   CPU time = 3042 ms,  elapsed time = 7434 ms.

    来自连接并进入连接的估计行数GROUP BY太少（5006.27 与实际 2,000,000）并且哈希聚合溢出到tempdb

    计划一

    尝试范围谓词

    SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datetimecolumn >= events.datecolumn
            AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)
GROUP  BY events.eventId

    缺少相等谓词会强制执行嵌套循环计划。由于没有有用的索引来支持此查询，它别无选择，只能扫描 200 万行表 5,000 次。

    在我的机器上给出了一个并行计划，最终在 1 分 40 秒后完成。

    Table 'tasks'. Scan count 4, logical reads 143390000
Table 'events'. Scan count 5, logical reads 788
Table 'Worktable'. Scan count 0, logical reads 0
  CPU time = 368193 ms,  elapsed time = 100528 ms.

    这次从联接出来并进入聚合的行数被严重高估了（估计为 124,939,000，实际为 2,000,000）

    计划 2

    在更改表后重复实验以使相应的日期/时间列成为聚集主键更改结果。

    两个查询最终都选择了嵌套循环计划。CASTas版本给出了一个在 4.5 秒内完成的DATE串行版本和一个在经过时间 1.1 秒内完成的并行计划，CPU 时间为 3.2 秒。

    应用于MAXDOP 1第二个查询以使数字更容易比较返回以下内容。

    查询 1

    Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 5000, logical reads 78137
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3167 ms,  elapsed time = 4497 ms.

    查询 2

    Table 'tasks'. Scan count 5000, logical reads 49440
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3042 ms,  elapsed time = 3147 ms.

    查询 1 估计有 5006.73 行从连接中出来，散列聚合tempdb再次溢出。

    查询 2 再次被高估（这次是 120,927,000）。

    这两个结果之间的另一个明显区别是范围查询看起来像是设法以tasks某种方式更有效地进行搜索。仅阅读49,440页面与78,137.

    转换为日期版本所寻找的范围是从内部函数派生的GetRangeThroughConvert。该计划显示了一个关于 的残差谓词CONVERT(date,[dbo].[tasks].[datetimecolumn],0)= [dbo].[events].[datecolumn]。

    如果查询 2 更改为

       LEFT OUTER JOIN tasks
     ON tasks.datetimecolumn > DATEADD(day, -1, events.datecolumn)
        AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)

    然后读取次数变得相同。该CAST AS DATE版本使用的动态搜索读取不必要的行（两天而不是一天），然后将它们与剩余谓词一起丢弃。

    另一种可能性是重组表以将date和time组件存储在不同的列中。

    CREATE TABLE [dbo].[tasks](
    [taskId] [int] IDENTITY(1,1) NOT NULL,
    [datecolumn] date NOT NULL,
    [timecolumn] time NOT NULL,
    [datetimecolumn]  AS DATEADD(day, DATEDIFF(DAY,0,[datecolumn]), CAST([timecolumn] AS DATETIME2(7))),
    [details] [char](1000) NULL,
PRIMARY KEY CLUSTERED 
(
    [datecolumn] ASC,
    [timecolumn] ASC
))

    datetimecolumn可以从组件部分派生出来，这对行大小没有影响（因为date+time(n)的宽度与 的宽度相同datetime2(n)）。（例外情况是，如果附加列增加了 的大小NULL_BITMAP）

    然后查询是一个直接的=谓词

    SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datecolumn = events.datecolumn
GROUP  BY events.eventId

    这将允许表之间的合并连接，而无需任何排序。尽管对于这些表大小，无论如何都选择了嵌套循环连接，其统计信息如下。

    Table 'tasks'. Scan count 5000, logical reads 44285
Table 'events'. Scan count 1, logical reads 717
   CPU time = 2980 ms,  elapsed time = 3012 ms.

    除了可能允许不同的逻辑连接类型将date单独存储为前导索引列之外，还可能有利于其他查询，tasks例如按日期分组。

    至于为什么谓词比具有相同嵌套循环计划（vs ）的版本=显示更少的逻辑读取，这似乎与预读机制有关。tasks> <=44,28549,440

    打开跟踪标志652将范围版本的逻辑读取减少到与等于版本的逻辑读取相同。

    • 6

  2. 我同意 Martin 的观点，即使用这种方法与日期范围方法相比，基数估计可能会受到影响。我还要补充一点，使用CONVERT(DATE并且仍然获得 sargability 可能意味着其他人阅读代码或从中学习通常对列使用函数是一个好主意，特别是当列被索引时。由于这是唯一这是可行的异常，在所有其他情况下，这实际上会在可能进行搜索时强制进行扫描，我认为使用除了代码作者之外没有任何实际好处的异常不是一个好习惯，即使那是短暂的——你可以节省几秒钟来写一个更简洁的表达式，而且你只需要做一次。在回答问题时，我一直面临同样的反对意见——我看到其他人发布的答案包含不良习惯，例如varchar不冗长的声明，我经常发表评论。我听到的借口是它在这方面工作得很好案例，但这不是重点——人们从这个案例中学习，并将他们学到的知识应用到其他可能效果不佳的案例中。它甚至可能在相同的情况下中断 - 例如，假设您以后想在几周或半天之类的时间加入，您将需要使用不同的数据类型，您可能会失去您认为获得的好处。

    对于 INNER JOIN，使用 WHERE 子句与 ON 子句没有区别。但是，类似地，我更愿意在 ON 子句中保留加入标准，并在 WHERE 子句中保留过滤标准。当然，如果您谈论的是 OUTER JOIN，情况就会发生变化，因为某些条件的放置可能会改变语义。

    我会这样写你的查询（我会小心你使用虚构的“单词”性能，因为它可能会引起大多数人的滑稽凝视）：

    LEFT OUTER JOIN
  dbo.tasks -- always use schema prefix!
  ON tasks.datetimecolumn >= events.datecolumn
    AND tasks.datetimecolumn < DATEADD(DAY, 1, events.datecolumn)

    当然，您应该对此进行测试以查看它对DATEADD()事件表的影响。因为听起来那是一个很大的小表，所以我不认为效果会很大，但检查一下不会有什么坏处。

    • 4

相关问题