473,224 Members | 1,441 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 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 4375
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: KathyB | last post by:
I'm trying to concatenate fields in SQL stored proc for use in text field in asp.net dropdownlist. I'm running into a problem when I try to use a DateTime field, but can't find the answer (so far)...
1
by: Dan Caron | last post by:
I have this stored procedure below that works great in SQL Server. The sql purges rows out of a table, leaving "x" # of rows in the table ("x" = s.RetainHistoryNum below). Now I need it to run in...
1
by: Mark | last post by:
ASP.NET: Any suggestions on how to display an animated .gif file on a web page while a stored procedure is running in the background? For example, on some of the airline sites, it asks you to...
1
by: Eric Land | last post by:
Help! I'm trying to call a parameterized stored proc in ASP.NET in VB. I am creating a command object and creating a parametr list, and assigning a value from a session variable (this is working)...
5
by: Andy G | last post by:
I have a registration page that captures 75% of the users data. After they enter that info they are redirected to one of two pages depending on how they answered a question on the registation...
14
by: Roy | last post by:
Apologies for the cross-post, but this truly is a two-sided question. Given the option of creating Looping statements within a stored proc of sql server or in the code-behind of an .net webpage,...
6
by: Paul M | last post by:
Hi All, I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the IBM Stored Procedure guide (SG24-7083-00). The database calls to read and update the database work...
3
by: Reza Solouki | last post by:
Hello, I have a system where it gets its data from a file that is provided periodically. There are cases where many values such as dates are blank. Considering DateTime variable doesn't accept...
0
by: Jim | last post by:
Here is my code and it times out on the server without throwing an error. I know the Stored Proc exists in the database I am connected to and I can run it with parameters from Query Analyzer with...
6
by: garyb2008 | last post by:
Hello All Ive been creating my first access project in Access 2000 and SQL Server. Its up and running now for 35 users and has been pretty much a success, though ive been stopped in my tracks...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

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.