Posted by Gert-Jan Strik on 09/13/06 21:24

Google the phrase "parameter sniffing"...

Gert-Jan


ujjc001 wrote:
>
> two variables declared in my proc:
> @DATE_RANGE_START as datetime,
> @DATE_RANGE_END as datetime,
>
> When I execute my SP it takes 34 seconds.
>
> When I change the variables to:
> @DATE_RANGE_START1 as datetime,
> @DATE_RANGE_END1 as datetime,
>
> and add this to my sp:
> declare @DATE_RANGE_START datetime
> declare @DATE_RANGE_END datetime
> set @DATE_RANGE_START = @DATE_RANGE_START1
> set @DATE_RANGE_END = @DATE_RANGE_END1
> the SP runs in 9 seconds (which is expected)
>
> Passing in '1/1/01' and '1/1/07' respectivly.
>
> Everything else is equal and non-important to this problem.
> Why does it take 34 seconds when I use the variables from the input
> parameters?
> Interesting isn't it.
> Jeff

• Next in forum: Re: Best practice for storing long text fields
• Prev in forum: Re: Recurring Scheduled Job
• Thread view: Re: stored proc bug with datetime variable

[Reply to this message]