Sql Server - Need to Backfill a Column in a Table with a Billion Records
I Need to Back Fill a Datetime Column into in an Existing Sql Server Table (A) with a Billion Records. Im Inner Joining the Target Table (A) with the Parent...
I need to back fill a datetime column into in an existing sql server table (A) with a billion records. Im inner joining the target table (A) with the parent table (B) on primary key (ID) and then retriving the dates. Unforunately, i dont have an index on the date column which is causing the update to be very slow. I m not able to create an index on Date column (ID as include) since the online index creation is eating up the whole tlog (150gb max) and offine index build is out of scope.
UPDATE A
SET A.DATE = ZZ.DATE
FROM A
INNER JOIN
(SELECT TOP 100000 A.ID,
B.DATE
FROM A WITH (NOLOCK)
INNER JOIN
B WITH (NOLOCK)
-- parent table
ON A.ID = B.ID
WHERE A.DATE IS NULL) AS ZZ
ON ZZ.ID = A.ID;
Any expert suggetions to perform the backfill in faster or efficent way .
Thanks
3 Answers
Sounds like a case for chunked updates. Incidentally a very exhaustive post has been written on this topic recently (). It deals with log management issues.
Basically, you should split the updates that you do into batches that are as large as possible while not causing too much log usage. You can either split on A (update ranges of A.ID) or split on b (pull the data from B according some data range that is indexed on B (for example the clustered index or any other index).
You select a range of rows using WHERE ID BETWEEN @a AND @b. If ID is indexed you avoid a table scan and can do incremental data pulls.
Please try the following code, it removed one-time inner join, and commit per batch. Remove one-time hash join probably will not help you a lot, but maybe worth a try.
And another thing is, you mention you can't do online index creation, can you do an online index update/re-build, you can add you date column to your cluster index on ID, include your [date] in you cluster index. Since in my query, the where clause has the ID as condition, as well as [date], so, if you can add [date] to your ID index, it will help performance a lot, it will have no table scan, only cluster index seek.
DECLARE @ID BIGINT
SELECT @ID = MIN(ID) FROM A
WHILE @ID < IDENT_CURRENT('DBO.A')
BEGIN
BEGIN TRAN
UPDATE A
SET A.DATE = B.DATE
FROM A
INNER JOIN B (nolock)
ON A.ID = B.ID
WHERE A.ID BETWEEN @ID AND @ID + 100000
AND A.DATE IS NULL
COMMIT TRAN
SET @ID = @ID + 100000
END
UPDATE A
SET A.DATE = ZZ.DATE
FROM A
INNER JOIN
(SELECT A.ID, B.DATE
FROM A WITH (NOLOCK)
INNER JOIN
B WITH (NOLOCK)
ON A.ID = B.ID
WHERE A.DATE IS NULL AND A.ID BETWEEN @a and @a + 100000
) AS ZZ
ON ( ZZ.ID = A.ID )
SET @X = @X + 100000
WAITFOR DELAY '00:00:05'
END
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
