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!