Best way to run updates

Question

Best way to run updates

AlanB

SSC Eights!

Points: 845
More actions
October 18, 2006 at 7:58 am

#61839

I'm investigating poor performance on one of our servers, and have come across a DTS package that uses Execute SQL Tasks to run SQL UPDATE statements. I presume this code would run better as a stored procedure, but one of the Execute SQL Tasks contains 83 UPDATE statements.
Would it be more efficient to group the UPDATE statements into (for example) blocks of 10? Would this enable the server to move on easier? Running it all in one go, I can see a huge build up in DiskIO, but nothing much else happening.
This seems to run reasonably OK at night with no users on the system, but if we have to run during the day, it takes forever.
Any help appreciated.
Alan

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

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

I don t think the huge amount of time is not because your running it from DTS and not SP but by the amount of work to be done.

You ll need to check each update and see if you can optimize it.

Or restrict the number of rows to be affected ... and call the DTS more often so it ll process less data at one time.

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I'll add to that that you may be able to merge some updates together so that you don't update for exemple, the same table with 4 statements, but only with 1.

AlanB SSC Eights! Points: 845 More actions · Answer 3

Thanks guys.

I can't restrict the number of rows because the table is dropped, re-created and populated at the start of the process. The updates then add keys by joining to various other tables (lots of them!).

I agree that there is definately scope for condensing the code to reduce the overall number of update statements, but possibly a more serious issue is the level of use of the server as a whole.

If this code runs at night when there are no users on the system , it runs reasonably well, so before I spend too much time rewritting code, perhaps I need to look at ways of restricting access to the server.

Thanks again.

Alan