1100万条数据下临时表自连接插入查询的性能优化咨询

开发者阿泽

2026-5-22

优化大表INSERT查询的实用方案（无DBA权限）

你的这个查询逻辑其实是要从临时表#TEMP里提取每个TID对应的**最大ETL_NUM**的记录，然后插入到ABC表中。1100万条数据跑9小时确实太慢了，咱们从几个可操作的方向来优化：

用窗口函数替代自连接，避免大表关联开销
原查询的LEFT JOIN本质是做了一次全表自关联，1100万条数据的自连接会产生海量中间结果，这是耗时的核心原因。改用ROW_NUMBER()窗口函数可以直接定位到每个TID的最新记录，效率会高很多：
```
INSERT INTO ABC(TRACKING_ID,GROUP_ID,ETL_NUM,ENTITY_ID,UNI_ID,DOS_TO)
SELECT TID, TID2, ETL_NUM, ENTITY_ID, UNI_ID, DOS_TO
FROM (
    SELECT 
        A.TID, A.TID2, A.ETL_NUM, A.ENTITY_ID, A.UNI_ID, A.DOS_TO,
        ROW_NUMBER() OVER(PARTITION BY A.TID ORDER BY A.ETL_NUM DESC) AS rn
    FROM #TEMP A
) t
WHERE rn = 1;
```
这个写法不需要做全表自连接，查询优化器可以更高效地处理分组和排序逻辑。
创建针对性的复合索引，而非单一字段索引
你之前只给ETL_NUM加了索引，但这个查询的核心是按TID分组、按ETL_NUM排序，单一索引帮不上大忙。你可以给临时表创建一个复合索引——因为临时表是会话级的，不需要DBA权限就能操作：
```
CREATE NONCLUSTERED INDEX IX_TEMP_TID_ETLNUM ON #TEMP(TID, ETL_NUM DESC)
INCLUDE (TID2, ENTITY_ID, UNI_ID, DOS_TO); -- 包含查询需要的其他字段，避免键查找
```
这个索引能让窗口函数直接按TID分组，快速找到每个组里最大的ETL_NUM记录，同时INCLUDE子句包含了所有需要查询的字段，避免了额外的表查找。

分批插入，降低事务和日志压力
一次性插入1100万条数据会产生巨大的事务日志，还可能引发锁竞争。可以把数据分成小批量插入，比如每次插10万条：

DECLARE @BatchSize INT = 100000;
DECLARE @MaxTID INT = (SELECT MAX(TID) FROM #TEMP);
DECLARE @CurrentTID INT = (SELECT MIN(TID) FROM #TEMP);

WHILE @CurrentTID <= @MaxTID
BEGIN
    INSERT INTO ABC(TRACKING_ID,GROUP_ID,ETL_NUM,ENTITY_ID,UNI_ID,DOS_TO)
    SELECT TID, TID2, ETL_NUM, ENTITY_ID, UNI_ID, DOS_TO
    FROM (
        SELECT 
            A.TID, A.TID2, A.ETL_NUM, A.ENTITY_ID, A.UNI_ID, A.DOS_TO,
            ROW_NUMBER() OVER(PARTITION BY A.TID ORDER BY A.ETL_NUM DESC) AS rn
        FROM #TEMP A
        WHERE A.TID BETWEEN @CurrentTID AND @CurrentTID + @BatchSize - 1
    ) t
    WHERE rn = 1;

    SET @CurrentTID = @CurrentTID + @BatchSize;
    WAITFOR DELAY '00:00:01'; -- 可选，给数据库一点喘息时间
END

如果TID不是连续的，也可以用ROW_NUMBER()给临时表加个序号，按序号分批。分批插入能显著降低日志写入压力，减少锁等待时间。

去掉不必要的NOLOCK提示
临时表#TEMP是会话隔离的，其他会话根本看不到你的临时表，所以NOLOCK提示完全没必要。去掉它不会有任何副作用，还能让查询优化器更灵活地选择执行计划。
检查临时表的数据加载方式
如果#TEMP的数据是从其他地方加载来的，尽量保证加载时是按TID和ETL_NUM排序的，这样创建复合索引时速度更快，后续查询的缓存命中率也会更高。如果是用SELECT INTO创建的临时表，它会是堆表，创建索引后会变成有序结构，对查询更友好。

试试这些方案，优先用窗口函数+复合索引的组合，应该能把耗时降到几小时甚至更短。如果还是慢，可以看看查询执行计划，看看是不是排序操作占了大量CPU，或者有没有全表扫描的情况。

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