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

Speeding up batch SQL Processes that use temporary table variables

Problems with TABLE variables and how to improve performance for SQL Batch Jobs


We recently moved our production system to SQL 2008. Everything seemed okay until we realised after a fortnight that a particular MS Agent job had not been completing correctly.

One of the steps within the job had been failing due to a missing SQL Login. This job transferred large amounts of data from daily tables to historical ones and a large backlog had now been created numbering in the tens of millions.

Due to the use of our batch delete process that removed data that had been transferred in small chunks (500 rows) to reduce locking the data had got to a stage where new records were being added almost as fast as we were removing them.

When I looked at the query that was doing the insert and then delete I saw that it was using a temporary table variable to hold the temporary data whilst it was being transformed.As this was now 20 million plus records it was a problem on it's own due to it's limitations.

This is a common SQL Performance bottleneck and one I have come across a few times now.

SQL's TABLE variables are very good for small datasets (a few hundred at most) and are very useful for array like usage within stored procedures. However when you move to large amounts of records they just become a performance nightmare.

Unlike proper temporary tables or fixed permanent tables you cannot add indexes to TABLE variables and with large record sizes this is usually a must. The constant table scans the process must have been having to do to find rows must have been a major cause of the slow performance.

I rewrote the stored proc to make use of a temporary table, added a clustered index on the columns I was using for my joins to the real table and I used a batch DELETE process using the TOP (@X) statement to remove old records.

This has sped the process up immensely but the automatic creation of indexes and creation and dropping of tables requires permissions higher than those that the website login I use for my site has.

Therefore to ensure everything ran smoothly I had to use the WITH EXECUTE AS 'login' statement to allow the user to impersonate a login with higher privileges.

A cut down example of the proc is below and it shows the following:

1. Impersonating logins with higher permissions to allow for DDL statements e.g CREATE and DROP.
2. Checking TEMP DB for existing temporary tables and indexes and dropping them if they exist.
3. Deleting large numbers of records in batches to prevent blocking.
4. Ensuring a SARGABLE where clause is used instead of DATEDIFF to get yesterdays data

CREATE PROCEDURE [dbo].[usp_sql_job_data_transfer] 
 @Success BIT = 0 OUTPUT,
 @RowsCopied INT = 0 OUTPUT,
 @RowsDeleted INT = 0 OUTPUT

