SQL查询和日期时间参数需要很长时间来执行

Question

我有一个查询，它将datetime作为参数，我们观察到的是，如果您通过变量提供datetime参数，Query执行的时间比如果你直接硬编码参数，是否有任何原因或解决方案

下面的查询需要大约5分钟返回结果

Declare @Date as DateTime 
Set @Date = '01/01/2009' 

Select * from TempTable where effdate = @Date 

虽然作为

Select * from TempTable where effdate = '01/01/2009' 

它在10-20秒内返回

这并不总是我有索引列使用我想要做的搜索。

按照kevchadders的建议，我看到了执行计划的巨大差异。带日期变量的查询正在进行聚簇索引扫描，另一个正在做索引查找。

Answer 1

我以前见过这个，通过使用参数表而不是变量来解决它。

if object_id('myParameters') is not null drop table myParameters 
Select cast('1996-05-01' as datetime) as myDate into myParameters 

Select * from TempTable where effdate = (select max(myDate) from myParameters) 

Answer 2

你看过两个执行计划，看看是否有任何线索？

Answer 3

有了这样简单的查询，返回时间应该很多，很低。尝试使用EXPLAIN运行查询，即EXPLAIN Select * from TempTable where effdate = '01/01/2009'。如果没有指示使用索引，则应该添加一个（查询优化中的see the web for a tutorial）。

CREATE INDEX 'date_index' ON `TempTable` (`effdate`); 

为什么变量需要更长的时间，但有一个指标应该足够加快查询，以赚取差价可以忽略它不完全清楚，我。

Answer 4

您应该查看查询的执行计划以查看是否有任何区别。它们应该看起来完全一样，在这种情况下，查询的执行没有任何区别，并且任何性能差异都是由于数据库之前从此之后缓存了哪些查询。

即使30-40秒对于这样一个简单的查询来说也是很多的。如果您在该字段中有一个索引，则即使对于一个非常大的表格，您也应该在几秒钟内得到结果。

对于实际查询，您当然应该指定要返回的字段而不是使用“select *”。通过仅返回实际需要的数据，可以减少从数据库服务器发送的数据量。例如，在此查询中，您知道结果中所有行的effdate字段的值是什么，因此无需将其返回。

Answer 5

通常的嫌疑犯是数据类型不匹配，这意味着该列是smalldatetime或varchar。 “datetime”具有更高的优先级，所以该列将被转换。

Answer 6

这可能是一个'参数嗅探'问题。尝试包括行：

OPTION (RECOMPILE) 

在您的SQL查询结束。

有文章在这里解释什么参数嗅探是：http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx