SQL查询和日期时间参数需要很长时间才能执行
我有一个以日期时间作为参数的查询，我们观察到，如果您通过变量提供日期时间参数，则查询执行时间比直接硬编码参数要多 2 -3 倍，有什么原因或解决方案下面
的查询大约需要 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 的建议，我看到执行计划存在巨大差异。使用日期变量的查询正在执行聚集索引扫描，另一个查询正在执行索引查找。
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
Following query takes around 5 mins to return the result
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
It is not always that i would have index on column using which i want to do 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
通常的怀疑是数据类型不匹配，这意味着该列是smalldatetime 或varchar。
“datetime”具有更高的优先级，因此该列将被转换。
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.
我以前见过这个，并通过使用参数表而不是变量来解决它。
I've seen this before, and got around it by using a parameter table rather than a variable.
您是否查看过两者的执行计划，看看是否能找到任何线索？
Have you looked at the Execution Plan on both to see if that brings up any clues?
您应该查看查询的执行计划以查看是否有任何差异。它们应该看起来完全相同，在这种情况下，查询的执行没有差异，任何性能差异都是由于数据库之前缓存的查询造成的。
对于这样一个简单的查询来说，即使 30-40 秒也很多了。如果您在字段上有索引，即使对于非常大的表，您也应该在几秒钟内获得结果。
对于实际查询，您当然应该指定要返回的字段，而不是使用“select *”。通过仅返回实际需要的数据，可以减少从数据库服务器发送的数据量。例如，在此查询中，您知道结果中所有行的 effdate 字段的值是什么，因此无需返回它。
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.
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.
通过如此简单的查询，返回时间应该要低得多。尝试使用
EXPLAIN运行查询，即EXPLAIN Select * from TempTable where effdate = '01/01/2009'。如果没有指示使用的索引，您应该添加一个 ( 在网上查看有关查询优化的教程）。我不太清楚为什么该变量需要更长的时间，但拥有索引应该足以加快查询速度，使差异可以忽略不计。
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 ).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.
这可能是“参数嗅探”问题。 添加以下行：
尝试在 SQL 查询末尾
。这里有一篇文章解释了什么是参数嗅探： http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
This could be a 'parameter sniffing' problem. Try including the line:
at the end of your SQL query.
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