如何优化T-SQL中关联SQLXML参数查询的执行耗时

核心问题根因 #

当前写法存在三个核心性能损耗点：

• 3处关联逻辑每次都独立解析XML参数，重复执行XML解析操作，无结果复用
• XML路径使用//后代轴，会遍历全XML节点，开销远高于精确路径解析
• @Code IS NULL OR ...的写法容易抑制业务表t.Code字段的索引使用，导致全表扫描

可落地优化方案 #

方案1：一次性预解析XML到表变量复用 #

只解析1次XML，把所有Code值存入带主键的表变量，3处关联逻辑直接复用表变量结果，避免重复解析。
代码示例：

-- 存储过程开头新增预解析逻辑
DECLARE @CodeList TABLE (Code NVARCHAR(2) PRIMARY KEY);
IF @Code IS NOT NULL
BEGIN
    INSERT INTO @CodeList(Code)
    SELECT Tbl.Col.value('text()[1]', 'nvarchar(2)')
    -- 替换//为精确绝对路径，减少解析开销
    FROM @Code.nodes('/codes/code') Tbl(Col);
END

原关联逻辑改写为：

AND (
    NOT EXISTS(SELECT 1 FROM @CodeList)
    OR EXISTS(SELECT 1 FROM @CodeList cl WHERE cl.Code = t.Code)
)

该方案无需修改存储过程入参，改造成本最低，性能可提升3倍以上

方案2：拆分查询逻辑消除OR条件 #

如果业务允许拆分查询分支，直接用IF分支区分参数为NULL和非NULL的场景，彻底避免OR对索引的抑制：

IF @Code IS NULL
BEGIN
    -- 不带Code过滤的原查询逻辑
END
ELSE
BEGIN
    -- 带Code匹配的查询逻辑，去掉OR判断，直接写匹配条件
    AND EXISTS(SELECT 1 FROM @CodeList cl WHERE cl.Code = t.Code)
END

该方案可让优化器正常走t.Code字段的索引，性能提升最明显

方案3：替换XML为表值参数(TVP)彻底消除解析开销 #

如果允许修改存储过程入参定义，直接用预定义的表类型替代XML参数，完全省去XML解析步骤：

1. 先定义表类型：

CREATE TYPE CodeListType AS TABLE (Code NVARCHAR(2) PRIMARY KEY);

2. 存储过程入参修改为：

@Code CodeListType READONLY = NULL

3. 关联逻辑直接复用@Code表参数，无需任何解析操作。
   该方案性能最优，适合高频调用的存储过程

补充优化 #

给业务表t的Code字段创建非聚集索引，如果查询中返回的其他字段不多，可直接创建覆盖索引，避免键查找开销。

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