Query runs slow with date expression, but fast with string literal

by

This could work better:

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

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)

Returns

+-------------------------+-------------------------+
| 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)

Query 1

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

Plan 1

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

But both of the following queries

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)

Give this plan

Plan 2

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

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. Is there a way to access the “previous row” value in a SELECT statement?
  3. What this query does to create comma delimited list SQL Server?
  4. SQL Server – Return value after INSERT
  5. SQL NVARCHAR and VARCHAR Limits
  6. How to group time by hour or by 10 minutes
  7. SQL query to insert datetime in SQL Server
  8. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  9. Convert Xml to Table SQL Server
  10. SQL Server : dynamic pivot over 5 columns
  11. How to select the last record of a table in SQL?
  12. How to create table using select query in SQL Server?
  13. How do I remove redundant namespace in nested query when using FOR XML PATH
  14. SQL SERVER: Get total days between two dates
  15. T-SQL and the WHERE LIKE %Parameter% clause
  16. Custom aggregate function (concat) in SQL Server
  17. Which SQL query is faster? Filter on Join criteria or Where clause?
  18. UPDATE and REPLACE part of a string
  19. Improve SQL Server query performance on large tables
  20. Average of multiple columns
  21. Show all rows that have certain columns duplicated
  22. Select SQL Server database size
  23. Check if a parameter is null or empty in a stored procedure
  24. Cannot Resolve Collation Conflict
  25. Execute sp_executeSql for select…into #table but Can’t Select out Temp Table Data
  26. Display next event date
  27. Select rows where column value has changed
  28. Issues with SQL comparison and null values
  29. How do you read XML column in SQL Server 2008?
  30. Sql query for tree table

Leave a Comment