Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skysailor
Starting Member
2 Posts |
 Posted - 2009-06-10 : 01:08:35
|
| Hi,I have to update many thousands of rows in a database table. Is it more efficient to do a thousand update statements within a single transaction or to do a transaction for each update?I was originally doing a single transaction around the whole update loop. This resulted in thousands of row level locks as the loop progressed. The performance was OK for up to about 1,000 rows. Then after that the performance dropped off (i.e. if double the number of rows the time taken was 4 times longer etc.).I figured there must be a lot of overhead in processing all the locks for each new update so I changed it to do a transaction for each update. Now the number of locks does not build up over time. But the performance is essentially the same. It still slows down dramatically when processing more than about 1000 rows.Any ideas on why this might be happening?Clayton |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-10 : 10:36:27
|
| First the question is, do you want the entire batch to succeed or fail as a whole or are you OK with small chunks of updates getting committed. If your requirements permit you to have smaller chunnks of updates, its better to put the transaction inside the loop. Also hopefully you are not doing one update per row? see if you can batch it.. this will reduce a lot of overhead.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-10 : 13:52:12
|
| also if you're batch update is taking too long, you could try to create a temporary covering index which can speed up the update query and then drop it off after the full updation is over. |
|
|
|
skysailor
Starting Member
2 Posts |
Posted - 2009-06-10 : 23:48:36
|
| Thanks for your feedback.The issue turned out to be related to code that was nothing to do with SQL. The DataTable was being filled up with each iteration. And each time a search of the table was performed which gradually became more expensive to perform. Fixed now. |
|
|
|
|
|
|
Single long transaction versus many short transact