XML自连接查询性能优化求助：8000条数据耗时10秒

首先，咱们先拆解下你当前查询慢的核心原因：你的CTE会先对全表所有行做XML解析提取masterid，然后再做自连接——这相当于先把8000条数据全解析一遍，再做匹配，哪怕你加了XML索引，全表解析的开销也会拉垮性能。下面给你几个针对性的优化方案，按优先级排序：

1. 最彻底的优化：持久化计算列+索引 #

这是长期来看最优的方案，直接把需要的masterid从XML里预提取出来，避免每次查询都解析XML：

-- 第一步：添加持久化计算列，把masterid预存到表中
ALTER TABLE [dbo].[products_xml]
ADD masterId AS xml.value('(/product/attribute-list/attribute[@name="variation_master_product_id"]/value[@default="1"])[1]','char(11)') PERSISTED;

-- 第二步：给计算列加非聚集索引，同时包含常用查询列避免键查找
CREATE NONCLUSTERED INDEX idx_products_xml_masterId ON [dbo].[products_xml](masterId)
INCLUDE (varenr, ean, title);

之后你的查询就可以直接用计算列，完全跳过XML解析：

-- 先拿到目标变体的masterid，再查所有同主商品的变体
WITH target_master AS (
    SELECT masterId
    FROM [dbo].[products_xml]
    WHERE varenr LIKE '277028%'
)
SELECT p.*
FROM [dbo].[products_xml] p
INNER JOIN target_master tm ON p.masterId = tm.masterId;

这个方案能把查询时间压缩到毫秒级，因为所有操作都是基于普通列索引，完全绕开了XML解析的开销。

2. 利用现有XML索引改写查询逻辑 #

如果暂时不想改表结构，那就要避免全表XML解析，先定位目标变体再获取masterid，再匹配同master的行：

-- 先获取目标变体的masterid（只解析符合条件的行，不是全表）
DECLARE @targetMaster char(11);
SELECT @targetMaster = xml.value('(/product/attribute-list/attribute[@name="variation_master_product_id"]/value[@default="1"])[1]','char(11)')
FROM [dbo].[products_xml]
WHERE varenr LIKE '277028%';

-- 再用masterid查询所有同主商品变体
SELECT *
FROM [dbo].[products_xml] p
CROSS APPLY p.xml.nodes('/product/attribute-list/attribute[@name="variation_master_product_id"]/value[@default="1"]') AS x(v)
WHERE x.v.value('.', 'char(11)') = @targetMaster;

同时要确保你的XML二级索引是路径索引（PATH），因为你的查询是基于固定XPath路径提取值，路径索引对这种场景的支持最好：

-- 假设你的XML主索引名为idx_products_xml_primary，创建路径二级索引
CREATE XML INDEX idx_products_xml_path ON [dbo].[products_xml](xml)
USING XML INDEX idx_products_xml_primary
FOR PATH;

3. 排查执行计划确认索引生效 #

你可以打开SQL Server的执行计划（Ctrl+M），看看当前查询是否用到了XML索引：

• 如果看到XML Index Seek，说明索引在工作；
• 如果还是Table Scan或XML Index Scan，可能是XPath写法有细微差异，或者索引类型不匹配，需要调整索引或XPath表达式。

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