Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77
6  3110 
Could be a long shot but try adding a with recompile to the stored
proc and updating the stats on the tables. pr************@yahoo.com (Praty77) wrote in message news:<6c**************************@posting.google. com>... Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77 pr************@yahoo.com (Praty77) wrote in message news:<6c**************************@posting.google. com>... Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77
One possible explanation for this is parameter sniffing: http://groups.google.com/groups?hl=e...%40tkmsftngp03
Simon
Simon -
thanks.
I do not think that explains the 10 times performance problem in my
case. I am not using any default parameters.
Any other thoughts?
praty77 sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>... pr************@yahoo.com (Praty77) wrote in message news:<6c**************************@posting.google. com>... Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77
One possible explanation for this is parameter sniffing:
http://groups.google.com/groups?hl=e...%40tkmsftngp03
Simon
Introduce
SET NOCOUNT ON if it is not present .
If you are running SP as a query and substituting parametes of the SP
with constant values in the code , execution will be faster .
Srinivas pr************@yahoo.com (Praty77) wrote in message news:<6c**************************@posting.google. com>... Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77
srini -
thanks.
The fact is ... it is 10 times slower. And sporadically the speed
problem disappears. I do not think that is an issue... it appears it
is some database internals problem (proc overhead too high). I am
hoping someone can help!
praty ma***@vsnl.com (M A Srinivas) wrote in message news:<f7**************************@posting.google. com>... Introduce
SET NOCOUNT ON if it is not present .
If you are running SP as a query and substituting parametes of the SP
with constant values in the code , execution will be faster .
Srinivas
pr************@yahoo.com (Praty77) wrote in message news:<6c**************************@posting.google. com>... Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77
srini -
thanks.
The fact is ... it is 10 times slower. And sporadically the speed
problem disappears. I do not think that is an issue... it appears it
is some database internals problem (proc overhead too high). I am
hoping someone can help!
praty ma***@vsnl.com (M A Srinivas) wrote in message news:<f7**************************@posting.google. com>... Introduce
SET NOCOUNT ON if it is not present .
If you are running SP as a query and substituting parametes of the SP
with constant values in the code , execution will be faster .
Srinivas
pr************@yahoo.com (Praty77) wrote in message news:<6c**************************@posting.google. com>... Hi -
I hope some one can help me with this.
I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]
I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.
I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?
The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.
Thanks in advance,
-praty77 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Wayne... |
last post by:
I'm trying to get asp to conect to a MySQL database, I've tried copying a
few examples online that I found and I
always get a 500 error with the code below. what am i doing wrong?
any examples of...
|
by: Hennie de Nooijer |
last post by:
Hi, This is a diffcult issue to explain. I hope to make my problem
clear to you.
SITUATION
I'm building A SLA Query for a customer. This customer has an awkward
way to determine the SLA results...
|
by: |
last post by:
In IE, from the child window of a frameset i use the function
top.window.close()
And this works in IE, but not in nn4!
How can i make this same effect in nn4?
please help
|
by: rich buchanan |
last post by:
Can anyone help me with a problem.
Using the DESIGN QUERY screen in Access ... I defined this field from
an existing table .. opinion votes.
politic_need: Mid$(.,13,1)
It returns a...
|
by: Paul Robson |
last post by:
To be precise, from the Event Viewer :-
"Application popup: vcspawn.exe - Application Error : The application
failed to initialize properly (0xc0000005). Click on OK to terminate the...
|
by: MadCrazyNewbie |
last post by:
Hey Group,
A quick question:
Im Using the following code for a xml project:
Private Sub frmSystemUsers_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load...
|
by: Mantorok |
last post by:
Hi all
This has been driving me nuts for months now, if I access my site from IE
https://db.cornwall.gov.uk/PlanningApplications and then hover over one of
the 2-level menus it comes up with the...
|
by: QDL |
last post by:
Hello everyone,
I have this very easy task to accomplish: block clicks on a whole page after
posting to the server to prevent impatient users from clicking many times
and causing problems to the...
|
by: Wayne Smith |
last post by:
I've come up against a major headache that I can't seem to find a solution
for but I'm sure there must be a workaround and I would really be grateful
of any help.
I'm currently building a web...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
|
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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
