Updating huge table

  • Hi All,

    I have two tables i.e VoiceData, VoiceData_History ,

    below are rows Count of the tables.

    VoiceData --230 millions (23 core)

    VoiceData_History --8 Millions (80 lacks)

    Now i want to update Archive_id column in VoiceData table based on VoiceData_History table

    below the update statement for updating the record

    Update VoiceData set Archive_id =VDH.Archive_id

    from VoiceData VD join VoiceData_History VDH on

    VD.ID=VDH.ID

    I have "non clustered index on Archive_id " column and Clustered index on ID column in both table

    Note: In both table Matched Data is 80 millions

    What is best way for updating the those records. If i run above statement , it locking the data table.

    and it is production DB

  • -- Gather all of the update info into a skinny temp table

    -- then you're done with VoiceData_History.

    -- Add a column containing a suitable batch number

    -- used to split your data into sensible batches of

    -- say 100,000 (1 lac) rows

    SELECT

    vdh.ID,

    vdh.Archive_id,

    batch = NTILE(800) OVER(ORDER BY VDH.ID)

    INTO #VoiceData_History

    FROM VoiceData vd

    JOIN VoiceData_History vdh

    ON vd.ID = vdh.ID

    CREATE UNIQUE CLUSTERED INDEX (ucx_ID) ON #VoiceData_History (ID, batch)

    DECLARE @Batch BIGINT

    SET @Batch = 1

    WHILE @Batch < 801

    BEGIN

    BEGIN TRANSACTION

    UPDATE vd SET Archive_id = vdh.Archive_id

    FROM VoiceData vd

    INNER JOIN #VoiceData_History vdh

    ON vdh.ID = vd.ID

    AND vdh.batch = @Batch

    COMMIT TRANSACTION

    SET @Batch = @Batch + 1

    -- some folks put a wait here

    END

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

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