Why is My SQL Server 2008 Query Slow When Using DateTime Expressions in the WHERE Clause?

Query Performance Discrepancy with DateTime Expression

A query experiencing significant slowdown when using a date expression in a WHERE clause compared to a string literal value poses a query performance dilemma. Let's delve deeper into the issue and seek a solution.

Root Cause

The performance gap arises due to a bug in SQL Server 2008, which affects the cardinality estimates when using a date expression in a WHERE clause. The bug results in incorrect estimates of the number of matching rows.

Optimized Query

To circumvent this bug, the following optimized query is suggested:

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

This expression returns the start of the current month, which is likely the desired selection criteria in this scenario.

Trace Flag 4199

Alternatively, trace flag 4199 can be enabled to force accurate cardinality estimates. With trace flag 4199 on, the following query will also produce the desired plan:

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

Example

For example, with a table called FK containing a column called DT with values representing the start of each month, the following two queries demonstrate the performance discrepancy:

-- Slow due to incorrect cardinality estimates
SELECT COUNT(Filler)
FROM FK
WHERE FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) AS DATE)  

-- Fast due to accurate cardinality estimates with trace flag 4199
SELECT COUNT(Filler)
FROM FK
WHERE FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) AS DATE)  
OPTION (QUERYTRACEON 4199)

With trace flag 4199 enabled, the second query will result in a significantly faster execution time due to the optimized query plan.

The above is the detailed content of Why is My SQL Server 2008 Query Slow When Using DateTime Expressions in the WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!