Insert, Select on same table

  • I have two jobs running. Job2 is kicked off after 3 mins of start of job1. Job1 has a long running step where it inserts and updates a table. Job2 has a step in which a query like where not exists( select from the same table udated in job1 ). Job2 which takes normally 4 to 5 mins to run, but when it has to run simultaneously with job1, it takes more than 40 minutes.

    This process has been running like this since years, no blocking or locking issues are recorded and job2 never had dirty reads. I am investigating the root cause of over running of Job2. I found that job 2 waiting at the same step where it has to select data from the same table. Now I need to know if this is the reason or could be any other. Please advice.

  • You are using "same" a few times and it's not clear what you are referring to.

    If there is a conflict of resources between the jobs, it could be blocking. It doesn't matter if it worked well in the past, if data size/skew changed, it's possible that the jobs are executing at the same time and slowing each other down.

  • if my interpretation of your "same" references, it is highly likely that the "inserts and updates" in job (1) are locking the table. Why not move the steps in job (2) to the end of job (1)'s job. In other words, keep job (2) from starting until job (1) is complete.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply