October 26, 2003 at 9:46 pm
Hi All
I am not able to update large amount of data (40 million records) with a simple update command in Sql Server2000 SP2, Sql Server2000 SP3a.
I am having a multiprocessor server with 3GB RAM. with 30 GB harddisk
Can anyone tell me the solution to this
Regards
Sudesh
October 26, 2003 at 10:40 pm
What's the error you are encountering during the update? or
How do you come to know that it ended abruptly?
That could help
shiva
October 26, 2003 at 10:41 pm
What's the error you are encountering during the update? or
How do you come to know that it ended abruptly?
What application you are trying to update from?
is it a web application or client-server application?
That could help
shiva
October 27, 2003 at 3:03 am
I am not getting any error. The query keeps on executing. After 2 hours I have to cancel the query
October 27, 2003 at 3:13 am
MAybe there is no error. Perhaps it is going to take a long time!
Try noting the time it takes to run just a small part of the update (perhaps 1%) and then extrapolating to estimate the total run time.
Tony Bater
Tony
October 27, 2003 at 3:59 am
Good practice would be to split the job up into a number of smaller jobs.
There are a number of problems updating 40 million rows in one statement. Firstly, you are likely to lock the table preventing other users from accessing it. Secondly, if the update fails, SQL Server will rollback any changes meaning that when you resubmit the query, it has to redo all the work that was rolled back.
If you have a primary key you could create a loop to update a number of rows in one step and then loop and updated the next set of rows until all the rows are processed. For example:
declare @current_start_row int, @max_rows int, @rows int
-- Get max rows
select @max_rows = max(primary_key)
from <table>
set @loop = 1000000
set @current_start_row = 1
while @current_start_row < @max_rows begin
-- Update set of rows
update <table>
set ...
where primary_key between @current_start_row and @current_start_row + @loop
-- Increment row counter
set @current_start_row = @current_start_row + @loop
-- Print current start row
print @current_start_row
end
If you need to restart this then you can see the last rows processed from the print statements and restart the update from that row.
You can also use this method to run the updates on multiple thread by having one thread loop for rows 1 to 10000000, another for rows 10000001 to 20000000 etc. This would reduce the overall time update time.
If you have locking problems reduce the number of rows updated in any one go and/or use locking hints.
HTH
Jeremy
Edited by - Jeremy Kemp on 10/27/2003 04:01:05 AM
October 27, 2003 at 4:26 am
Hi Jeremy
Thanks for the reply
This update is called in the DTS. The DTS is run when no one is working on the system i.e at night so even if the table is locked it wont matter.
Also as Tony has said I also checked it by extrapolation and according to it it should have taken only 50 minutes.
The similar query in Sql Server 7.0 takes 3to 5 minutes to execute with one processor.
I wanted to know why process does not complete even after long time in Sql Server 2000 and completes in a fraction of time in Sql Server 7.0
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply