x

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<>''
more ▼

asked Jun 10, 2014 at 04:44 PM in Default

avatar image

sqlLearner 1
972 43 51 57

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jun 10, 2014 at 05:37 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1114
x428
x172
x47
x8

asked: Jun 10, 2014 at 04:44 PM

Seen: 445 times

Last Updated: Jun 10, 2014 at 05:37 PM

Copyright 2017 Redgate Software. Privacy Policy