Jobs run FOREVER in enterprise manager


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Jobs run FOREVER in enterprise manager

  1. #1
    amy Guest

    Jobs run FOREVER in enterprise manager


    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?

  2. #2
    Dave Guest

    Re: Jobs run FOREVER in enterprise manager


    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?



  3. #3
    amy Guest

    Re: Jobs run FOREVER in enterprise manager


    "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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center