471,071 Members | 2,883 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

stored proc bug with datetime variable

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

Sep 13 '06 #1
5 4305
There was a union between two select's in this sp also. When I removed
the union and one of the selects (vice-versa also) it ran faster. So
maybe it's a combination of the problem below and a union?
Just thought I'd mention.
Jeff

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
Sep 13 '06 #2
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
Sep 13 '06 #3
ujjc001 (uj*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 13 '06 #4
Ok, so I read
http://blogs.msdn.com/queryoptteam/a...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 (uj*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 14 '06 #5
ujjc001 (uj*****@gmail.com) writes:
Ok, so I read
http://blogs.msdn.com/queryoptteam/a...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?
I will have to ask for apologies, but could you post an example to
clarify your question? I'm not sure that I understand it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Andy G | last post: by
6 posts views Thread by Paul M | last post: by
3 posts views Thread by Reza Solouki | last post: by
reply views Thread by Jim | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.