Need to do a mass update - taking days

Question

Need to do a mass update - taking days

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004621 More actions · Answer 1

Ed B wrote:

Trust me, I don’t use format specifically because of your previous remarks. I changed as little as possible to illustrate a point.

Ah... now I understand. Heh... I thought "WTH? Is this the same Ed B that I'm thinking of"? 😀 😀 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Your Name Here SSCrazy Points: 2345 More actions · Answer 2

I was blowing out the log files trying to update 150M records & decided to do batch updates instead.

Here's a demo of the script I wrote to generate the commands I needed based on a batch size I thought was reasonable (1 M). I'm sure people smarter than me here will have better suggestions but this worked for me.

-- [ID] is the primary key in the source table #bigtable
-- @y is my 'batch processing' guide table
-- @commands is the table I'm going to loop through

/* PREP */
/*
DROP TABLE IF EXISTS #bigtable;
CREATE TABLE #bigtable (
   [ID] VARCHAR(20) PRIMARY KEY
  ,[HatSize] TINYINT NULL)
INSERT INTO #bigtable ([ID], [HatSize])
   SELECT CONCAT(pos1.[ch], pos2.[ch], pos3.[ch])
         ,CASE 
             WHEN pos1.[ch] IN ('A','B','C','D','E','F','G') THEN 6
             WHEN pos1.[ch] IN ('H','I','J','K','L','M','N') THEN 7
             WHEN pos1.[ch] IN ('O','P','Q','R','S','T','U') THEN 8
             WHEN pos1.[ch] IN ('V','W','X','Y','Z') THEN 9
             ELSE 10
          END 
      FROM (SELECT CHAR([value]) AS [ch] FROM GENERATE_SERIES(65, 90, 1)) pos1
            CROSS JOIN 
            (SELECT CHAR([value]) AS [ch] FROM GENERATE_SERIES(65, 90, 1)) pos2
            CROSS JOIN 
            (SELECT CHAR([value]) AS [ch] FROM GENERATE_SERIES(65, 90, 1)) pos3
*/

/* Hold on to your hats, folks! */
DECLARE @batch INT = 40

DROP TABLE IF EXISTS #x;
CREATE TABLE #x (
   [RecID]   INT IDENTITY(1, 1)
  ,[ID]      VARCHAR(20) NOT NULL PRIMARY KEY)
DECLARE @y TABLE (
   [LowID]   VARCHAR(20) NOT NULL
  ,[HighID]  VARCHAR(20) NOT NULL
  ,[BatchID] TINYINT NOT NULL PRIMARY KEY)
DECLARE @commands TABLE (
   [RecID]   INT IDENTITY(1, 1)
  ,[Command] NVARCHAR(MAX) NOT NULL)
INSERT INTO #x ([ID]) 
   SELECT [ID]
      FROM #bigtable

;WITH range_cte ([RecID], [ID], [LowHighFlag], [NextVal]) AS (
   SELECT [RecID]
         ,[ID]
         ,[RecID] % @batch
         ,LEAD([ID]
              ,1
              ,(SELECT MAX([ID]) FROM #x)) OVER (ORDER BY [ID])  
      FROM #x
      WHERE [RecID] % @batch = 1 
      OR [RecID] % @batch = 0)
INSERT INTO @commands ([Command])
   SELECT CONCAT('SET NOCOUNT ON; BEGIN TRAN; UPDATE #bigtable SET [HatSize] = [HatSize] + 10 WHERE [ID] BETWEEN '
                ,CHAR(39),CONVERT(VARCHAR(19), [ID]),CHAR(39)
                ,' AND '
                ,CHAR(39),CONVERT(VARCHAR(19), [NextVal]),CHAR(39)
                ,'; COMMIT TRAN; SET NOCOUNT OFF;')
      FROM range_cte
      WHERE [LowHighFlag] = 1
      ORDER BY [RecID];
SELECT [Command]
   FROM @commands
DROP TABLE IF EXISTS #x;

--DROP TABLE IF EXISTS #bigtable;