Re: stored proc bug with datetime variable

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@xxxxxxxxx) 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@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Newbie Question on Jobs & Stored Procedures.
    ... Sql Server. ... DECLARE @MSG INT ...
    (microsoft.public.sqlserver.programming)
  • Re: between dates with no time consideration
    ... > Columnist, SQL Server Professional ... > declare @start as datetime ... >> only passing the date value back to the stored procedure, ...
    (microsoft.public.sqlserver.programming)
  • Re: Real to datetime - how to...?
    ... would like to have it in hh:mm:ss format. ... The division with 24 is necessary, because a datetime value consists ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • RE: Problem with profiling datetime
    ... DateTime dt = cmd.ExecuteScalar; ... The resulting trace log item is as follow ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: between dates with no time consideration
    ... Columnist, SQL Server Professional ... just the range doesnt declare @start as datetime ... "Tom Moreau" wrote in message ... > "Tom Moreau" wrote in message ...
    (microsoft.public.sqlserver.programming)