使用FETCH NEXT、OFFSET和OPTIMIZE FOR插入数据后行缺失问题排查

背景 #

目标是单次运行（约43次循环）完成CSV中全部200万条记录的导入，但实际仅能加载部分数据，需多次运行才能完成全量导入。怀疑问题出在FETCH NEXT、OFFSET和/或OPTIMIZE FOR语句上。

当前导入逻辑：

• 每次批量插入约50万条数据至临时表#tmp
• 循环约43次，每次筛选出数据库中未存在的约5万条数据，转换后插入目标表

问题 #

单次运行仅能导入部分数据，多次执行脚本才能完成全量导入。迁移历史记录显示每次运行都会新增部分数据，具体数据如下：

相关SQL代码 #

USE FoodData_Central;
DECLARE @beforeChecksum INT = 0;
DECLARE @afterChecksum INT = 0;
DECLARE @migrationName NVARCHAR(40) = N'2024 April Full from 2021 - ';  
DECLARE @pathToInputFolder NVARCHAR(40) = N'C:\FoodData_Central_csv_2024-04-18\';
DECLARE @tableName Nvarchar(40);
DECLARE @startTime DATETIME2 = GETDATE();

BEGIN TRY  
    set @tableName = 'food';
    
    -- 获取表记录数
    DECLARE @SQL NVARCHAR(MAX) = 'SELECT @ResultVariable = count(*) FROM ' + @tableName;
    EXEC sp_executesql @SQL, N'@ResultVariable INT OUTPUT', @ResultVariable = @beforeChecksum output;

    -- 仅首次运行时截断表，后续用分页逐步导入
    -- TRUNCATE TABLE food; 
    -- 创建临时表映射数据
    DROP TABLE IF EXISTS #tmp;
    create table #tmp(
        fdc_id NVARCHAR(max) NOT NULL, 
        data_type NVARCHAR(max) NULL, 
        description NVARCHAR(max) NULL,
        food_category_id NVARCHAR(max) NULL, 
        publication_date NVARCHAR(max) NULL
    )
    bulk insert #tmp
    From 'C:\FoodData_Central_csv_2024-04-18\food.csv' -- 注意文件名可能不同
    WITH
        (
            CODEPAGE = '65001'
            ,FIRSTROW = 2
            ,FIELDTERMINATOR = '\",\"'
            ,ROWTERMINATOR = '0x0A'   -- 换行符
            ,batchsize=500000
            ,TABLOCK
        );

    DECLARE @i int = 1
    DECLARE @offsetCount int = 1;
    DECLARE @nextCount int = 50000;

    WHILE @i < 43
    BEGIN
        SET @i = @i + 1
        -- 插入数据至目标表
        insert into food(fdc_id, data_type, description, food_category_id, publication_date) -- 需更新文件名和列名
            select 
                 CAST(REPLACE(t.fdc_id,'&quot;','') AS INT) AS fdc_id
                , t.data_type
                , t.description
                , CAST(t.food_category_id AS SMALLINT) AS food_category_id
                , CAST(REPLACE(REPLACE(t.publication_date, '&quot;', ''), CHAR(13), '') AS DATETIME2) AS publication_date
            from #tmp t
            WHERE NOT EXISTS ( -- 跳过已存在的记录
                SELECT 1 FROM food AS d -- 更新目标表名
                WHERE d.fdc_id = CAST(REPLACE(t.fdc_id,'&quot;','') AS INT)
            )
            ORDER BY fdc_id DESC

            OFFSET @offsetCount - 1 ROWS 
            FETCH NEXT @nextCount  - @offsetCount + 1 ROWS ONLY
            OPTION ( OPTIMIZE FOR (@offsetCount = 1, @nextCount = 2036474) ); 

        set @offsetCount = @offsetCount + 50000;
        set @nextCount = @nextCount + 50000;
    END

    -- 清理临时表
    DROP TABLE IF EXISTS #tmp;
END TRY  
BEGIN CATCH
    PRINT '错误编号: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
    PRINT '错误信息: ' + ERROR_MESSAGE();
    -- 清理临时表
    DROP TABLE IF EXISTS #tmp;
END CATCH; 
GO  

编辑补充 #

感谢各位的帮助，尤其是@MatBailie的性能优化建议。我为临时表创建了聚簇索引（原临时表无法直接创建，因此新建了带索引的临时表来迁移数据），将运行时间缩短了一半。

内容的提问来源于stack exchange，提问作者Urasquirrel