DTS insert and Update

Question

DTS insert and Update

fred2002

SSCrazy

Points: 2943
More actions
November 28, 2006 at 7:38 am

#170505

we have a DTS runnung wich perform update and inserts into a table. And since the DB grows we get more and more table locks.
Could somebody help?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Any chance you could post some more info for us? How about, your SQL statements to do you updates as well as table design for starters... Take a look at your indexes and see if you can't improve them to make your updates and inserts run a bit faster.

What kind of locks are you seeing?

To help us help you read this[/url]For better help with performance problems please read this[/url]

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

The best way to prevent table locks is to limit the number of rows that are part of each transaction. Commit inserts every few thousand rows, perform updates in a loop that only updates a few thousand at a time.

fred2002 SSCrazy Points: 2943 More actions · Answer 3

Scott,

how should i commit the insert every few thousands rows?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

What is the data source ? SQL or non-SQL ?

Instead of a long-running row-by-row DTS process, you can minimize Insert/Update duration and duration of locks by pulling the data into a SQL staging table, then using set-based Insert/Update statements between 2 SQL statements.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

Look at the options for the data pump task. The default is to commit all rows in one transaction, but you change it to commit every x number of rows. The insert will probably run faster and hold fewer locks if it is committed in smaller blocks, but you have to consider how you would recover if the package failed with only half of the rows committed.

Pulling the data into a staging table before doing inserts/updates would probably make the package run faster, but if you are updating a large number of rows in a heavily-used table you may still run into locking issues. You might still want to put the inserts & updates (from the staging table) into loops of a few thousand rows at a time.

You might also look at the execution plan for the inserts/updates to see if it is using indexes efficiently. If there are a lot of indexes on the table it will slow down updates and inserts. You could look into whether the table is getting badly fragmented with all the updates and inserts.

fred2002 SSCrazy Points: 2943 More actions · Answer 6

Hello All,

we could resolved the problem separating the select from insert /update instructions. But it s strange that we didnt had this problem before. And the number of row did not increase.