CODEWITHSUNDEEP
sqlsql-server-2000
rsapru
rsapru

Reputation: 698

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.

Upvotes: 4

Views: 6355

Answers (6)

ninjaPixel
ninjaPixel

Reputation: 6382

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

Upvotes: -1

cindi
cindi

Reputation: 4791

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)

Upvotes: 2

gbn
gbn

Reputation: 432210

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.

Upvotes: 3

Guffa
Guffa

Reputation: 700262

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.

Upvotes: 1

Piskvor left the building
Piskvor left the building

Reputation: 92752

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.

Upvotes: 0

kevchadders
kevchadders

Reputation: 8335

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

Upvotes: 1

Related Questions