当约束硬编码时，SQL Server在WHERE子句中性能不同

问题描述：

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

为了以防万一是相关的时间戳有一个索引。下面你会发现不同的查询和他们的表现。从TT

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!

请参阅回答下面的替代解决方案，除了构建查询

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

因为您正在转换为字符串......最好不要混合数据类型 –

什么类型是Timestamp？ varchar或datetime？尝试通过@开始/结束日期而不是@开始/结束日期 – gsharp

什么是时间戳列的类型？ – FDavidov

答

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

其次，您很有可能碰到参数嗅探。两种方法来回避这个使用Query Hints：

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

第一个选项是唯一合理的对未运行查询很多时候（>> 1 /秒），而第二种情况在很多情况下是首选。

OPTION（RECOMPILE）工作， – aragorn

OPTION（OPTIMIZE FOR UNKNOWN）没有。谢谢！ – aragorn

答

我发现周围的工作。我构建了查询并执行了它。如果你有任何想法，为什么表现不同请让我知道。

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]>='''[email protected]+''' AND [Timestamp]<'''[email protected]+''' 
' 
EXECUTE sp_executesql @Query

当约束硬编码时，SQL Server在WHERE子句中性能不同

相关推荐