Search code examples
sqlsql-server-2000

SQL query and datetime parameter takes long time to execute

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.

Solution

  • 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)