Why Are SQL Server 2008 Date Expression Queries Slower Than String Literal Queries?

Date Expression Queries: Faster with String Literals

Queries utilizing date expressions within their conditions, as seen in the following example, often exhibit slow performance in SQL Server 2008:

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

However, it has been observed that replacing the date expression with a string literal, such as '2013-05-01', results in significantly faster query execution. This discrepancy has perplexed many developers.

The performance disparity arises from an internal bug in SQL Server 2008 that affects the way it estimates the cardinality of results when date expressions are used. When presented with a date expression like the one in the question, SQL Server incorrectly assumes that it represents a constant value corresponding to the first date in the specified month. This leads to inaccurate cardinality estimates, resulting in inefficient query plans.

Using a string literal for the date value, on the other hand, forces SQL Server to confront the actual value at runtime and derive more precise cardinality estimates. This allows the optimizer to generate more optimal query plans, leading to faster execution.

To bypass this bug, one can use the following alternative expression instead:

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

This expression returns a constant date value representing the first day of the current month, which aligns with the intended behavior of the query. With this substitution, the optimizer can accurately estimate the cardinality and produce a more efficient query plan, resolving the performance bottlenecks associated with date expressions.

The above is the detailed content of Why Are SQL Server 2008 Date Expression Queries Slower Than String Literal Queries?. For more information, please follow other related articles on the PHP Chinese website!