June 28, 2013 at 5:24 am
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
June 28, 2013 at 6:16 am
-- 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
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