Posted by paulmac106 on 11/17/05 01:26

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

• Next in forum: Re: Optimal search for the nearest date
• Prev in forum: Re: Indexes being improperly used when selecting data through a view
• Thread view: Optimizing Stored Procedure with Datetime parameter

[Reply to this message]