检索存在对应子记录的父记录时SQL查询耗时过长的优化方案咨询

你遇到的核心问题是CTE_Result里的相关子查询会逐行执行计算，当数据量较大时会带来巨大的性能开销。原来的COUNT(1)子查询会对CTE_AOC的每一行都遍历一次TermMappings表，这就是查询耗时32秒的主要原因。下面是几个针对性的优化方案：

方案1：用预计算集合+JOIN重构逻辑（最推荐） #

先提前算出所有符合映射条件的TermId集合，再和CTE_AOC做关联，避免逐行执行子查询：

;WITH CTE_Topics AS (
 SELECT TermId, Term, ParentId FROM Terms WITH (NOLOCK)
 WHERE ParentId = 24 AND IsDeleted = 0
 UNION ALL
 SELECT T.TermId, T.Term, T.ParentId FROM Terms T WITH (NOLOCK)
 INNER JOIN CTE_Topics CT ON T.ParentId = CT.TermId AND T.IsDeleted = 0
),
CTE_Filter AS (
 SELECT TermId FROM CTE_Topics WITH (NOLOCK)
 WHERE Term IN ('Topis 1', 'Topis 2', 'Topis 3', 'Topis 4', 'Topis 5')
),
CTE_AR AS (
 SELECT TermId, Term, ParentId, LongDescription FROM TermMappings TM WITH (NOLOCK)
 INNER JOIN Terms TD WITH (NOLOCK) ON TD.TermId = TM.DestinationId AND TD.IsDeleted = 0 AND TM.IsDeleted = 0 AND TM.SourceId = 5121 AND TD.ParentId IN (100, 108)
),
CTE_AOC AS (
 SELECT TermId, Term, ParentId, LongDescription FROM TermMappings TM WITH(NOLOCK)
 INNER JOIN Terms TD WITH(NOLOCK) ON TD.TermId = TM.DestinationId AND TD.IsDeleted = 0 AND TM.IsDeleted = 0 AND TM.SourceId = 5300 AND TD.ParentId IN (100, 108)
 WHERE TermId NOT IN (SELECT TermId FROM CTE_AR)
),
-- 预计算所有存在有效映射的TermId，只保留唯一值
CTE_MatchingTerms AS (
 SELECT DISTINCT TM.SourceId AS TermId
 FROM TermMappings TM WITH(NOLOCK)
 WHERE TM.IsDeleted = 0
 AND TM.DestinationId IN (SELECT TermId FROM CTE_Filter)
)
SELECT 
  CA.TermId, CA.Term, CA.ParentId, CA.LongDescription,
  1 AS TopicMappingExist  -- JOIN已确保存在匹配，直接设为1
FROM CTE_AOC CA
INNER JOIN CTE_MatchingTerms MT ON CA.TermId = MT.TermId

方案2：保留原结构但用EXISTS替代COUNT #

如果你想保留原来的CASE逻辑，可以把COUNT(1)换成EXISTS——EXISTS只需要找到第一条匹配记录就会停止计算，不需要遍历所有符合条件的行，效率会高很多：

;WITH CTE_Topics AS (
 SELECT TermId, Term, ParentId FROM Terms WITH (NOLOCK)
 WHERE ParentId = 24 AND IsDeleted = 0
 UNION ALL
 SELECT T.TermId, T.Term, T.ParentId FROM Terms T WITH (NOLOCK)
 INNER JOIN CTE_Topics CT ON T.ParentId = CT.TermId AND T.IsDeleted = 0
),
CTE_Filter AS (
 SELECT TermId FROM CTE_Topics WITH (NOLOCK)
 WHERE Term IN ('Topis 1', 'Topis 2', 'Topis 3', 'Topis 4', 'Topis 5')
),
CTE_AR AS (
 SELECT TermId, Term, ParentId, LongDescription FROM TermMappings TM WITH (NOLOCK)
 INNER JOIN Terms TD WITH (NOLOCK) ON TD.TermId = TM.DestinationId AND TD.IsDeleted = 0 AND TM.IsDeleted = 0 AND TM.SourceId = 5121 AND TD.ParentId IN (100, 108)
),
CTE_AOC AS (
 SELECT TermId, Term, ParentId, LongDescription FROM TermMappings TM WITH(NOLOCK)
 INNER JOIN Terms TD WITH(NOLOCK) ON TD.TermId = TM.DestinationId AND TD.IsDeleted = 0 AND TM.IsDeleted = 0 AND TM.SourceId = 5300 AND TD.ParentId IN (100, 108)
 WHERE TermId NOT IN (SELECT TermId FROM CTE_AR)
),
CTE_Result AS (
 SELECT 
  TermId, Term, ParentId, LongDescription,
  CASE WHEN EXISTS (
    SELECT 1 FROM TermMappings WITH(NOLOCK) 
    WHERE SourceId = TermId 
      AND DestinationId IN (SELECT TermId FROM CTE_Filter) 
      AND IsDeleted = 0
  ) THEN 1 ELSE 0 END AS TopicMappingExist
 FROM CTE_AOC
)
SELECT * FROM CTE_Result WHERE TopicMappingExist = 1

额外性能提升建议 #

• 添加复合索引：给TermMappings表创建索引CREATE NONCLUSTERED INDEX IX_TermMappings_SourceId_DestinationId_IsDeleted ON TermMappings (SourceId, DestinationId, IsDeleted)，这能直接加速映射关系的匹配速度。
• 检查NOLOCK合理性：虽然WITH(NOLOCK)能减少锁等待，但会读取未提交的脏数据，如果业务不允许脏读，建议去掉该提示。
• 优化CTE_Filter：如果Terms表的Term字段重复率高，可以考虑给Term字段添加索引，加速CTE_Filter的筛选。

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