当对约束进行硬编码时，WHERE子句中的SQL Server性能不同

Question

我在SQL Server 2014上遇到了一个非常奇怪的性能问题。我试图根据where子句来选择一些值，该子句约束特定日期的记录。当我硬编码日期时，查询很快（3秒），而当我尝试参数化查询时，查询速度很慢。

为了以防万一，时间戳上有一个索引。以下是不同的查询及其性能。

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!

请参阅下面TT的答案，了解除了构建查询之外的其他解决方案

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

Answer 1

首先，我将使用DATETIME类型作为参数。

其次，你很有可能遇到参数嗅探。使用查询提示可以避免这种情况的两种方法：

• 向查询添加查询提示OPTION(RECOMPILE)
• 向查询添加查询提示OPTION(OPTIMIZE FOR UNKNOWN)

第一个选项仅适用于不经常运行（>>1/sec）的查询，而在许多情况下，第二个选项是首选选项。

Answer 2

我找到了一个变通办法。我构造了查询并执行了它。如果你知道为什么性能不同，请告诉我。

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