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.