Posted by Gert-Jan Strik on 11/17/05 23:55

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

• Next in forum: Re: Simple SQL Query (To Neglect time from DateTime DataType)
• Prev in forum: Simple SQL Query (To Neglect time from DateTime DataType)
• Thread view: Re: Optimizing Stored Procedure with Datetime parameter

[Reply to this message]