2

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!

Improve this question
10
  • Perhaps a silly question to start us off, but are the "id" fields on each table unique on each table? I fear some sort of Cartesian Product situation might have developed if, for instance, one of your tables actually has duplicate "id" values within itself. Also, any reason why you're using a LEFT JOIN rather than an INNER?
    – EMarkM
    Mar 2, 2015 at 13:55
  • What's the output from this query on your instance? select session_id, blocking_session_id, wait_type from sys.dm_exec_requests where session_id>51 This will let us know if your query is potentially blocked by another query, or what type of activity the query is waiting on (it might be IO, or Network, or something else)
    – Stuart Moore
    Mar 2, 2015 at 14:08
  • 1
    Cancelling and rolling back can far more time than the length of the current query's runtime. Cancel and rollback is a 'mostly' single-threaded process, rather than what could currently be a multi-threaded UPDATE. blogs.msdn.com/b/psssql/archive/2008/09/12/…
    – Mark Sinkinson
    Mar 2, 2015 at 14:09
  • @Stuart session_id blocking_session_id wait_type 57 0 PAGEIOLATCH_EX 58 0 NULL
    – Anna
    Mar 2, 2015 at 14:15
  • 1
    So you're duplicating data in every row for a single id? Why?
    – Aaron Bertrand
    Mar 2, 2015 at 14:50

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.