TSQL Batch Updates SQL 2005 - 2008

Updating tables in Batches to prevent locking in SQL

There are times when you may need to carry out UPDATES on large tables that are in use and constantly being inserted, deleted, or updated.

If you carry out a large UPDATE that affects all of the rows in the table, and in my case millions of rows, then the table will be locked for the duration of the update and any other processes that may need to carry out DML statements will be BLOCKED from doing so.

For example, you may experience long delays caused by locking when trying to return data from this table on a website or API service. Or even worse deadlocks but you will most definitely experience performance issues and if any SELECT statements that access the data don't use a WITH (NOLOCK) statement they too will have to wait in line for the UPDATE to finish.

Obviously wrapping WITH (NOLOCK) onto every SELECT statement is not a good solution unless you know what you are doing as it will provide dirty reads and you may end up giving your users old or incorrect data. 

This might be fine for some scenarios but in critical applications where data integrity is key then you need another solution that provides data integrity and allows you to UPDATE the table without performance problems.

When I find myself requiring the need to UPDATE every record in a large table I use a BATCH UPDATE process which cuts the large UPDATE statement down into lots of small UPDATES that affect only a few rows at a time.

By doing this the UPDATE rattles through all the tows of even large tables very quickly as long as the batch size of records updated in each loop iteration is small enough not to cause locking that may affect front-end processes.

For example instead of the whole table getting locked for an hour with lots of blocked processes building up behind waiting for it to finish it would instead only be locked for lots of little time periods lasting seconds or less.

These smaller locking periods allow other processes in to do their work and if the batch size is small enough and you have appropriate indexes you might find that you won't experience a full table lock anyway.

There are various methods for carrying out this approach and you should tailor your BATCH SIZE to your own requirements. Before SQL 2005 you could use the: SET NOCOUNT 50 command to set the size of the batch but in SQL 2005 and beyond you can use a variable directly with an UPDATE TOP (@VAR) command.

This is an example of a BATCH UPDATE, that uses a column in the table called LastUpdated, which gets updated every time the row in the table is. You could do this either through stored procedures that update the table, or triggers on insert and update. However because on each loop I update this column it means on the next loop the same records won't get touched as the time is within the 20 minutes I have set as the BATCH to be updated, 

Obviously, this must be tailored to your own system, whether you create an "updated" flag column that is defaulted to 0, and then on the BATCH UPDATE set to 1, and the WHERE statement that selects which TOP X records are looked at ignore any that have been set to 1.

You definitely need something to change on UPDATE, otherwise, you will find this process going on forever as there is no way to order the records for that the UPDATE statement so that it could keep getting the same TOP(X) records on each batch, enabling the process to rattle on forever with you scratching your head wondering why. 

If you can wangle an ORDER BY statement with a convoluted statement then that might work, however having a simple date or flag that is updated within the batch, and is also checked on each loop so that the same records are not looked at over and over again is the easy answer to this issue. 

SET NOCOUNT ON
SET DATEFORMAT YMD

DECLARE @ROWS INT, @TOTALROWS INT, @BATCHSIZE INT

SELECT @ROWS = 1,
@TOTALROWS = 0,
@BATCHSIZE = 50

-- As we start @ROWS at 1 and we know there are thousands of records to update
-- then when it gets to the stage where the UPDATE returns @@rowcount of 0
-- we have finished the criteria of the loop and so exit it the sanity check ensures this 
DO WHILE @ROWS > 0 
BEGIN
     
     -- Show the time this batch started as it might take hours
     PRINT 'Job started at ' + UPPER(FORMAT(GETDATE(),'hh:mm:ss dd/MMM/yyyy'))

     -- We must have a way that we don't keep updating the same records over and over again,
     -- so I use the LastUpdated date which gets updated on each batch update then checked
     -- in the where clause to ensure the date is at least 20 minutes in the future

     -- Update data in the table in batches to prevent blocking if we have to do this
     -- whilst people are using the site and accessing the table at the same time
     UPDATE  TOP(@BATCHSIZE) MyTable
     SET     MyColumn = dbo.udf_SOME_FUNCTION(MyPK),
             Flag = 1,
             LastUpdated = GETDATE()
     WHERE   SomeDate > '2023-JAN-01'
             AND Flag = 0 -- put in for the update
             -- this could be cut out
             AND DATEDIFF(MINUTE,LastUpdated,GETDATE())>20

     SELECT @ROWS = @@ROWCOUNT, @TOTALROWS = @TOTALROWS + @ROWS

    
     PRINT 'Updated ' + CAST(@ROWS as varchar) + ' in batch'

     -- As this UPDATE job may take hours we want other processes
     -- to be able to access the tables in case of locks therefore
     -- we wait for 2 seconds between each BATCH to allow
     -- time for these processes to aquire locks and do their job
     WAITFOR DELAY '00:00:02'

     -- samity check
     IF @ROWS = 0
       BREAK
 
 END

