如何优化涉及大表变量的SQL批量插入查询性能？

你的原查询存在几个关键性能瓶颈：两次调用LocalFunction导致重复执行内连接逻辑、NOT IN/IN子查询的执行效率低下、表变量的统计信息缺失让优化器无法生成最优计划。以下是针对性的优化方案：

核心优化步骤 #

1. 缓存本地城市数据，避免重复调用函数 #

LocalFunction是两张表的内连接查询，每次调用都会重新执行一次关联逻辑。百万级数据下两次调用会直接翻倍开销，先把函数结果缓存到临时表：

-- 缓存LocalFunction的结果到临时表，只执行一次内连接
CREATE TABLE #LocalCity (CityCode NVARCHAR(10), CityName NVARCHAR(MAX))
INSERT INTO #LocalCity(CityCode, CityName)
SELECT CityCode, CityName FROM LocalFunction

-- 给临时表创建索引，加速后续关联
CREATE NONCLUSTERED INDEX IX_LocalCity_CityCode ON #LocalCity(CityCode) INCLUDE (CityName)

2. 替换表变量为临时表，优化统计信息 #

表变量@OnlineDataBase在百万级数据场景下，SQL Server默认不会生成详细的统计信息，优化器容易做出低效的执行计划。换成临时表并创建索引：

-- 替换原表变量为临时表
CREATE TABLE #OnlineDataBase (ID INT IDENTITY(1,1),CityCode NVARCHAR(10),CityName NVARCHAR(MAX),Area INT, Population INT)
-- 此处执行线上数据导入逻辑（与原逻辑一致）
-- 创建索引加速关联
CREATE NONCLUSTERED INDEX IX_OnlineDataBase_CityCode ON #OnlineDataBase(CityCode) INCLUDE (CityName, Area, Population)

3. 用JOIN替代IN/NOT IN子查询，提升关联效率 #

NOT IN在子查询存在NULL值时会导致逻辑错误，且执行效率远不如LEFT JOIN + IS NULL；IN子查询也可以用INNER JOIN替代，让优化器更好地利用索引：

插入新数据（@tbNewEntry）

INSERT INTO @tbNewEntry(CityCode,CityName,Area,Population)
SELECT OD.CityCode, OD.CityName, OD.Area, OD.Population
FROM #OnlineDataBase OD
LEFT JOIN #LocalCity LC ON OD.CityCode = LC.CityCode
WHERE LC.CityCode IS NULL

插入待更新数据（@tbUpdateEntry）

INSERT INTO @tbUpdateEntry(CityCode,CityName,Area,Population)
SELECT OD.CityCode, OD.CityName, OD.Area, OD.Population
FROM #OnlineDataBase OD
INNER JOIN #LocalCity LC ON OD.CityCode = LC.CityCode
WHERE OD.CityName != LC.CityName

4. 清理临时表（可选） #

查询结束后清理临时表，释放资源：

DROP TABLE #LocalCity
DROP TABLE #OnlineDataBase

优化原理说明 #

• 缓存函数结果：避免两次执行昂贵的内连接查询，减少IO和CPU开销。
• 临时表优势：临时表会生成统计信息，SQL Server能根据数据量选择最优的关联方式（比如嵌套循环、哈希连接），而表变量默认只有一行统计，容易导致低效执行计划。
• JOIN替代子查询：JOIN的执行计划通常比IN/NOT IN子查询更高效，尤其是在有索引的情况下，优化器可以直接利用索引进行关联过滤。
• 覆盖索引：创建包含必要字段的覆盖索引，避免查询时的键查找（Key Lookup），直接从索引中获取所需数据。

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