批量更新 4000 万行的最佳方式 [英] Best way to update 40 million rows in batch

问题描述

基本上我需要在一个有 4000 万行的表上运行这个，一次更新每一行都会崩溃，所以我想批处理查询，以便如果它崩溃，它可以重新运行查询，它会跳过完成批次，然后继续处理剩下的.

Basically I need to run this on a table with 40 million rows, updating every row at once will crash, so I want to batch the query so that if it crash, it can re-run the query and it would skip the finished batch and just continue with the ones left over.

UPDATE [table]
SET [New_ID] = [Old_ID]

最快的方法是什么?表的创建方式如下:

What is the fastest way to do this? Here is how the table is created:

CREATE TABLE [table](
    [INSTANCE_ID] [int] NOT NULL,
    [table_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [old_ID] [bigint] NOT NULL,
    [new_ID] [bigint] NOT NULL,
    [owner_ID] [int] NOT NULL,
    [created_time] [datetime] NULL
) ON [PRIMARY]

created_time、owner_ID 上也有索引.

There are also indexes on created_time, owner_ID.

我的更新语句完全如图所示，我实际上只需要将 old_id 中的每个条目复制到 new_id 中，共 4000 万行.

My update statement is EXACTLY as shown, I literally just need to copy every entry in old_id into new_id for 40 million rows.

推荐答案

Declare @Rowcount INT = 1;

WHILE (@Rowcount > 0)   
BEGIN
        UPDATE TOP (100000) [table]   --<-- define Batch Size in TOP Clause
           SET [New_ID] = [Old_ID]
        WHERE [New_ID] <> [Old_ID]

        SET @Rowcount = @@ROWCOUNT;

       CHECKPOINT;   --<-- to commit the changes with each batch
END

这篇关于批量更新 4000 万行的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！