优化跨库数据迁移T-SQL脚本：解决数据准备阶段性能瓶颈

云原生阿岚

2026-6-16

加速5000万条跨表数据迁移准备阶段的优化方案

问题背景

我有同服务器下的Main和Archive两个数据库，需从Archive迁移约5000万条来自11张表的特定行至Main。脚本分为两个阶段：

数据准备阶段：筛选需迁移的工单（Work Order）及其子数据，存入临时表后转存至11张永久表并建立索引，最后清空临时表释放内存
数据迁移阶段：逐个循环处理工单，将对应数据从永久表插入Main库的对应表

目前数据准备阶段已运行48小时仍未完成，以下是数据准备阶段的初始代码：

print 'Script started!'

set nocount on
set xact_abort on

declare @Start datetime = getdate()
declare @PrepTime datetime
declare @InsertTime datetime

drop table if exists ArchiveProject_FinalProdOrderNonDuplicates
drop table if exists ArchiveProject_FinalSalesOrderArchiveCorresponding
drop table if exists ArchiveProject_FinalProdOrderHistoryCorresponding
drop table if exists ArchiveProject_FinalBOMArchiveCorresponding
drop table if exists ArchiveProject_FinalSerialNumberCorresponding
drop table if exists ArchiveProject_FinalNonConformanceCorresponding
drop table if exists ArchiveProject_FinalProdOrderLotArchiveCorresponding
drop table if exists ArchiveProject_FinalProdOrderReworkCorresponding
drop table if exists ArchiveProject_FinalProdOrderReworkStepsCorresponding
drop table if exists ArchiveProject_FinalProdOrderScrapArchiveCorresponding
drop table if exists ArchiveProject_FinalShipmentArchiveCorresponding

create table ArchiveProject_FinalProdOrderNonDuplicates (
    ArchiveYear nvarchar(5),
    [PrO Number] [nvarchar](20) ,
    [SO Number] [nvarchar](7) ,
    [SO Line Item] [nvarchar](10) ,
    [Priority] [nvarchar](10) ,
    [AP Part Number] [nvarchar](20) ,
    [Rev] [varchar](3) ,
    [Order Quantity] [int] ,
    [Due Date] [datetime] ,
    [Scheduled Date] [datetime] ,
    [Time Required] [smallint] ,
    [Status] [nvarchar](20) ,
    [Parent] [nvarchar](20) ,
    [Special Instructions] [nvarchar](50) ,
    [Unit Cost] [money] ,
    [Order Date] [datetime] ,
    [Open Qty] [int] ,
    [Prod Qty] [int] ,
    [Discount] [real] ,
    [Current Department Code] [nvarchar](3) ,
    [Original Due Date] [datetime] ,
    [SO Quantity] [int] ,
    [SN OK] [bit] ,
    [WeldDate] [datetime] ,
    [WeldPriority] [smallint] ,
    [WeldNotes] [varchar](100) ,
    [KitFlag] [bit] ,
    [KitNotes] [varchar](100) ,
    [StockRev] [varchar](3) ,
    [OutsideJob] [bit] ,
    [CustPN] [varchar](20) ,
    [TimeStamp] [timestamp] ,
    [GSSstamp] [datetime] ,
    [TubeNotes] [varchar](100) ,
    [SOnotes] [varchar](100) ,
    [ShortDate] [datetime] ,
    [JOB]  nvarchar(30),
    [SUFFIX]  nvarchar(30),
    [RemoteWO]  nvarchar(30),
    [Site] [varchar](3) ,
    [PART]  nvarchar(30),
    [KitFlagHighlight] [bit] ,
    [CreateDate] [datetime2](7) ,
    [CreatedByHostName] [nvarchar](100) ,
    [CreatedByLogin] [nvarchar](100) ,
    [UpdateDate] [datetime2](7) ,
    [UpdatedByHostName] [nvarchar](100) ,
    [UpdatedByLogin] [nvarchar](100) ,
    [RowPointer] [nvarchar](36) ,
    [Modifiable] [bit],
    rowNum int
)

