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开发者_Go百科 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.

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.

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)

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

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.

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

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.

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

OPTION (RECOMPILE)

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