Sql-server – Update taking too long

performancesql serverupdate

I am updating a table with 250 million rows in SQL Server, and it's been running for 4.5 days. I'd like to use the computer it's running on for other memory-intensive work, so I'm torn about whether to cancel the query (which might also take days, right?) or let it keep going. Does anyone see anything in the code below to make you think it's in a never ending loop or something? Will canceling it take so long it's not worth doing?

Here is the code:

update A.Large_Table
set [Large_Table].var1
= [Segment_Info].var1
from A.[Large_Table] left join [A].[Segment_Info]
on [Large_Table].id = [Segment_Info].id
where [Large_Table].var1 is null;

Var1 and id in both tables are integers. I have successfully done this query on a 100 million row table in the past, and it took maybe 2 hours, but in that case the join was performed on an 8-character string column (instead of the 'id' integer). I would have thought that switching to an integer would greatly improve performance and I could get the results for a larger table in comparable time.

Once I get past this run and either cancel or it finishes, I am aware of a few things to make this go better in the future (though it's not an operation I have to run often):

  • Use insert into or select into a new table rather than update the existing one
  • Move my log file to a different drive than the database (I'm still learning such basic things…)
  • Restart SQL Server so the temp file clears

Any other things to try? Any of those above not likely to actually give a boost?

Thanks for your help!

Best Answer

Yikes, I'd be scared to look at your transaction log. This needs to be run in batches. You can experiment with that to determine the fastest runtime. What I like to do is strip out maybe a million or so records, then run updates in various batch sizes, like 1k, 2k, 5k, 10k, at a time to determine the quickest. I've done several hundred million row updates in an hour before. Then again, this also depends on the data size and I'm hoping you aren't messing around with blob fields.