create table ArchiveProject_FinalSalesOrderArchiveCorresponding (
    ArchiveYear nvarchar(5),
    [SO Number] [nvarchar](7) NOT NULL,
    [Customer Code] [nvarchar](8) NOT NULL,
    [Customer PO] [nvarchar](15) NULL,
    [Order Date] [datetime] NOT NULL,
    [Due Date] [datetime] NULL,
    [Priority] [nvarchar](10) NULL,
    [Standing] [nvarchar](10) NULL,
    [Ship To Address 1] [nvarchar](30) NULL,
    [Ship To Address 2] [nvarchar](30) NULL,
    [Ship To City] [nvarchar](22) NULL,
    [Ship To State] [nvarchar](2) NULL,
    [Ship To Zip] [nvarchar](10) NULL,
    [How Shipped] [nvarchar](20) NULL,
    [Cert] [nvarchar](10) NULL,
    [Repair Order] [bit] NOT NULL,
    [OriginalDueDate] [datetime] NULL,
)

create table ArchiveProject_FinalProdOrderHistoryCorresponding (
    ArchiveYear nvarchar(5),
    [PrO Number] [varchar](20),
    [Time Stamp] [datetime],
    [Activity Code] [varchar](5),
    [Work Station Code] [varchar](3),
    [Employee Code 1] [varchar](10),
    [Employee Code 2] [varchar](10),
    [Employee Code 3] [varchar](10),
    [Employee Code 4] [varchar](10),
    [Stop Date] [datetime],
    [Stop Time] [datetime],
    [PausedFunctionID] [nvarchar](5),
    [ActivityPairID] [nvarchar](36) 
)

create table ArchiveProject_FinalBOMArchiveCorresponding (
    ArchiveYear nvarchar(5),
    [PrO Number] [nvarchar](20),
    [AP Part Number] [nvarchar](20),
    [Rev] [nvarchar](3),
    [Rqd Quantity] [smallint],
    [Kit] [bit],
    [UOM] [nvarchar](5),
    [Item Number] [int],
    [LineDueDate] [date] 
)

create table ArchiveProject_FinalSerialNumberCorresponding (
    ArchiveYear nvarchar(5),
    [Serial Number] [nvarchar](8) NOT NULL,
    [PrO Number] [nvarchar](20) NOT NULL,
    [Date Packaged] [datetime] NOT NULL,
    [Cert Date] [datetime] NULL,
    [ShipPrO] [nvarchar](20) NULL,
    [Date Picked] [datetime] NULL
)

create table ArchiveProject_FinalNonConformanceCorresponding (
    ArchiveYear nvarchar(5),
    [WorkOrder] [varchar](10) NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [NonConformCode] [varchar](5) NULL,
    [DepartmentCode] [varchar](3) NULL,
    [Quantity] [int] NULL,
    [Emp] [varchar](10) NULL
)

create table ArchiveProject_FinalProdOrderLotArchiveCorresponding (
    Dataset nvarchar(100),
    ArchiveYear nvarchar(5),
    [PrO Number] [nvarchar](20),
    [AP Part Number] [nvarchar](20),
    [Lot Number] [nvarchar](12),
    [Replacement] [char](1),
    [Quantity] [int],
    [TimeStamp] [datetime]
)

create table ArchiveProject_FinalProdOrderReworkCorresponding (
    ArchiveYear nvarchar(5),
    [ReworkNumber] [varchar](20) NOT NULL,
    [Originator] [varchar](15) NULL,
    [DueDate] [varchar](10) NULL,
    [Department] [varchar](3) NULL,
    [DefectCode] [varchar](5) NULL,
    [RefType] [varchar](3) NULL,
    [CAR] [varchar](12) NULL,
    [Comment] [varchar](max) NULL,
    [Lot] [varchar](8) NULL,
    [Parent_Lot] [char](1) NULL
)

create table ArchiveProject_FinalProdOrderReworkStepsCorresponding (
    ArchiveYear nvarchar(5),
    [ReworkNumber] [varchar](20) NOT NULL,
    [ReworkLine] [int] NOT NULL,
    [ActionCode] [varchar](5) NULL,
    [ActionComment] [varchar](200) NULL
)

