Rogue Cursor

Can anybody help'
I have a stored procedure that takes 6 minutes to run when
executed through Query analyser When its called by a
scheduled job it takes over 6 hours. Nothing else runs on
the server so there are no issues with conflicts.
The stored procedure is a simple fast forward cursor that
goes through the rows of a table containing approx 300k
rows. I have evaluated the execution using the SQL
profiler and when the stored procedure is run from a job
the cursor seems to pause for approx 600ms every 7
cycles , this pause does not happen at all when it is
executed via query analyser. As there are over
The server is SQLServer7 Standard (evaluation) and run on
an NT4 platform. I have tried to run the same stored
procedure on a much higher spec machine (SQLServer 2k,
windows 2k server, dual processor, raid config) but
although the runtimes for either method were a lot
quicker, calling the stored procedure from a job is at
least 50% slower.
Can anyone tell me?
A. What causes this?
B. Can it be fixed?
C. What else could be affected?You very very seldom need any cursors at all. Can you post the code so
someone here can rewrite it without a cursor? Based on my experience your sp
will then run in about 6 seconds, without a cursor.
--
Jacco Schalkwijk
SQL Server MVP
"H jones" <hmjones@.atlanticeg.com> wrote in message
news:0a3e01c3c975$333c1cc0$a401280a@.phx.gbl...
> Can anybody help'
> I have a stored procedure that takes 6 minutes to run when
> executed through Query analyser When its called by a
> scheduled job it takes over 6 hours. Nothing else runs on
> the server so there are no issues with conflicts.
> The stored procedure is a simple fast forward cursor that
> goes through the rows of a table containing approx 300k
> rows. I have evaluated the execution using the SQL
> profiler and when the stored procedure is run from a job
> the cursor seems to pause for approx 600ms every 7
> cycles , this pause does not happen at all when it is
> executed via query analyser. As there are over
> The server is SQLServer7 Standard (evaluation) and run on
> an NT4 platform. I have tried to run the same stored
> procedure on a much higher spec machine (SQLServer 2k,
> windows 2k server, dual processor, raid config) but
> although the runtimes for either method were a lot
> quicker, calling the stored procedure from a job is at
> least 50% slower.
> Can anyone tell me?
> A. What causes this?
> B. Can it be fixed?
> C. What else could be affected?
>