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

Question

我在 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子句中，但我需要将其转换为日期才能进行过滤。正确的方法是什么？

Answer 1

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

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

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

"幸运的是"，您的数据似乎在样式代码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};

Answer 2

你可以这样使用来获得更好的性能：

SELECT TOP 10 *
FROM Table
WHERE cast(DATE as date) BETWEEN '2020-07-04' AND '2020-07-04' and cast(DATE as time) BETWEEN '00:00:00' AND '23:59:59'

如果您想搜索一整天，则无需包括时间部分。