create table ArchiveProject_FinalProdOrderScrapArchiveCorresponding (
    ArchiveYear nvarchar(5),
    [WorkOrder] [varchar](20) NULL,
    [PartNumber] [varchar](20) NULL,
    [Rev] [varchar](3) NULL,
    [Quantity] [int] NULL,
    [DeptCode] [varchar](3) NULL,
    [Emp] [varchar](10) NULL,
    [TimeStamp] [datetime] NULL,
)

create table ArchiveProject_FinalShipmentArchiveCorresponding (
    ArchiveYear nvarchar(5),
    [PrO Number] [nvarchar](20) NOT NULL,
    [Date Shipped] [datetime] NOT NULL,
    [Time Shipped] [datetime] NOT NULL,
    [How Shipped] [nvarchar](20) NULL,
    [Where Shipped] [nvarchar](20) NULL,
    [Quantity] [int] NOT NULL,
    [TrackingNumber] [nvarchar](75) NULL
)

print 'ArchiveProject tables created'

GO

/***************
Production Order (pks: pro number)
***************/
drop table if exists #ProdOrderArchiveWithRows
drop table if exists #ProdOrderArchiveDuplicates
drop table if exists #FinalProdOrderRowsDuplicated

delete from ArchiveProject_FinalProdOrderNonDuplicates

--#ProdOrderArchiveWithRows:
select

    *
    , row_number() over(partition by a.[Pro Number] order by a.[Pro Number], a.ArchiveYear) [row]

into #ProdOrderArchiveWithRows

from

    ATS_ARCHIVE.dbo.[Production Order] a with(nolock)

--#ProdOrderArchiveDuplicates: distinct WOs in the archive that have more than 1 row in the archive
select distinct 

    t.[PrO Number]

into #ProdOrderArchiveDuplicates

from

    #ProdOrderArchiveWithRows t

where

    t.[row] > 1

--#FinalProdOrderRowsDuplicated: Rows duplicated in archive table that don't exist in main
select 

    'Rows duplicated in archive table that dont exist in main' Dataset
    , t2.*

into #FinalProdOrderRowsDuplicated

from

    #ProdOrderArchiveDuplicates t
    inner join #ProdOrderArchiveWithRows t2 on t.[PrO Number] = t2.[PrO Number]

where

    t.[PrO Number] not in (select t.[PrO Number] from [Production Order] t with(nolock))

