查询使用日期表达式运行缓慢，但使用字符串文字运行速度快 [英] Query runs slow with date expression, but fast with string literal

问题描述

我正在 SQL Server 2008 中运行具有以下条件的查询.

I am running a query with below condition in SQL Server 2008.

Where FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE)  

查询需要永远运行上述条件，但如果只是说

Query takes forever to run with above condition, but if just say

Where FK.DT = '2013-05-01' 

它在 2 分钟内运行良好.FK.DT 键只包含当月起始数据的值.

it runs great in 2 mins. FK.DT key contains values of only starting data of the month.

任何帮助，我只是不知道为什么会发生这种情况.

Any help, I am just clueless why this is happening.

推荐答案

这可能会更好:

Where FK.DT = cast(getdate() + 1 - datepart(day, getdate()) as date)

除非您使用跟踪标志 4199 运行，否则 一个影响基数估计的错误.在撰写本文时

Unless you are running with trace flag 4199 on there is a bug that affects the cardinality estimates. At the time of writing

SELECT DATEADD(m, DATEDIFF(m, getdate(), 0), 0), 
       DATEADD(m, DATEDIFF(m, 0, getdate()), 0)

退货

+-------------------------+-------------------------+
| 1786-06-01 00:00:00.000 | 2013-08-01 00:00:00.000 |
+-------------------------+-------------------------+

问题在于问题中的谓词在推导基数估计时使用第一个日期而不是第二个日期.所以对于下面的设置.

The bug is that the predicate in the question uses the first date rather than the second when deriving the cardinality estimates. So for the following setup.

CREATE TABLE FK
(
ID INT IDENTITY PRIMARY KEY,
DT DATE,
Filler CHAR(1000) NULL,
UNIQUE (DT,ID)
)

INSERT INTO FK (DT)
SELECT TOP (1000000) DATEADD(m, DATEDIFF(m, getdate(), 0), 0)
FROM master..spt_values o1, master..spt_values o2
UNION ALL
SELECT               DATEADD(m, DATEDIFF(m, 0, getdate()), 0)

查询 1

SELECT COUNT(Filler)
FROM FK
WHERE FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) AS DATE)  

估计匹配的行数将为 100,000.这是与日期 '1786-06-01' 匹配的数字.

Estimates that the number of matching rows will be 100,000. This is the number that match the date '1786-06-01'.

但是以下两个查询

SELECT COUNT(Filler)
FROM FK
WHERE FK.DT = CAST(GETDATE() + 1 - DATEPART(DAY, GETDATE()) AS DATE)

SELECT COUNT(Filler)
FROM FK
WHERE FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) AS DATE)  
OPTION (QUERYTRACEON 4199)

给出这个计划

由于更准确的基数估计，该计划现在只执行单个索引查找而不是完整扫描.

Due to the much more accurate cardinality estimates the plan now just does a single index seek rather than a full scan.

这篇关于查询使用日期表达式运行缓慢，但使用字符串文字运行速度快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！