WITH EXECUTE AS 'admin_user'
AS
  BEGIN

  SET NOCOUNT ON
  SET DATEFORMAT YMD

  DECLARE @Stamp datetime, 
   @InsertDate datetime,
   @TmpRows int,
   @Rows int,  
   @RowsInserted int,
   @error int
   

  -- check for existance of old temp table, a local temp table wont persist but we may need to use a global one (Example usage)
  IF object_id('tempdb..#JobHits') IS NOT NULL
    BEGIN
    -- drop it
      
    DROP TABLE #JobHits
    END 
 
  -- create temp table   
  CREATE TABLE #JobHits(
   JobFK int, 
   Stamp Datetime, 
   ViewHits int, 
   SearchHits int
  )

  -- job runs after midnight and we want a SARGABLE WHERE clause
  SELECT @Stamp = CONVERT(datetime,CONVERT(varchar,getdate(),23))

   
  SELECT @TmpRows = 0, @RowsInserted = 0, @RowsDeleted = 0  

  -- insert into my temp table the search hits by date (SQL 2005)
  -- if this proves to be still slow or causing blocks then try inserting in batches of 1000 OR 20 min chunks
  INSERT INTO #JobHits
  (JobFK, Stamp, ViewHits, SearchHits)
  SELECT JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23)),0, count(jobFk) 
  FROM JOBS_DATA with (nolock)
  WHERE HitType = 'S' 
   AND Stamp < @Stamp
  GROUP BY JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23))

  SELECT @TmpRows = @@ROWCOUNT, @Error = @@ERROR
  
  IF @Error <> 0
    GOTO HANDLE_ERROR

  -- insert into my temp table view hits
  INSERT INTO #JobHits
  (JobFK, Stamp, ViewHits, SearchHits)
  SELECT JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23)),count(jobFk), 0
  FROM JOBS_DATA with (nolock)
  WHERE HitType = 'V' AND 
   Stamp < @Stamp -- SARGABLE WHERE CLAUSE - note I am not using DATEDIFF
  GROUP BY JobFK, CONVERT(datetime,CONVERT(varchar,Stamp,23))

  SELECT @TmpRows = @TmpRows + @@ROWCOUNT, @Error = @@ERROR


  -- if an error occurred jump to the error handler
  IF @Error <> 0 
    GOTO HANDLE_ERROR
  --If no error but no rows then its just a bad day for the site with no hits
  ELSE IF @TmpRows = 0  
    GOTO EXIT_PROC
  ELSE
    BEGIN
     
    -- add an index to aid lookups and searching 

    -- ensure no record exists already in Temp DB
    IF object_id('tempdb..clidx_#JobHits') IS NOT NULL 
      BEGIN
      
     DROP INDEX clidx_#JobHits ON #JobHits

      END

       
    -- Add a clustered index to help searching - cover the main join column JobFk and group by column Stamp 
    CREATE CLUSTERED INDEX [clidx_#JobHits] ON #JobHits
    (
     [JobFK] ASC,
     [Stamp] ASC    
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    
    
    END
  
  
  -- join my temp table to the main table to get info about the company the job belonged to
  INSERT INTO JOBS_DATA_HISTORY
  (JobFk,Stamp,ViewHits, SearchHits, ClientFK)
  SELECT a.JobFK, a.Stamp, Sum(a.ViewHits), Sum(a.SearchHits), j.ClientFK
  FROM  #JobHits as a
  JOIN  JOBS as j with (nolock)
   ON  a.JobFk = j.JobPK
  GROUP BY a.JobFK, a.Stamp, j.ClientFk

  SELECT @RowsInserted = @@rowcount, @error = @@ERROR
  

  --if we are here then there must be > 0 rows otherwise we would have exited earlier
  IF @RowsInserted=0 or @Error<>0
    GOTO HANDLE_ERROR
 

  -- Now we have copied our data we need to delete it all from the daily table
  -- as this table is being used by the site still we delete in batches to prevent blocking locks
  SELECT @Rows = 1
  
  -- loop until no more rows are left
  WHILE @Rows > 0
    BEGIN 
    -- delete data in table using the TOP command to ensure we only delete on our indexed column in batches
    DELETE TOP(1000)
    FROM JOBS_DATA
    WHERE Stamp < @Stamp -- indexed column in JOBS_DATA
      
    SELECT @Rows = @@ROWCOUNT, @RowsDeleted = @RowsDeleted + @Rows, @Error = @@ERROR

    -- should i wait for a second in between batches? cannot find a definitive answer
    -- if problems occur (which they haven't so far) then try this
    --WAITFOR DELAY '00:00:01' 
    
    IF @Error <> 0 
      GOTO HANDLE_ERROR
    END

  --if no rows were deleted then something went pete tong
  IF @RowsDeleted=0
    GOTO HANDLE_ERROR
  END

-- clean up
CLEAN_UP:
    
   -- add an index to aid lookups and searching (not sure if when table is dropped the index record remains or not so being safe - check this!)
  IF object_id('tempdb..clidx_#JobHits') IS NOT NULL 
    BEGIN
  -- drop our clustered index
  DROP INDEX clidx_#JobHits ON #JobHits

    END
    
  IF object_id('tempdb..#JobHits') IS NOT NULL
    BEGIN
  -- drop our temp table  
  DROP TABLE #JobHits
    END 
  
  
  -- jump over error handler and exit 
  GOTO EXIT_PROC
  

--handle error
HANDLE_ERROR:

  SELECT @Success = 0

  IF @FromProc = 0
    SELECT @Success as success, COALESCE(@RowsCopied,0) as rowscopied,COALESCE(@RowsDeleted,0) as rowsDeleted

  -- exit
  RETURN 0



--handle a successful exit
EXIT_PROC:
  SELECT @Success = 1, @RowsCopied = @RowsInserted

  -- exit
  RETURN 1