--ArchiveProject_FinalProdOrderNonDuplicates: 
insert into ArchiveProject_FinalProdOrderNonDuplicates (
    ArchiveYear
    , [PrO Number]
    , [SO Number]
    , [SO Line Item]
    , [Priority]
    , [AP Part Number]
    , [Rev]
    , [Order Quantity]
    , [Due Date]
    , [Scheduled Date]
    , [Time Required]
    , [Status]
    , [Parent]
    , [Special Instructions]
    , [Unit Cost]
    , [Order Date]
    , [Open Qty]
    , [Prod Qty]
    , [Discount]
    , [Current Department Code]
    , [Original Due Date]
    , [SO Quantity]
    , [SN OK]
    , [WeldDate]
    , [WeldPriority]
    , [WeldNotes]
    , [KitFlag]
    , [KitNotes]
    , [StockRev]
    , [OutsideJob]
    , [CustPN]
    , [GSSstamp]
    , [TubeNotes]
    , [SOnotes]
    , [ShortDate]
    , [JOB]
    , [SUFFIX]
    , [RemoteWO]
    , [Site]
    , [PART]
    , [KitFlagHighlight]
    , [Modifiable]
    , rowNum 
)

    select

        t.ArchiveYear
        , t.[PrO Number]
        , t.[SO Number]
        , t.[SO Line Item]
        , t.[Priority]
        , t.[AP Part Number]
        , null
        , t.[Order Quantity]
        , t.[Due Date]
        , t.[Scheduled Date]
        , t.[Time Required]
        , t.[Status]
        , t.[Parent]
        , t.[Special Instructions]
        , t.[Unit Cost]
        , t.[Order Date]
        , t.[Open Qty]
        , t.[Prod Qty]
        , t.[Discount]
        , t.[Current Department Code]
        , t.[Original Due Date]
        , t.[SO Quantity]
        , t.[SN OK]
        , t.[WeldDate]
        , t.[WeldPriority]
        , t.[WeldNotes]
        , t.[KitFlag]
        , t.[KitNotes]
        , t.[StockRev]
        , t.[OutsideJob]
        , t.[CustPN]
        , t.[GSSstamp]
        , t.[TubeNotes]
        , t.[SOnotes]
        , null
        , null
        , null
        , null
        , t.[Site]
        , null
        , null
        , null
        , row_number() over(order by t.[PrO Number]) rowNum

    from

        #ProdOrderArchiveWithRows t
        left join [Production Order] t2 with(nolock) on t.[PrO Number] = t2.[PrO Number]

    where

        t2.[PrO Number] is null
        and t.[PrO Number] not in (select [PrO Number] from #ProdOrderArchiveDuplicates)

drop table if exists #FinalProdOrderRowsInBoth
drop table if exists #ProdOrderArchiveWithRows
drop table if exists #ProdOrderArchiveDuplicates
drop table if exists #FinalProdOrderRowsDuplicated

drop index if exists temp_index_FinalProdOrderNonDuplicates on ArchiveProject_FinalProdOrderNonDuplicates

create index temp_index_FinalProdOrderNonDuplicates
on ArchiveProject_FinalProdOrderNonDuplicates([PrO Number]);

优化方案

一、表结构与索引优化

延迟索引创建：创建永久表时不建立任何索引，等所有数据插入完成后再统一创建。索引创建是IO密集型操作，批量插入后建索引比边插边建效率提升数倍。
替换DELETE为TRUNCATE：原代码中delete from ArchiveProject_FinalProdOrderNonDuplicates改为TRUNCATE TABLE ArchiveProject_FinalProdOrderNonDuplicates，TRUNCATE是DDL操作，不记录单行删除日志，执行速度远快于DELETE。
启用页压缩：创建永久表时添加DATA_COMPRESSION = PAGE选项，减少存储空间占用，提升读写效率，适合归档类数据。
示例：
```
create table ArchiveProject_FinalProdOrderNonDuplicates (
    -- 字段定义不变
) WITH (DATA_COMPRESSION = PAGE);
```

二、查询逻辑简化

预提取已存在工单：一次性把Main库中已有的工单ID提取到临时表，后续所有判断都关联这个表，避免重复扫描大表。
示例：
```
SELECT [PrO Number] INTO #ExistingProNumbers FROM [Production Order] WITH(NOLOCK)
```
替换NOT IN为NOT EXISTS：NOT IN遇到NULL值会出现逻辑异常，且性能远不如NOT EXISTS，尤其是大表查询场景。
示例：
```
WHERE NOT EXISTS (SELECT 1 FROM #ExistingProNumbers t2 WHERE t.[PrO Number] = t2.[PrO Number])
```
**去掉不必要的SELECT ***：原代码中#ProdOrderArchiveWithRows使用SELECT *，只选择需要的字段，减少数据传输和内存占用。
删除无用计算列：如果迁移阶段不需要rowNum列，直接去掉row_number() over(order by t.[PrO Number]) rowNum计算步骤，减少CPU消耗。

三、批量处理优化

分批插入数据：不要一次性插入所有数据，按工单ID范围或ArchiveYear拆分，每次插入10万-50万条，避免长时间占用锁资源和日志暴涨。
示例：
```
DECLARE @BatchSize INT = 50000;
DECLARE @MaxID INT = (SELECT MAX(CAST([PrO Number]
```

优化跨库数据迁移T-SQL脚本：解决数据准备阶段性能瓶颈

问题背景 #

优化方案 #

一、表结构与索引优化 #

二、查询逻辑简化 #

三、批量处理优化 #

问题背景

优化方案

一、表结构与索引优化

二、查询逻辑简化

三、批量处理优化