SQL查询和datetime参数执行时间较长

I have a query which takes datetime as a parameter, what we have observed is that if you supply datetime parameter through a variable, Query takes 2 -3 times more time to execute than if you directly hardcode the parameter, Is there any reason or solution to it

我有一个以datetime为参数的查询，我们观察到，如果您通过一个变量提供datetime参数，那么查询执行所需的时间是直接对参数进行硬编码的时间的2 -3倍，是否有任何原因或解决方案

Following query takes around 5 mins to return the result

接下来的查询大约需要5分钟才能返回结果

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

Select * from TempTable where effdate = @Date

While as

而作为

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

it returns in 10–20 sec

它的回报是10-20秒。

It is not always that i would have index on column using which i want to do seach.

并不是所有的列上都有索引我想用它来做seach。

As recommended by kevchadders, i saw a huge difference in execution plan. Query with date variable was doing clustered index scan and the other one was doing index Seek.

根据kevchadders的推荐，我发现执行计划有很大的不同。带日期变量的查询进行聚集索引扫描，另一个进行索引搜索。

6 个解决方案

#1

I've seen this before, and got around it by using a parameter table rather than a variable.

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

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)

#2

The usual suspect is a datatype mismatch, meaning the column is smalldatetime or varchar. "datetime" has a higher precedence so the column will be converted.

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

#3

Have you looked at the Execution Plan on both to see if that brings up any clues?

你有没有看一下执行计划，看看有没有发现什么线索?

#4

You should look at the execution plan of the queries to see if there is any difference. They should look exactly the same, in that case there is no difference in the execution of the queries, and any performance difference is due to what queries the database has cached since before.

您应该查看查询的执行计划，看看是否有任何差异。它们应该看起来完全相同，在这种情况下，查询的执行没有差异，任何性能差异都是由于数据库以前缓存的查询。

Even 30-40 seconds is a lot for such a simple query. If you have an index on the field, you should get the result in a few seconds even for a very large table.

对于这样一个简单的查询，甚至30-40秒都是很多的。如果您在字段上有一个索引，那么您应该在几秒钟内得到结果，即使是一个非常大的表。

For the actual query you should of course specify the fields that you want returned instead of using "select *". By only returning the data that you actually need, you can reduce the amount of data sent from the database server. In this query for example you know what the value of the effdate field will be for all rows in the result, so there is no need to return it.

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

#5

With such simple query, the return time should be much, MUCH lower. Try running the query with EXPLAIN, i.e. EXPLAIN Select * from TempTable where effdate = '01/01/2009'. If there's no indication of an index used, you should add one ( see the web for a tutorial on query optimization ).

对于这样简单的查询，返回时间应该要少得多。尝试使用EXPLAIN运行查询，例如，EXPLAIN Select * from TempTable，其中effdate = '01/01/2009'。如果没有使用索引的指示，您应该添加一个(有关查询优化的教程，请参阅web)。

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

It's not exactly clear to me why the variable takes longer, but having an index should speed up the query enough to make the difference negligible.

我不太清楚为什么这个变量要花更长的时间，但是有一个索引应该可以使查询速度足够快，从而使差异可以忽略不计。

#6

-1

This could be a 'parameter sniffing' problem. Try including the line:

这可能是一个“参数嗅探”问题。试包括:

OPTION (RECOMPILE)

at the end of your SQL query.

在SQL查询的末尾。

There is an article here explaining what parameter sniffing is: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

这里有一篇文章解释了什么是参数嗅探:http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-_sniffing.aspx。

#1