Need to do a mass update - taking days

  • 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)

  • 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;

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

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