question

sqlLearner 1 avatar image
sqlLearner 1 asked

Optimize Update

I have a simple update statement I need to run on a large table of about 20 million records. The update itself takes about 11 minutes to run. I ran the update in batches of 50,000 at a time and that knocked it down to about 9 minutes. Is there anything that can be done to improve this further? I really need it to run much quicker. I am concatenating a Varchar field with a text field. OldValue is the TEXT field update TableA set NewLong = OldValue+cast(NewLong as varchar(max)) where OldValue''
sqltsqlupdateoptimizationbatch
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
Because the old value is going for something other than empty string, there's not much you can do with indexes to make it faster. I think you've largely covered what's possible unless you have a better way to filter that can use an index to speed up the process.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.