|
|
Posted by ujjc001 on 09/14/06 15:51
Ok, so I read
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx and
it make sense but why then, was the SP'd call slower if it would have
recompiled vs the call in a raw query? I would assume that my date
column stats were messed up? Would that seem logical?
Erland Sommarskog wrote:
> 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]
|