Jobs run FOREVER in enterprise manager

[amy]

I need to update about 300,000 records. I wrote several stored procedures
so that I could 'chunk up' the records into smaller groups. The problem
is that when I run the stored procedures from query analyzer they take abou
10 minutes each and when I create a job in enterprise manager to call the
same stored procedures they take about 3 hours each. When I scheduled 1
job with 6 steps (1 for each stored procedure that processes about 50,000
records each), the job took 15 hours to run. I can run all 6 stored procedures
from query analyzer and they run in a total of about 90 minutes. Any ideas?

[Dave]

Amy: Is it possible that when you run the job from isql you are running only
one step at a time, but when you run the job from EM that EM is "helping"
you by spawning threads that run the different steps simultaneously? This
might create locks on the various tables you are using and make the different
procs wait for resources. You might test this by firing up the job in EM
and then looking in Management... Current Activity... Process Info to see
if it is running separate processes, and look to the far right to see if
any are blocked.

Good Luck, Dave

"amy" <amykr@firstlogic.com> wrote:
>
>I need to update about 300,000 records. I wrote several stored procedures
>so that I could 'chunk up' the records into smaller groups. The problem
>is that when I run the stored procedures from query analyzer they take abou
>10 minutes each and when I create a job in enterprise manager to call the
>same stored procedures they take about 3 hours each. When I scheduled 1
>job with 6 steps (1 for each stored procedure that processes about 50,000
>records each), the job took 15 hours to run. I can run all 6 stored procedures
>from query analyzer and they run in a total of about 90 minutes. Any ideas?

[amy]

"Dave" <dave@nadolna.net> wrote:
>
>Amy: Is it possible that when you run the job from isql you are running
only
>one step at a time, but when you run the job from EM that EM is "helping"
>you by spawning threads that run the different steps simultaneously? This
>might create locks on the various tables you are using and make the different
>procs wait for resources. You might test this by firing up the job in EM
>and then looking in Management... Current Activity... Process Info to see
>if it is running separate processes, and look to the far right to see if
>any are blocked.
>
>Good Luck, Dave
>
>"amy" <amykr@firstlogic.com> wrote:
>>
>>I need to update about 300,000 records. I wrote several stored procedures
>>so that I could 'chunk up' the records into smaller groups. The problem
>>is that when I run the stored procedures from query analyzer they take
abou
>>10 minutes each and when I create a job in enterprise manager to call the
>>same stored procedures they take about 3 hours each. When I scheduled
1
>>job with 6 steps (1 for each stored procedure that processes about 50,000
>>records each), the job took 15 hours to run. I can run all 6 stored procedures
>>from query analyzer and they run in a total of about 90 minutes. Any ideas?
>
Dave,
I tried what you said and it doesn't seem to be spawning other threads.
You are correct that from ISQL I am running one step at a time and from
EM I am running 6 steps - one runs after the successful completion of the
other. I checked the job history and no step started before the previous
one finished. Thanks for the thoughts, though.