我有一个T-sql语句,我正在对一个有很多行的表运行.我看到一些奇怪的行为.将DateTime列与预先计算的值进行比较比将每行与基于GETDATE()函数的计算进行比较要慢.
以下sql需要8秒：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @TimeZoneOffset int = -(DATEPART("HH",GETUTCDATE() - GETDATE()))
DECLARE @LowerTime DATETIME = DATEADD("HH",ABS(@TimeZoneOffset),CONVERT(VARCHAR,GETDATE(),101) + ' 17:00:00')
SELECT TOP 200 Id,EventDate,Message
FROM Events WITH (NOLOCK)
WHERE EventDate > @LowerTime
GO
这个替代奇怪地立刻返回：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT TOP 200 Id,Message FROM Events WITH (NOLOCK) WHERE EventDate > GETDATE()-1 GO
为什么第二个查询要快得多？
编辑：我更新了sql以准确反映我正在使用的其他设置
解决方法
经过大量的阅读和研究,我发现这里的问题是参数嗅探. sql Server尝试根据where子句确定如何最好地使用索引,但在这种情况下,它并没有做得很好.
请参阅以下示例：
慢版：
declare @dNow DateTime Select @dNow=GetDate() Select * From response_master_Incident rmi Where rmi.response_date between DateAdd(hh,-2,@dNow) AND @dNow
快速版：
Select * From response_master_Incident rmi Where rmi.response_date between DateAdd(hh,GetDate()) AND GetDate()
“快速”版本的运行速度比慢速版本快10倍. Response_Date字段已编制索引,并且是DateTime类型.
解决方案是告诉sql Server如何最好地优化查询.修改示例如下以包含OPTIMIZE选项导致它使用与“快速版本”相同的执行计划.这里的OPTMIZE选项明确告诉sql server将本地@dNow变量视为日期(好像将它声明为DateTime不够：s)
这样做时要小心,因为在更复杂的WHERE子句中,最终可能会使查询的性能比sql Server自身的优化更差.
declare @dNow DateTime SET @dNow=GetDate() Select ID,response_date,call_back_phone from response_master_Incident rmi where rmi.response_date between DateAdd(hh,@dNow) AND @dNow -- The optimizer does not know too much about the variable so assumes to should perform a clusterd index scann (on the clustered index ID) - this is slow -- This hint tells the optimzer that the variable is indeed a datetime in this format (why it does not know that already who knows) OPTION(OPTIMIZE FOR (@dNow = '99991231'));
