SQL Server高效标记并检索行：如何避免重复执行昂贵查询？

针对你需要执行一次复杂查询、复用结果完成更新并返回数据的场景，以下是几种SQL Server专属的优化策略：

1. 用临时表替代表变量 #

表变量的统计信息有限，SQL Server默认会假设其行数极少，导致后续关联/更新的执行计划不佳。临时表（#开头）会生成完整的统计信息，还能添加索引进一步提升性能：

仅存储ID的临时表方案 #

-- 创建带主键的临时表（自动生成聚集索引）
CREATE TABLE #Ids (id INT PRIMARY KEY);

INSERT INTO #Ids (id) 
SELECT id
FROM my_table 
WHERE complex_conditions; 

-- 基于临时表ID更新主表
UPDATE my_table 
SET columnA = 1 
WHERE id IN (SELECT id FROM #Ids);

-- 复用临时表ID查询结果
SELECT * 
FROM my_table 
INNER JOIN #Ids ON my_table.id = #Ids.id
INNER JOIN other_complex_joins;

-- 用完可手动删除，也会在会话结束后自动清理
DROP TABLE #Ids;

存储完整查询结果的临时表方案 #

如果后续需要的不止ID，直接把复杂查询结果存入临时表，避免二次关联主表：

CREATE TABLE #ExpensiveResults (
    id INT PRIMARY KEY,
    other_columns VARCHAR(100) -- 匹配原查询列的类型
);

INSERT INTO #ExpensiveResults
SELECT TOP 1000 id, other_columns
FROM my_table
WHERE complex_conditions
ORDER BY some_expensive_sorting;

-- 基于临时表更新主表
UPDATE t
SET columnA = 1
FROM my_table t
JOIN #ExpensiveResults er ON t.id = er.id;

-- 直接返回预存的结果
SELECT * FROM #ExpensiveResults;

2. 结合OUTPUT子句减少重复操作 #

如果需要返回更新后的行，可在UPDATE语句中用OUTPUT直接捕获结果，避免额外查询。若需要原始查询结果，可先将原始数据存入临时表再执行更新：

保留原始结果并更新 #

CREATE TABLE #OriginalResults (
    id INT PRIMARY KEY,
    other_columns VARCHAR(100),
    columnA_old INT -- 可选，记录更新前的值
);

-- 先存储原始查询结果
INSERT INTO #OriginalResults (id, other_columns, columnA_old)
SELECT id, other_columns, columnA
FROM my_table
WHERE complex_conditions
ORDER BY some_expensive_sorting;

-- 执行更新，可选输出更新日志
UPDATE t
SET columnA = 1
OUTPUT deleted.id, deleted.columnA AS old_value, inserted.columnA AS new_value
INTO #UpdateLog -- 可选表，记录更新前后数据
FROM my_table t
JOIN #OriginalResults er ON t.id = er.id;

-- 返回原始查询结果
SELECT id, other_columns FROM #OriginalResults;

直接返回更新后的行 #

DECLARE @UpdatedResults TABLE (
    id INT,
    columnA INT,
    other_columns VARCHAR(100)
);

UPDATE my_table
SET columnA = 1
OUTPUT inserted.id, inserted.columnA, inserted.other_columns
INTO @UpdatedResults
WHERE complex_conditions; -- 若条件极复杂，仍建议先存临时表

SELECT * FROM @UpdatedResults;

3. 使用内存优化表变量（SQL Server 2014+） #

内存优化的表变量数据存储在内存中，无需磁盘I/O和日志写入（非持久化场景），性能远优于普通表变量。需先创建内存优化的表类型：

-- 创建内存优化的表类型
CREATE TYPE dbo.IdListType AS TABLE (
    id INT NOT NULL INDEX IX_IdListType_Id NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000)
) WITH (MEMORY_OPTIMIZED = ON);

-- 使用该类型声明变量
DECLARE @Ids dbo.IdListType;

INSERT INTO @Ids (id)
SELECT id
FROM my_table
WHERE complex_conditions;

UPDATE my_table
SET columnA = 1
WHERE id IN (SELECT id FROM @Ids);

SELECT *
FROM my_table
JOIN @Ids ON my_table.id = @Ids.id
JOIN other_complex_joins;

注意：哈希索引的BUCKET_COUNT需设置为接近预期行数的2倍左右，避免哈希冲突。

4. 优化原始复杂查询的性能 #

从根源上降低复杂查询的耗时，后续复用的成本也会降低：

• 添加覆盖索引：针对查询的过滤条件、排序字段和返回列创建覆盖索引，避免键查找或表扫描：

  CREATE NONCLUSTERED INDEX IX_my_table_Complex_Covering
  ON my_table (filter_column1, filter_column2, some_expensive_sorting)
  INCLUDE (id, other_columns); -- 包含查询需要返回的非索引键列
  

• 更新统计信息：确保SQL Server有最新的表统计信息，生成更优的执行计划：

  UPDATE STATISTICS my_table WITH FULLSCAN;
  

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