By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,714 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,714 IT Pros & Developers. It's quick & easy.

Optimizing Stored Procedure with Datetime parameter

P: n/a
Hi,

When I pass a date time parameter the stored procedure takes about 45
seconds, when I hard code the parameter it returns in 1 second. How can
I rewrite my stored procedure?

@createddatelower datetime

WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )
AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45
seconds)

vs.

WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') AND
dbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)

thanks for your help,
Paul

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Do you get the same (correct) result with both queries?

Nov 23 '05 #2

P: n/a
Yes, the result is the same

Nov 23 '05 #3

P: n/a
I've read that perhaps the sp is not using the index. The table i'm
searching is a joined table in the sp...Do you know how i can force it
to use the index? Then name of the index is IndexDate

thanks,
Paul

Nov 23 '05 #4

P: n/a
>Do you know how i can force it to use the index?

Check out "forceplan" as in:

set forceplan on

Nov 23 '05 #5

P: n/a
Paul,

Your stored procedure could benefit from parameter sniffing if you
change the local variable into a parameter, as in:

CREATE PROCEDURE MyProcedure (@createddatelower datetime) AS
...
WHERE eventdate > dateadd(day,-7,@createddatelower)
...

EXEC MyProcedure '20051115'

HTH,
Gert-Jan

paulmac106 wrote:

Hi,

When I pass a date time parameter the stored procedure takes about 45
seconds, when I hard code the parameter it returns in 1 second. How can
I rewrite my stored procedure?

@createddatelower datetime

WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )
AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45
seconds)

vs.

WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') AND
dbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)

thanks for your help,
Paul

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.