无唯一值字段的SQL插入查询优化咨询：1500万条数据转换插入耗时6小时的解决方案

1500万条记录耗时6小时确实偏慢，结合你的场景（全VARCHAR临时表、无唯一键、大量数据转换），我从数据处理逻辑、插入策略、数据库配置几个方面给你一些实用的优化建议：

一、优化数据转换逻辑，减少实时计算开销 #

你的原SQL在INSERT时实时执行了大量CAST/CASE判断，会让数据库同时处理数据转换和写入操作，拖慢整体速度。可以先做预处理拆分任务：

1. 提前创建预处理临时表 #

把所有数据转换逻辑提前完成，生成一个和目标表数据类型完全匹配的临时表，再直接插入目标表，避免插入时的实时计算：

-- 创建预处理临时表（自动匹配目标表数据类型）
SELECT 
    CAST(activityYear AS INT) AS activityYear,
    CAST(stateCode AS varchar(2)) AS stateCode,
    -- 替换ISNUMERIC为更可靠的TRY_CAST判断，避免误判
    CAST(CASE WHEN TRY_CAST(countyCode AS INT) IS NULL THEN '-99999999' ELSE countyCode END AS INT) AS countyCode,
    -- 简化loanAmount转换：如果是整数格式的字符串，直接转BIGINT即可
    TRY_CAST(loanAmount AS BIGINT) AS loanAmount,
    TRY_CAST(CASE 
        WHEN loanToValueRatio = 'Exempt' THEN '-88888888' 
        WHEN TRY_CAST(loanToValue AS NUMERIC(14,2)) IS NULL THEN '-99999999' 
        ELSE loanToValue 
    END AS NUMERIC(14,2)) AS loanToValue
INTO #PreprocessedStaging
FROM stagingTable;

-- 直接插入目标表，此时无任何转换开销
INSERT INTO reportingTable WITH (TABLOCK) (activityYear, stateCode, countyCode, loanAmount, loanToValue)
SELECT * FROM #PreprocessedStaging;

2. 替换不可靠的ISNUMERIC函数 #

ISNUMERIC的判断逻辑存在盲区（比如ISNUMERIC('$')会返回1），改用TRY_CAST(col AS 目标类型) IS NOT NULL的方式，既精准又能提升转换效率。

二、优化插入策略，降低写入开销 #

1. 使用TABLOCK提示启用批量日志 #

如果你的数据库恢复模式是简单或大容量日志，添加WITH (TABLOCK)可以让数据库采用批量日志模式，大幅减少日志生成量，同时减少锁竞争：

INSERT INTO reportingTable WITH (TABLOCK) 
(activityYear, stateCode, countyCode, loanAmount, loanToValue)
SELECT -- 这里放优化后的转换逻辑
FROM stagingTable;

2. 临时禁用目标表的索引和约束 #

插入大量数据时，每一条记录都要维护索引、检查约束，会带来极大的性能损耗。可以先禁用非聚集索引和外键约束，插入完成后再重建/启用：

-- 禁用非聚集索引
ALTER INDEX ALL ON reportingTable DISABLE;

-- 禁用外键约束（如果存在）
ALTER TABLE reportingTable NOCHECK CONSTRAINT ALL;

-- 执行插入操作...

-- 重建索引（比直接启用更高效，还能整理索引碎片）
ALTER INDEX ALL ON reportingTable REBUILD;

-- 启用外键约束
ALTER TABLE reportingTable CHECK CONSTRAINT ALL;

⚠️ 注意：主键（聚集索引）不建议禁用，除非你能接受临时无主键的状态，否则会导致后续重建开销更大。

3. 分批插入，避免资源耗尽 #

如果一次性插入1500万条导致数据库资源（内存、CPU、磁盘）过载，可以分成小批次插入，比如每次10万条：

DECLARE @BatchSize INT = 100000;
DECLARE @StartRow INT = 1;
DECLARE @TotalRows INT = (SELECT COUNT(*) FROM stagingTable);

WHILE @StartRow <= @TotalRows
BEGIN
    INSERT INTO reportingTable WITH (TABLOCK) 
    (activityYear, stateCode, countyCode, loanAmount, loanToValue)
    SELECT 
        CAST(activityYear AS INT),
        CAST(stateCode AS varchar(2)),
        CAST(CASE WHEN TRY_CAST(countyCode AS INT) IS NULL THEN '-99999999' ELSE countyCode END AS INT),
        TRY_CAST(loanAmount AS BIGINT),
        TRY_CAST(CASE 
            WHEN loanToValueRatio = 'Exempt' THEN '-88888888' 
            WHEN TRY_CAST(loanToValue AS NUMERIC(14,2)) IS NULL THEN '-99999999' 
            ELSE loanToValue 
        END AS NUMERIC(14,2))
    FROM (
        -- 生成行号分批，无唯一键时用ORDER BY (SELECT NULL)快速生成
        SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
        FROM stagingTable
    ) t
    WHERE RowNum BETWEEN @StartRow AND @StartRow + @BatchSize - 1;

    SET @StartRow = @StartRow + @BatchSize;
END

三、数据库配置调优 #

• 调整恢复模式：确保数据库处于简单或大容量日志模式，避免完整恢复模式下的全量日志写入。
• 优化数据文件：增加数据文件的初始大小，设置合理的自动增长值（比如按固定大小增长，而非百分比），避免插入过程中频繁扩容。
• 调整并行度：根据服务器CPU核心数，调整MAXDOP（最大并行度），让数据转换和插入可以利用多个CPU核心。

你的原SQL代码 #

INSERT INTO reportingTable ( [activityYear] ,[stateCode] ,[countyCode] ,[loanAmount] ,[loanToValue] ) 
SELECT 
CAST(activityYear AS INT) AS activityYear,
CAST(stateCode AS varchar(2)) AS stateCode,
CAST((CASE WHEN ISNUMERIC(countyCode) = 0 then '-99999999' else countyCode END ) AS INT) AS countyCode,
TRY_CAST(TRY_CAST(loanAmount as float) as BIGINT) AS loanAmount,
TRY_CAST((CASE WHEN loanToValueRatio='Exempt' THEN '-88888888' WHEN ISNUMERIC(loanToValue) = 0 THEN '-99999999' else TRY_CAST(loanToValue as FLOAT) END ) AS NUMERIC(14,2)) AS loanToValue
FROM stagingTable

示例数据 #

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