如何优化SQL存储过程性能？其因获取大量记录运行缓慢

咱们来一步步拆解怎么优化这个处理大量数据的存储过程，先梳理下现存的几个性能瓶颈：

• 两个分支的SELECT逻辑几乎完全重复，既不好维护，也不利于SQL Server复用执行计划
• 当前的非聚集索引没有覆盖查询所需的所有列，导致查询时需要频繁回表（Key Lookup），处理大量数据时这会严重拖慢速度
• 查询里重复了Ac列的输出，虽然对性能影响不大，但属于冗余代码
• 可能存在参数嗅探问题——如果@T='ABC'和其他值对应的数据集大小差异很大，SQL Server可能会生成不合适的执行计划

下面是具体的优化步骤：

1. 合并重复逻辑，简化分支判断 #

把两个分支里重复的SELECT部分整合，只在排序逻辑上做区分，这样能让SQL Server更好地复用执行计划，同时代码也更简洁：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
-- 优化后的Usp_SP1
ALTER PROC [dbo].[Usp_SP1] @T nvarchar(50)
AS
BEGIN
    SELECT 
        xyz as [XYZK],
        COALESCE(RTRIM(LTRIM(T)),'') AS T,
        COALESCE(RTRIM(LTRIM(V)),'') AS V,
        COALESCE(RTRIM(LTRIM(Des)),'') AS Des,
        COALESCE(Seq, 0) AS Seq,
        COALESCE(Ac,0) AS Ac, -- 移除了重复的Ac列
        COALESCE(RTRIM(LTRIM(U)),'') AS U,
        COALESCE(L,0) AS _L,
        COALESCE(M,0) AS _M,
        COALESCE(E,0) AS _E,
        COALESCE(S,0) AS _S
    FROM tblA WITH (NOLOCK)
    WHERE T = @T
    -- 根据@T的值动态调整排序逻辑
    ORDER BY 
        CASE WHEN @T = 'ABC' THEN V ELSE '' END,
        Seq; -- 非ABC场景下，第一个排序列是常量，实际就按Seq排序
END
SET NOCOUNT OFF
GO

2. 创建覆盖索引，避免回表和额外排序 #

这是提升大量数据查询性能最关键的一步。当前的非聚集索引没有覆盖查询需要的所有返回列和排序列，导致SQL Server不得不先通过索引找到数据位置，再去表中读取完整数据（回表操作），还可能要额外做排序。创建下面的覆盖索引：

CREATE NONCLUSTERED INDEX IX_tblA_T_V_Seq_Covering
ON tblA (T, V, Seq)
INCLUDE (xyz, Des, Ac, U, L, M, E, S);

索引设计思路： #

• 首列T是查询的过滤条件（WHERE T=@T），能快速定位符合条件的数据集
• 后面的V和Seq是排序列，当@T='ABC'时排序顺序是V, Seq，索引的顺序刚好能满足这个需求，避免SQL Server做额外的Sort运算
• INCLUDE子句包含了所有SELECT需要返回的其他列，这样查询可以直接从索引里获取所有需要的数据，完全不需要回表

如果非ABC的查询占比很高，也可以考虑针对两种排序场景各建一个索引，但通常一个覆盖索引就能应对大部分情况，SQL Server会根据查询条件自动选择最优的使用方式。

3. 其他注意事项 #

• 关于NOLOCK：WITH(NOLOCK)允许读取未提交的脏数据，如果你的业务不需要这种宽松的一致性，建议去掉这个提示，避免数据不准确的问题。如果必须用，一定要确认业务能接受脏读的风险。
• 参数嗅探问题：如果@T='ABC'对应的数据集和其他值的数据集大小差很多，可能会出现参数嗅探导致的性能波动。可以在查询末尾加上OPTION(RECOMPILE)，或者用局部变量承接参数来缓解：

  DECLARE @LocalT nvarchar(50) = @T;
  SELECT ... WHERE T = @LocalT ORDER BY ... OPTION(RECOMPILE);
  

  不过RECOMPILE会增加执行计划编译的开销，建议只在确实遇到参数嗅探问题时使用。
• 冗余代码清理：原查询里重复写了COALESCE(Ac,0) AS Ac，优化后的代码已经移除了这个重复项，减少不必要的计算。

通过这些优化，应该能大幅降低查询的执行时间——尤其是处理大量记录时，覆盖索引能彻底避免回表和额外排序，简化后的逻辑也有助于执行计划的高效复用。

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