存储过程内查询性能优化求助：同查询SP内耗时远超外部

在存储过程（SP）中执行以下查询时，获取结果集耗时5秒，但在存储过程外执行相同查询仅需250毫秒，需要优化该查询使SP内执行耗时小于1秒：

SELECT TAB1.COL6 
FROM TAB1
INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL1
WHERE TAB1.COL2 = 10;

表结构与索引详情 #

TAB1表结构 #

(
    Col0 Bigint (primary key)
    Col1  Char(8)
    Col2 Smallint
    Col3 Timestamp
    Col5 Timestamp
    Col6 Bigint
)

TAB1索引 #

Create Index Index_Name ON TAB1 (Col1,Col2);

• TAB1约200万行数据，包含6列

TAB2表结构 #

(Col1  Char(8))

• TAB2是SP内创建的全局临时会话表，仅1列，约30行，用于存储目标ID后与TAB1做内连接
• 预期返回结果约4500行

备注：无法获取该查询及存储过程的执行计划

1. 更新临时表统计信息 #

存储过程内创建并填充TAB2后，数据库可能没及时生成或更新其统计信息，导致优化器选了低效执行计划。填充完TAB2后手动更新统计：

UPDATE STATISTICS TAB2;

2. 给TAB2加主键或索引 #

TAB2只存Col1列，给它加主键或索引能大幅提升连接效率：

-- 创建TAB2后添加主键
ALTER TABLE TAB2 ADD PRIMARY KEY (Col1);
-- 或者创建非唯一索引
CREATE INDEX IX_TAB2_Col1 ON TAB2 (Col1);

3. 优化TAB1的索引为覆盖索引 #

当前Index_Name包含(Col1,Col2)，但查询要返回Col6，得回表取数据。把Col6加入索引作为包含列，做成覆盖索引，避免回表：

CREATE INDEX IX_TAB1_Col1Col2_IncludeCol6 ON TAB1 (Col1, Col2) INCLUDE (Col6);

如果不能新建索引，也可以考虑修改现有索引，把Col6作为第三列，但要评估对其他查询的影响。

4. 强制重新生成执行计划（解决参数嗅探） #

存储过程可能存在参数嗅探问题，导致优化器用的执行计划不匹配当前临时表的数据分布。在查询末尾加OPTION (RECOMPILE)强制生成适配的计划：

SELECT TAB1.COL6 
FROM TAB1
INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL1
WHERE TAB1.COL2 = 10
OPTION (RECOMPILE);

5. 重写查询，先过滤再连接 #

先筛选出TAB1中Col2=10的数据，再和TAB2连接，减少连接的数据量：

SELECT t1.COL6
FROM (SELECT Col1, Col6 FROM TAB1 WHERE Col2 = 10) t1
INNER JOIN TAB2 t2 ON t1.Col1 = t2.Col1;

6. 改用局部临时表 #

如果业务允许，把全局临时表换成局部临时表（仅当前会话可见），可能减少额外开销：

CREATE TABLE #TAB2 (Col1 Char(8) PRIMARY KEY);

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