473,700 Members | 2,523 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

stored proc bug with datetime variable

two variables declared in my proc:
@DATE_RANGE_STA RT as datetime,
@DATE_RANGE_END as datetime,

When I execute my SP it takes 34 seconds.

When I change the variables to:
@DATE_RANGE_STA RT1 as datetime,
@DATE_RANGE_END 1 as datetime,

and add this to my sp:
declare @DATE_RANGE_STA RT datetime
declare @DATE_RANGE_END datetime
set @DATE_RANGE_STA RT = @DATE_RANGE_STA RT1
set @DATE_RANGE_END = @DATE_RANGE_END 1
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 4396
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_STA RT as datetime,
@DATE_RANGE_END as datetime,

When I execute my SP it takes 34 seconds.

When I change the variables to:
@DATE_RANGE_STA RT1 as datetime,
@DATE_RANGE_END 1 as datetime,

and add this to my sp:
declare @DATE_RANGE_STA RT datetime
declare @DATE_RANGE_END datetime
set @DATE_RANGE_STA RT = @DATE_RANGE_STA RT1
set @DATE_RANGE_END = @DATE_RANGE_END 1
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_STA RT as datetime,
@DATE_RANGE_END as datetime,

When I execute my SP it takes 34 seconds.

When I change the variables to:
@DATE_RANGE_STA RT1 as datetime,
@DATE_RANGE_END 1 as datetime,

and add this to my sp:
declare @DATE_RANGE_STA RT datetime
declare @DATE_RANGE_END datetime
set @DATE_RANGE_STA RT = @DATE_RANGE_STA RT1
set @DATE_RANGE_END = @DATE_RANGE_END 1
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_STA RT as datetime,
@DATE_RANGE_END as datetime,

When I execute my SP it takes 34 seconds.

When I change the variables to:
@DATE_RANGE_STA RT1 as datetime,
@DATE_RANGE_END 1 as datetime,

and add this to my sp:
declare @DATE_RANGE_STA RT datetime
declare @DATE_RANGE_END datetime
set @DATE_RANGE_STA RT = @DATE_RANGE_STA RT1
set @DATE_RANGE_END = @DATE_RANGE_END 1
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****@sommarsk og.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_STA RT as datetime,
@DATE_RANGE_END as datetime,

When I execute my SP it takes 34 seconds.

When I change the variables to:
@DATE_RANGE_STA RT1 as datetime,
@DATE_RANGE_END 1 as datetime,

and add this to my sp:
declare @DATE_RANGE_STA RT datetime
declare @DATE_RANGE_END datetime
set @DATE_RANGE_STA RT = @DATE_RANGE_STA RT1
set @DATE_RANGE_END = @DATE_RANGE_END 1
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****@sommarsk og.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****@sommarsk og.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
4000
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) on the Internet. Was hoping someone here would know? My sql stored proc: SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As 'SelectInfo' FROM tblAnomaly
1
1802
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 MS Access. The thing that throws me off is the multiple "from" statements. I have tried a dozen different queries, nothing seems to do the trick. Either I end up with an syntax error, or I end up with inaccurate results. Dan
1
3059
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 wait while a reservation is being placed. During that time that the process is running on the server, they frequently show some sort of graphic that implies "don't touch anything - we're working." The problem: If I post a page with the graphic,...
1
1725
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) so that I can sen in the value as a parameter. When I try to use the adapter's fill command to fill the data set, it get a error tellig me that I am not giving the stored procedure the parameter that it expects, even though I am! I've looked...
5
1724
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 page. My question is would it be better to store all of the fields(15 or so) from the first page in session variables and reference them from the next page and run ONE stored procedure OR would it be better to run a stored procedure the first page...
14
1830
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, which would you choose and why? Reason I ask is I created a webpage which essentially runs through a litany of loops to determine which stored proc to kick off. This is written in the code-behind. It occurred to me that I could probably just...
6
2159
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 fine...however, I can't seem to figure out how to pass parms to the C Program. The compile, bind, and run using DB2BATCH all work fine, however, when I attempt to access any values passed into the program, they're not present. Here's part of the JCL...
3
7412
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 null, and SQL Server doesn't accept DateTime.MinValue... What is the solution to deal with these kind of cases? I probably could create some work around to send some awkword value that SQL accept and then
0
1059
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 no problem. Am I making the wrong call against this procedure. I'm passing dates as parameters and the variables are defined as date and I use smalldatetime in the stored proc. My connection object is cnSQL and it works perfectly.
6
4504
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 quite often to figure out how to get these applications to talk with eachother. Todays problem: I want to pass time figures to a stored procedure using a forms Input Paramaters property, namely @StartTime and @EndTime (both are defined as datetime...
0
8728
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8647
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9217
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7813
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6563
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5903
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3092
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2392
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2030
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.