SQL 查询和日期时间参数需要很长时间才能执行 [英] SQL query and datetime parameter takes long time to execute

问题描述

我有一个以日期时间为参数的查询，我们观察到，如果您通过变量提供日期时间参数，则 Query 执行时间比直接硬编码参数要多 2 -3 倍，是否有原因或解决方法

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

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

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

虽然

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

它在 10-20 秒后返回

it returns in 10–20 sec

我并不总是在要搜索的列上建立索引.

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

在 kevchadders 的推荐下，我看到了执行计划的巨大差异.带有日期变量的查询是在做聚集索引扫描，另一个是在做索引搜索.

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.

推荐答案

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

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)

这篇关于SQL 查询和日期时间参数需要很长时间才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！