优化性能低下的T-SQL CASE表达式及MERGE语句性能问题

更新说明 #

应部分用户要求，我已制作了一个示例环境，包含所有相关表、索引及完整MERGE语句（已修改列名等），同时调整了原问题中的CASE表达式以匹配示例环境的列名。

如前所述，MERGE本身并非问题所在——移除用于填充[Approved]列的CASE表达式后，MERGE仅需几秒即可运行完成。

原问题 #

我编写了一条MERGE语句用于整合多个不同表的数据。近期将该方案迁移至新服务器后，性能严重下降：在旧服务器上运行仅需约1.5分钟，新服务器却耗时超25分钟。

我查看执行计划后发现，主要问题出在Table Spool（Lazy Spool）。在新服务器上添加OPTION (NO_PERFORMANCE_SPOOL)选项后，运行时间降至约17.5分钟，但我无法影响新服务器的设置或硬件，且无法继续使用旧服务器，此方向已无解决思路。

MERGE的大部分逻辑都很简单，但其中一个字段通过如下CASE表达式填充：

CASE
  WHEN EXISTS (
    SELECT 1
    FROM [dbo].[Approved_Items] 
    WHERE [ID] IN (
      SELECT [OtherId] 
      FROM (
        SELECT [Source].[OtherId]
        UNION
        SELECT [Source].[Parent.OtherId] AS [OtherId]
        UNION
        SELECT [OtherId] FROM [dbo].[Child_Items] WHERE [Parent.Id] = [Source].[Id]
      ) AS S
      WHERE [OtherId] IS NOT NULL
    )
  ) THEN 1
  ELSE 0
END

这无疑是MERGE中最耗时的部分——移除该CASE表达式后，运行时间仅需8秒。其逻辑为：若条目自身ID、父级ID或任何子级ID存在于[dbo].[Approved_Items]中，则该字段设为1，否则设为0。

我确信此逻辑实现远非最优，但我对T-SQL（SQL Server）不够精通，无法找到更优方案，特此求助。

优化方案 #

原CASE表达式的性能瓶颈在于多层嵌套查询+UNION操作：UNION会触发排序去重，且Lazy Spool会为每一行Source数据重复执行整个子查询，开销极大。可通过以下方式优化：

1. 拆分EXISTS条件，避免嵌套与UNION

将原逻辑拆分为三个独立的EXISTS判断，直接利用索引加速查找：

CASE
    WHEN EXISTS (SELECT 1 FROM [dbo].[Approved_Items] ai WHERE ai.ID = [Source].[OtherId])
        OR EXISTS (SELECT 1 FROM [dbo].[Approved_Items] ai WHERE ai.ID = [Source].[Parent.OtherId])
        OR EXISTS (
            SELECT 1 
            FROM [dbo].[Child_Items] ci
            JOIN [dbo].[Approved_Items] ai ON ci.[OtherId] = ai.ID
            WHERE ci.[Parent.Id] = [Source].[Id]
        )
    THEN 1
    ELSE 0
END

2. 预缓存审批ID，减少重复查询

如果Approved_Items数据量较大，可提前将ID缓存到临时表并创建索引，进一步降低查找开销：

-- 预缓存审批ID并创建索引
SELECT ID INTO #ApprovedIds FROM [dbo].[Approved_Items]
CREATE UNIQUE CLUSTERED INDEX IX_ApprovedIds ON #ApprovedIds(ID)

-- MERGE中使用缓存的ID
CASE
    WHEN EXISTS (SELECT 1 FROM #ApprovedIds ai WHERE ai.ID = [Source].[OtherId])
        OR EXISTS (SELECT 1 FROM #ApprovedIds ai WHERE ai.ID = [Source].[Parent.OtherId])
        OR EXISTS (
            SELECT 1 
            FROM [dbo].[Child_Items] ci
            JOIN #ApprovedIds ai ON ci.[OtherId] = ai.ID
            WHERE ci.[Parent.Id] = [Source].[Id]
        )
    THEN 1
    ELSE 0
END

3. 确保关键索引存在

为进一步提升性能，需确认以下索引已创建：

• [dbo].[Approved_Items]的ID列为主键或有唯一非聚集索引
• [dbo].[Child_Items]创建包含[Parent.Id]和[OtherId]的组合索引，或分别为这两列创建单独索引

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