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

Could be a long shot but try adding a with recompile to the stored
proc and updating the stats on the tables.


praty77-google@yahoo.com (Praty77) wrote in message news:<6c19a8f5.0404061536.74ad2c16@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

praty77-google@yahoo.com (Praty77) wrote in message news:<6c19a8f5.0404061536.74ad2c16@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
sql@hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0404070102.7c6fb9a4@posting.google. com>...
> praty77-google@yahoo.com (Praty77) wrote in message news:<6c19a8f5.0404061536.74ad2c16@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


praty77-google@yahoo.com (Praty77) wrote in message news:<6c19a8f5.0404061536.74ad2c16@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
masri@vsnl.com (M A Srinivas) wrote in message news:<f7e90f78.0404082230.7d613bc6@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
>
>
> praty77-google@yahoo.com (Praty77) wrote in message news:<6c19a8f5.0404061536.74ad2c16@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