Query Runs Slow with Date Expression, But Fast with String Literal

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

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)  

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

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

SQL Server Query: Fast with Literal but Slow with Variable

Probably it is because in the parameter case, the optimizer cannot know that the value is not null, so it needs to create a plan that returns correct results even when it is. If you have SQL Server 2008 SP1 you can try adding OPTION(RECOMPILE) to the query.

Why DATEADD slows down SQL query?

For createdate BETWEEN '2014-02-15 03:34:16' AND '2014-02-15 03:34:18' the literal values can be looked up in the column statistics to estimate the number of rows that will match.

The values of variables are not sniffed except if you use option (recompile) so SQL Server will just use heuristics to guess a number.

Presumably the plan that is derived from using the first number is different from that from using the second number.

e.g. One estimates fewer rows and uses a non covering index with lookups and the other a full scan as the estimated number of rows is above the tipping point where this option is considered cheaper.

Query extremely slow in code but fast in SSMS

Your code in SSMS is not the same code you run in your application. This line in your application adds a NVARCHAR parameter:

 ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);

while in the SSMS script you declare it as VARCHAR:

declare @clientID varchar(200)

Due to the rules of Data Type Precedence the Where client_id = @clientID expression in your query is not SARG-able where @clientID is of type NVARCHAR (I'm making a leap of faith and assume that client_id column is of type VARCHAR). The application thus forces a table scan where the SSMS query can do a quick key seek. This is a well know and understood issue with using Parameters.AddWithValue and has been discussed in many articles before, eg. see How Data Access Code Affects Database Performance. Once the problem is understood, the solutions are trivial:

• add parameters with the constructor that accepts a type: Parameters.Add("@clientID", SqlDbType.Varchar, 200) (and do pass in the explicit length to prevent cache pollution, see Query performance and plan cache issues when parameter length not specified correctly

• or cast the parameter in the SQL text: where client_id = cast(@clientID as varchar(200)).

The first solution is superior because it solves the cache pollution problem in addition to the SARG-ability problem.

I would also recommend you read Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

---

Related Topics