PRINT 'Updated ' + CAST(@TOTALROWS as varchar) + ' total rows'

I am currently using this process now to update a table that is constantly being accessed by a very busy API system that has over a million rows in it and by using BATCH UPDATES it isn't causing any BLOCKING, LOCKING or performance issues at all.

If you really wanted to give the SELECT statements looking at this table as you UPDATE it in batches then you could add in a DELAY within each loop e.g a 2-second DELAY after the UPDATE statement and the SELECT @ROWS = ..... that collects stats for you to look at after the process has finished would just be something like this.

WAITFOR DELAY '00:00:02'

So hopefully this might help you out if you are having to UPDATE large tables that are also in use at the same time by websites, API's, services, or 3rd parties.

© 2023 - By Strictly-Software

SQL System Views

Using SQL 2005 System Views

SQL has numerous system views containing META data about the system. In fact I would say in SQL 2005 there are probably too many system views as they have kept views from previous versions to be backwards compatible and therefore you are spoilt for choice when it comes to accessing system data. For example the following 4 SELECT statements are all examples of accessing meta data about the tables you have created within a database. Notice how each SELECT uses a different system view.

SELECT TOP 1*
FROM INFORMATION_SCHEMA.TABLES
ORDER BY  TABLE_NAME

SELECT TOP 1 *
FROM sys.tables
WHERE type='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.sysobjects
WHERE xtype='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.objects
WHERE type = 'U'
ORDER BY Name

Each statement should return the same table name with varying amounts of related data.

Using System Views to create SQL

There are so many different ways that a working knowledge of the system views can be beneficial but one of the ways is to help you write SQL statements which would otherwise take a long time by hand. For example today at work I had to change the value of a certain column from A to B. The column was a key used for identifying websites. It resided in a system that used 4 databases and appeared in nearly 80% of all the tables, so writing the statements by hand would have taken a long time. The idea was to sync up our development and production server so that this column value was the same for both. Therefore I knew the names of the databases that needed updating as well as the name of the column, the existing value and the new value. I needed to output all the relevant UPDATE statements (177 in total) to do the required changes.

Filtering databases using sys.databases

After setting up some variables I created a local temp table which I populated with the names of the databases that I needed to create UPDATE statements for. I used the system view sys.databases for this e.g

CREATE TABLE #DB(DB INT,DBNAME NVARCHAR(200))

--set DBs we want to check for
INSERT INTO #DB
SELECT database_id,Name
FROM master.sys.databases
WHERE Name LIKE 'mydatabase_system_%'

Then I used the undocumented system stored procedure sp_MSForEachDB to loop through all the databases on the server checking each one to see if it also matches a database in my temp table #DB and if so I run a SELECT statement that takes data from 4 more system views to populate another temporary table #TABLES. This temp table will hold all the information I need to create the necessary SQL including the database name, table name and schema owner.

EXEC sp_MSForEachDB 'USE [?];
IF DB_ID() IN(SELECT DB FROM #DB)
BEGIN
INSERT INTO #TABLES
SELECT d.Name,u.Name, o.Name, c.Name
FROM sys.objects as o
JOIN sys.columns as c
ON o.object_id = c.object_id
JOIN sysusers as u
ON u.uid = o.schema_id
JOIN sys.databases as d
ON d.database_id = db_id()
WHERE c.Name = ''SiteFk''
AND o.type = ''U''
AND o.name LIKE ''tbl_%''
AND c.is_identity=0
ORDER BY o.Name
END'

Notice that I am using the latest system views that come with SQL 2005 and I am returning data that will enable me to create the necessary UPDATE statements with fully qualified names.

Once I have populated my working tables I can create the UPDATE statements. As the column I am updating is used in numerous foreign key constraints I need to disable these constraints before carrying out the update and then re-enable them once the update has completed. Therefore for each table in my loop I output 3 SQL statements.

-- Create ALTER statement to disable foreign key constraints
SELECT @SQL = 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' NOCHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create UPDATE sql to change old value to new value
SELECT @SQL = @SQL + 'UPDATE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' '+@NL + 'SET ' + DBCol + ' = ' + CAST(@NewVal as varchar(5)) + @NL + 'WHERE '+DBCol + ' = ' + CAST(@OldVal as varchar(5)) + ';' + @NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create ALTER statement to enable foreign key constraints
SELECT @SQL = @SQL + 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' CHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

Each loop iteration will PRINT out the value for the @SQL variable and after running the code within a second I have 531 SQL statements to run including 177 UPDATE statements e.g:

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] NOCHECK CONSTRAINT ALL

UPDATE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS]
SET SiteFK = 5000
WHERE SiteFK = 116;

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] CHECK CONSTRAINT ALL

So even with the time it took me to write this little script it still outweighed the amount of time it would have taken me to do the work manually e.g find all tables across 4 databases that included the column I needed to update and then write the necessary SQL. Plus if I need to do something similar in the future I can just tweak the script.

To view the full SQL script that I used click here