SQL Server different performance in a WHERE clause when constraint is hardcoded

Question

I face a very weird performance issue on SQL Server 2014. I try to select some values based on a where clause which constraints records of a specific date. When I hardcode the date the query is fast (3 sec) while when I try to parametrize the query it is slow.

Just in case is relevant there is an index on timestamp. Below you will find the different queries and their performance.

SELECT *
FROM [DB].[schema].[table]
WHERE [Timestamp]>='2016-07-25' AND [Timestamp]<'2016-07-26'
--execution time 3 seconds!

SELECT *
FROM [DB].[schema].[table]
WHERE [Timestamp] BETWEEN '2016-07-25' AND '2016-07-26'
--execution time 3 seconds!

DECLARE @StartDate DATETIME
SET @StartDate = '2016-07-25'

DECLARE @StartDateString VARCHAR(10)
SET @StartDateString =CAST(FORMAT(@StartDate,'yyyy-MM-dd HH:mm:ss.fff') AS VARCHAR(10))

DECLARE @EndDateString VARCHAR(10)
SET @EndDateString =CAST(FORMAT(DATEADD(day,1,@StartDate),'yyyy-MM-dd HH:mm:ss.fff') AS VARCHAR(10))

SELECT *
FROM [DB].[schema].[table]
WHERE [Timestamp] BETWEEN @StartDateString AND @EndDateString
--execution time 30 minutes!

DECLARE @StartDate DATETIME
SET @StartDate = '2016-07-25'

DECLARE @EndDate DATETIME
SET @EndDate =DATEADD(day,1,@StartDate)

SELECT *
FROM [DB].[schema].[table]
WHERE [Timestamp] BETWEEN @StartDate AND @EndDate
--execution time 30 minutes!

See answer from TT below for an alternative solution besides constructing the query

DECLARE @StartDate DATETIME
SET @StartDate = '2016-07-25'

DECLARE @EndDate DATETIME
SET @EndDate =DATEADD(day,1,@StartDate)

SELECT *
FROM [DB].[schema].[table]
WHERE [Timestamp] BETWEEN @StartDate AND @EndDate
OPTION(RECOMPILE)
--execution time 3 sec

because you are casting to a string... better not to mix datatypes — Keith Hall, Jul 28 '16 at 06:21
what type is Timestamp? varchar or datetime? try to pass @start/enddate instead of @start/enddatestring — gsharp, Jul 28 '16 at 06:21
I tried also to pass a DATETIME variable in the where clause instead of a string. this was slow as well. See my edited post where I added an extra query — aragorn, Jul 28 '16 at 06:27

score 2 · Accepted Answer · answered Jul 28 '16 at 06:33

2

First off, I would use the DATETIME type for your parameters.

Second, there is a good chance you are running into Parameter Sniffing. Two ways to sidestep this using Query Hints:

Add query hint OPTION(RECOMPILE) to the query
Add query hint OPTION(OPTIMIZE FOR UNKNOWN) to the query

The first option is only reasonable for queries that aren't run very often (>> 1/sec), while the second is preferred in a lot of cases.

answered Jul 28 '16 at 06:33

TT.

15,774
6
47
88

OPTION(RECOMPILE) worked, – aragorn Jul 28 '16 at 06:41
OPTION(OPTIMIZE FOR UNKNOWN) didn't. Thanks! – aragorn Jul 28 '16 at 06:42

score 0 · Answer 2 · answered Jul 28 '16 at 06:25

I found a work around. I constructed the query and executed it. Still if you have any idea why the performance is different please let me know.

DECLARE @StartDate DATETIME
SET @StartDate = '2016-07-25'
DECLARE @StartDateString VARCHAR(10)
SET @StartDateString =CAST(FORMAT(@StartDate,'yyyy-MM-dd HH:mm:ss.fff') AS VARCHAR(10))
DECLARE @EndDateString VARCHAR(10)
SET @EndDateString =CAST(FORMAT(DATEADD(day,1,@StartDate),'yyyy-MM-dd HH:mm:ss.fff') AS VARCHAR(10))


 DECLARE @Query NVARCHAR(4000)
 SET @Query =
 '
 SELECT *
 FROM [DB].[schema].[table]
 WHERE [Timestamp]>='''+@StartDateString+''' AND [Timestamp]<'''+@EndDateString+'''
 '
 EXECUTE sp_executesql @Query

SQL Server different performance in a WHERE clause when constraint is hardcoded

2 Answers2