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>...[color=blue]
> 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>...[color=green]
> > 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[/color][/color]
