Fastest way to do mass update

Posted by user356004 on Stack Overflow See other posts from Stack Overflow or by user356004
Published on 2010-06-02T02:28:20Z Indexed on 2010/06/02 2:33 UTC
Read the original article Hit count: 484

Filed under:
|

Let’s say you have a table with about 5 million records and a nvarchar(max) column populated with large text data. You want to set this column to NULL if SomeOtherColumn = 1 in fastest possible way. The brute force UPDATE does not work very well here because it will create large implicit transaction and take forever. Doing updates in small batches of 50K records at a time works but it’s still taking 47 hrs to complete on beefy 32 core/64GB server. Is there any way to do this update faster? Are there any magic query hints/table options that scarifies something else (like concurrency) in exchange of speed?

NOTE: Creating temp table or temp column is not an option because this nvarchar(max) column involves lots of data and so consumes lots of space!

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql