Tuning SQL Update
-
Hi there,
I have a script which update a huge table (almost 4 millions records) with date values.
Is there a solution of tuning this update:
update calendar_documente
set data_sf='2007-08-31' --year month day (end of future month)
where data_sf = '2007-07-31'--year month day (end of current month)
??
In Theory, theory and practice are the same...In practice, they are not. -
1)Is there an index on data_sf?
2)if it doesn't have to run as a transaction, try splitting it up in batches
(test the following first)
DECLARE @DATEOLD datetime
DECLARE @DATENEW datetime
set @DATEOLD=...
set @DATENEW=...
SET ROWCOUNT 10000 /*limit processing to 10000 records*/
while exists(select 1 from calendar_documente where data_sf=@DateOld)
BEGIN
update calendar_documente
set data_sf=@DateNew --year month day (end of future month)
where data_sf =@DateOld--year month day (end of current month)
END
-
1) no index on data_sf
2) ok, it is processing 10000 records, how about the other records? is there a way of
avoiding "while" mechanism - row by row. I know another mechanism through which it could
insert 4 millions records in 2 minutes, using a CTE (Common Table Expression) - SQL Server 2005. It is a different approach. For SQL Server 2000 it's something similar using unions.
In Theory, theory and practice are the same...In practice, they are not. -
Is data_sf quite selective? If so an index on data_sf wouldn't probably hurt to avoid a tablescan for the records to update.
-
I think I should put an index on data_sf. I just wonder if it exists another method to quickly update.
In Theory, theory and practice are the same...In practice, they are not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply