Performance of update statement inside DTS package

I have a DTS package, which after running daily with no problems for a couple of years, has started to play up. Originally it was inserting data into a table which would then fire an insert trigger. The trigger used [inserted] to update three columns in the table. Usually, it was updating about 500,000 rows of inserted data.

When the problem started, the update statement inside the trigger was taking hours to run and usually had to be cancelled. Disabling the trigger allowed the DTS to run as normal. Running the UPDATE statement in a regular query window didn't cause any problems - it ran in less than 10 seconds.

To get around the problem, another task was added to the DTS to run the UPDATE after the INSERT in place of the trigger. This worked OK for a while, but the separate UPDATE task has now started to exhibit the same problem that the trigger had. The UPDATE still runs as expected if done in a regular query window.

The DTS package was 开发者_如何学Pythoncreated in SQL 2000 and is running on SQL 2005 Enterprise x64.

I'm at a bit of a loss to try and figure this one out. Any ideas?

---

Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).

Therefore you may have a look into the approach of SQL Parallel Boost at http://www.ibax.ch/-IBX-/plain.simpleimagetitletextlinklist.en.solutions.products.parallelboost/default.aspx

This approach can also be used to execute multiple SQL statements in parallel.

A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !

In case you don't wan't to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.

---

Have you tried looking at the query plan for the UPDATE statement? You can do that in query analyzer/ssms.

What indexes does the table have on it? Maybe they are having to be maintained and are slowing the update down.

Can you go into detail on the UPDATE? Is it one SQL statement or are you using a CURSOR?

Converting the whole thing to SSIS would most likely add performance, but that depends on what you're actually doing in DTS.