|
|
Posted by Erland Sommarskog on 09/13/06 22:08
ujjc001 (ujjc001@gmail.com) writes:
> 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?
Without knowing the tables, indexes and queries, it's impossible but
to answer in general terms.
The keyword is "parameter sniffing". When SQL Server builds the
query plan for a stored procedure, it uses the actual values of the
parameters in first invocation as guidance. On the other hand,
it is completely blind for the value of variables and makes standard
assumptions.
Usually it's better with more information, but sometimes it backfires,
for instance if statistics are not accurate.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|