时间戳

首页 > 解决方案 > 为什么在 WHERE 子句中使用 CONVERT(DATETIME, [date], [format]) 需要这么长时间？

sql - 为什么在 WHERE 子句中使用 CONVERT(DATETIME, [date], [format]) 需要这么长时间？

问题描述

我在 100M 的数据集上运行以下代码来测试一些东西，然后我最终将整个范围（不仅仅是前 10 名）加入另一个表中以使其更小。

SELECT TOP 10 *
    FROM Table
    WHERE CONVERT(datetime, DATE, 112) BETWEEN '2020-07-04 00:00:00' AND '2020-07-04 23:59:59'

该表不是我的，而是客户的，所以很遗憾，我不对列的数据类型负责。DATE 列以及其余数据位于varchar. 至于BETWEEN条款中的日期，我只是放在一个比较小的范围内进行测试。

我听说CONVERT不应该在WHERE子句中，但我需要将其转换为日期才能过滤。解决这个问题的正确方法是什么？

标签： sqlsql-servertsql

解决方案

在这里总结我的评论，因为他们是“二等公民”，因此可以被删除。

CONVERT首先，您的查询速度慢的原因是DATE您的WHERE. 将函数应用于您的列WHERE几乎总是会使您的查询不可搜索（有一些例外，但这并不是一个好主意）。因此，必须扫描整个表以查找适用于您的WHERE; 它不能使用索引来帮助它。

因此，真正的问题是您将日期（和时间）值作为非日期（和时间）数据类型存储在表中；大概是一个(n)varchar。事实上，这是一个主要的设计缺陷，需要修复。字符串类型值未验证为有效日期，因此有人可以轻松插入“日期”'20210229'甚至20211332'. 修复设计不仅可以阻止这种情况，还可以使您的数据更小（adate大小为 3 个字节，avarchar(8)为 10 个字节），并且您可以将强类型日期和时间值传递给您的查询，这将是 SARGable。

“幸运的是”您的数据显示在样式代码 112 中，即yyyyMMdd; 这至少意味着日期的顺序与强类型日期（和时间）数据类型相同。这意味着以下查询将起作用并返回您想要的结果：

SELECT TOP 10 * --Ideally don't use * and list your columns properly
FROM dbo.[Table]
WHERE [DATE] >= '20210704' AND [DATE] < '20210705'
ORDER BY {Some Column};

推荐阅读