I would like to perform an update statement impacting 10 million rows. It is a simple update like
UPDATE Table
SET ColumnX = 0;
I did notice that a simple SELECT like
SELECT Column
FROM Table
takes about 34 seconds.
As it is a table used in production and the table is rather big (2,8 GB data, index 1,6 GB) I would like to estimate the runtime before executing the statement.
The update is performed on SQL Server 2008 R2.
Is there a possibility to obtain the estimated runtime?
There is no way to estimate this.
You ~could~ backup the database, restore it on similar hardware...and try that. But since no one will probably be hitting the restored-backup, even that is not an exact measurement.
If you can deal with having the updates occur....but without a transaction (aka, its ok for some rows to have the correct value for a few seconds...while the others do not), then I would invite the goldie locks approach.
Goldie Locks would be........not all the rows in one hit, but not row by row either.
Here is a pointer...Update TOP N ...
Maybe try 1,000 or 10,000 or something like that.
https://granadacoder.wordpress.com/2009/07/06/update-top-n-order-by-example/
........
Below is a modified example. I have 1,000 rows. I update 100 rows at a time, so it loops 10 times (10 x 100 = 1000)...to update the column.
It's a "goldie locks" trick..........I think this would serve you well on a real production db under load.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Television]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[Television]
END
GO
CREATE TABLE [dbo].[Television] (
TelevisionUUID [uniqueidentifier] not null default NEWSEQUENTIALID() ,
TelevisionName varchar(64) not null ,
TelevisionKey int not null ,
IsCheckedOut bit default 0
)
GO
ALTER TABLE dbo.Television ADD CONSTRAINT PK_Television_TelevisionUUID
PRIMARY KEY CLUSTERED (TelevisionUUID)
GO
ALTER TABLE dbo.Television ADD CONSTRAINT CK_Television_TelevisionName_UNIQUE
UNIQUE (TelevisionName)
GO
set nocount on
declare @counter int
select @counter = 11000
declare @currentTVName varchar(24)
declare @TopSize int
select @TopSize = 100
while @counter > 10000 /* this loop counter is ONLY here for fake data,….do not use this syntax for production code */
begin
select @currentTVName = 'TV:' + convert(varchar(24) , @counter)
INSERT into dbo.Television ( TelevisionName , TelevisionKey ) values ( @currentTVName , @counter)
select @counter = @counter - 1
end
select count(*) as TV_Total_COUNT from dbo.Television
print ''
print 'Checked Out Items, Should be Zero at this point'
select count(*) as PRE_UPDATE_COUNT from dbo.Television tv where tv.IsCheckedOut <> 0
/*
--Does not Work!
Update TOP (10) dbo.Television
Set IsCheckedOut = 1
FROM
dbo.Television tv
ORDER BY tv.TelevisionKey
*/
declare @AuditTrail table ( TelevisionUUID uniqueidentifier , OldIsCheckedOut bit , NewIsCheckedOut bit )
declare @LoopCounter int
select @LoopCounter = 0
while exists ( Select top 1 * from dbo.Television tv where tv.IsCheckedOut = 0 )
BEGIN
select @LoopCounter = @LoopCounter + 1
/*print '/@LoopCounter/'
print @LoopCounter
print ''*/
;
WITH cte1 AS
( SELECT
TOP (@TopSize)
TelevisionUUID , /* <<Note, the columns here must be available to the output */
IsCheckedOut
FROM
dbo.Television tv
WITH ( UPDLOCK, READPAST , ROWLOCK ) --<<Optional Hints, but helps with concurrency issues
WHERE
IsCheckedOut = 0
ORDER BY tv.TelevisionKey DESC /* This order by is optional */
)
UPDATE cte1
SET IsCheckedOut = 1
output inserted.TelevisionUUID , deleted.IsCheckedOut , inserted.IsCheckedOut into @AuditTrail ( TelevisionUUID , OldIsCheckedOut , NewIsCheckedOut )
;
END
print '/Number of Update Loops/'
print @LoopCounter
print ''
print ''
print 'Newly Checked Out Items'
select count(*) as POST_UPDATE_COUNT from dbo.Television tv where tv.IsCheckedOut <> 0
print 'Output AuditTrail'
select * from @AuditTrail
print 'Not checked out items'
select count(*) as TVCOUNTIsNOTCheckedOut from dbo.Television tv where tv.IsCheckedOut = 0