Update And insert statement running parallel on 2 different tables

Question

Update And insert statement running parallel on 2 different tables

sumiga_11

Grasshopper

Points: 18
More actions
August 13, 2009 at 12:46 am

#138134

Hi friends,
In my SSIS package an update statement and an Insert statement is running parallel on two different tabels in the same database. even if both the statements are acting upon different tables the update statement is waiting for the completion of the insert statement .. therefore i am experiencing an increase in run time... Can some one help me how to resolve this problem ?

Viewing 5 posts - 1 through 5 (of 5 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

sumiga_11 (8/13/2009)
Hi friends,
In my SSIS package an update statement and an Insert statement is running parallel on two different tabels in the same database. even if both the statements are acting upon different tables the update statement is waiting for the completion of the insert statement .. therefore i am experiencing an increase in run time... Can some one help me how to resolve this problem ?

If your update is waiting for the insert statement to complete, then the statements are not running in parallel. Is there a relationship between the 2 tables and what is the relationship? Can you open SSMS during the execution and go to the Activity Monitor? Right-click and select "View Locks by Object". Tell us what contention do you see?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

sumiga_11 Grasshopper Points: 18 More actions · Answer 2

Hi,

I am testing the package in development server. the package is not deployed still. And there is no relationship between the two tables. They belong to the same database. The update statement is executing till the insert statement is completed. If i run the update statement alone its taking just 1 min for execution. If i run it along with the insert stmt (30 mins) the update statement is also taking 30 mins to complete execution...

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

And the results from Activity Monitor are ... ?

Maybe tempdb is getting locked and that is blocking the update...

If all else fails, why not just run the UPDATE first and save yourself the pain?

sumiga_11 Grasshopper Points: 18 More actions · Answer 4

The problem is the package is already running for 2 hours and there are 20 update statements running sequential. I cant make them parallel since they are updating the same table. And i am adding an insert statement to the package which runs for 30 mins and the run time of the package should not increase. So i am running both of them together.