获取两个子查询结果差异时遭遇执行超时问题求助

数据老陈

2026-8-16

问题描述

需要对比两个子查询的结果差异，每个子查询涉及约10000条记录。单独运行每个子查询均能成功返回结果，但使用EXCEPT组合后执行超时，错误信息：

[S0000][-2] Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

两个子查询仅在PersonAccount和RelationType的关联方式上有区别：一组使用LEFT JOIN，另一组使用JOIN，原SQL语句如下：

declare @date DATETIME2, @buildingId uniqueidentifier, @companyId uniqueidentifier;
set @date = GETDATE();
set @buildingId = '24f0bd56-5361-4b9f-a04e-83f86cce1628';
set @buildingId = null;

WITH [PASSPORTS]
         AS (SELECT [PersonId],
                    CONCAT([LastName], ' ', [FirstName], ' ', [MiddleName])              AS FullName,
                    ROW_NUMBER() over (partition by d.[PersonId] order by d.[Date] desc) as [Row]
             FROM [dbo].[Document] d),

     [PRIVILEGES] AS
         (SELECT p.Id AS PersonId, priv.Name AS Privileges
          FROM supo.Privilege priv
                   JOIN supo.PersonPrivilege pp ON priv.Id = pp.PrivilegeId
                   JOIN Person p ON pp.PersonId = p.Id),

     [PLACES] AS
         (SELECT r.Id AS RoomId, COUNT(*) as RoomPlaces
          FROM supo.Room r
                   JOIN supo.RoomPlace rp ON r.Id = rp.RoomId
          GROUP BY r.Id)

SELECT *
FROM (SELECT DISTINCT r.Apartment     AS RoomNumber,
                      p.Id            AS PersonId,
                      p.PersonTypeId,
                      p.WorkPlace,
                      p.WorkPosition,
                      a.Id            AS AccountId,
                      pa.Id           AS PersonAccountId,
                      pa.CompanyId    AS CompanyId,
                      a.RentDocument,
                      a.RentDate,
                      a.ClosedDate,
                      a.DateExpiring,
                      rt.Name         as RelationName,
                      a.BuildingId,

                      privs.Privileges,
                      sg.Name         AS [Group],
                      sg.TrainingPeriod,
                      p.FundingTypeId AS FundingTypeId,

                      sg.DateCreated  AS GroupDateCreated,
                      csg.ShortName   AS Institute,
                      places.RoomPlaces,
                      r.TotalSpace    AS RoomSpace,
                      c.Name          AS Citizenship,

                      ps.FullName,
                      p.Gender,
                      a.RoomId,
                      rt.IsRenter

      FROM LivingHistoryItem lhi
               JOIN Person p ON lhi.PersonId = p.Id
               JOIN Account a ON lhi.AccountId = a.Id
               JOIN supo.Room r ON a.RoomId = r.Id
               JOIN Building b ON r.BuildingId = b.Id

               LEFT JOIN PersonAccount pa ON lhi.AccountId = pa.AccountId AND lhi.PersonId = pa.PersonId
               LEFT JOIN RelationType rt ON pa.RelationTypeId = rt.Id
               LEFT JOIN [PASSPORTS] ps ON ps.PersonId = p.Id and ps.Row = 1

               LEFT JOIN [PRIVILEGES] privs ON p.Id = privs.PersonId
               LEFT JOIN [PLACES] places ON places.RoomId = r.Id
               LEFT JOIN supo.StudentGroup sg ON p.StudentGroupId = sg.Id
               LEFT JOIN Company csg ON sg.CompanyId = csg.Id
               LEFT JOIN Citizenship c ON p.CitizenshipId = c.Id

               LEFT JOIN Account a1 ON a.ProlongedAccountId = a1.Id


      WHERE CAST(lhi.DormSettleDate as date) <= @date
        AND (lhi.DormEvictionDate IS NULL OR cast(lhi.DormEvictionDate as date) > @date)
        AND (a.ClosedDate IS NULL OR CAST(a.ClosedDate as DATE) > @date)
        AND CAST(a.RentDate AS DATE) <= @date

        AND (a.ProlongedAccountId IS NULL OR CAST(a1.RentDate AS DATE) > @date)
        AND (b.Id = @buildingId OR @buildingId IS NULL)
        AND (pa.CompanyId = @companyId OR @companyId IS NULL)) t1

EXCEPT

SELECT *
FROM (SELECT DISTINCT r.Apartment     AS RoomNumber,
                      p.Id            AS PersonId,
                      p.PersonTypeId,
                      p.WorkPlace,
                      p.WorkPosition,
                      a.Id            AS AccountId,
                      pa.Id           AS PersonAccountId,
                      pa.CompanyId    AS CompanyId,
                      a.RentDocument,
                      a.RentDate,
                      a.ClosedDate,
                      a.DateExpiring,
                      rt.Name         as RelationName,
                      a.BuildingId,

                      privs.Privileges,
                      sg.Name         AS [Group],
                      sg.TrainingPeriod,
                      p.FundingTypeId AS FundingTypeId,

                      sg.DateCreated  AS GroupDateCreated,
                      csg.ShortName   AS Institute,
                      places.RoomPlaces,
                      r.TotalSpace    AS RoomSpace,
                      c.Name          AS Citizenship,

                      ps.FullName,
                      p.Gender,
                      a.RoomId,
                      rt.IsRenter

      FROM LivingHistoryItem lhi
               JOIN Person p ON lhi.PersonId = p.Id
               JOIN Account a ON lhi.AccountId = a.Id
               JOIN supo.Room r ON a.RoomId = r.Id
               JOIN Building b ON r.BuildingId = b.Id

               JOIN PersonAccount pa ON lhi.AccountId = pa.AccountId AND lhi.PersonId = pa.PersonId
               JOIN RelationType rt ON pa.RelationTypeId = rt.Id
               LEFT JOIN [PASSPORTS] ps ON ps.PersonId = p.Id and ps.Row = 1

               LEFT JOIN [PRIVILEGES] privs ON p.Id = privs.PersonId
               LEFT JOIN [PLACES] places ON places.RoomId = r.Id
               LEFT JOIN supo.StudentGroup sg ON p.StudentGroupId = sg.Id
               LEFT JOIN Company csg ON sg.CompanyId = csg.Id
               LEFT JOIN Citizenship c ON p.CitizenshipId = c.Id

               LEFT JOIN Account a1 ON a.ProlongedAccountId = a1.Id

      WHERE CAST(lhi.DormSettleDate as date) <= @date
        AND (lhi.DormEvictionDate IS NULL OR cast(lhi.DormEvictionDate as date) > @date)
        AND (a.ClosedDate IS NULL OR CAST(a.ClosedDate as DATE) > @date)
        AND CAST(a.RentDate AS DATE) <= @date

        AND (a.ProlongedAccountId IS NULL OR CAST(a1.RentDate AS DATE) > @date)
        AND (b.Id = @buildingId OR @buildingId IS NULL)
        AND (pa.CompanyId = @companyId OR @companyId IS NULL)) t2

优化方案

1. 替换`EXCEPT`为直接过滤逻辑

EXCEPT需要对两个全量结果集做排序、去重和逐行比对，性能开销极大。你的需求本质是找出左连接子查询中有但内连接子查询中没有的记录——也就是那些PersonAccount或RelationType匹配不到的行，直接在左连接查询里加AND (pa.Id IS NULL OR rt.Id IS NULL)过滤即可，无需执行两次全量查询。

2. 移除或延迟`DISTINCT`

原查询中的DISTINCT会强制排序去重，增加额外开销。先确认业务逻辑是否真的会产生重复行：如果LivingHistoryItem到关联表的关联逻辑是一对一或一对多但不会导致重复输出，可直接移除；如果必须保留，也尽量在最小的数据集上执行（比如在CTE中先去重，而非外层查询）。

3. 避免日期函数导致索引失效

原WHERE条件中对日期字段使用CAST(... AS DATE)会让字段上的索引无法被使用，改成预计算截止日期，直接用字段和截止日期比对，减少计算开销并利用索引加速查询。

4. 优化CTE的执行效率

对PASSPORTS CTE，先过滤Row=1再返回结果，减少后续关联的数据量；其他CTE如果被多次使用，可考虑临时表存储结果，避免重复计算。

修改后的SQL

declare @date DATETIME2, @buildingId uniqueidentifier, @companyId uniqueidentifier;
set @date = GETDATE();
set @buildingId = '24f0bd56-5361-4b9f-a04e-83f86cce1628';
set @buildingId = null;

-- 预计算截止日期，避免重复转换操作
DECLARE @cutoffDate DATE = CAST(@date AS DATE);

WITH [PASSPORTS]
     AS (SELECT [PersonId],
                CONCAT([LastName], ' ', [FirstName], ' ', [MiddleName]) AS FullName
         FROM (SELECT [PersonId],
                      [LastName],
                      [FirstName],
                      [MiddleName],
                      ROW_NUMBER() over (partition by d.[PersonId] order by d.[Date] desc) as [Row]
               FROM [dbo].[Document] d) t
         WHERE t.Row = 1),

     [PRIVILEGES] AS
         (SELECT p.Id AS PersonId, priv.Name AS Privileges
          FROM supo.Privilege priv
                   JOIN supo.PersonPrivilege pp ON priv.Id = pp.PrivilegeId
                   JOIN Person p ON pp.PersonId = p.Id),

     [PLACES] AS
         (SELECT r.Id AS RoomId, COUNT(*) as RoomPlaces
          FROM supo.Room r
                   JOIN supo.RoomPlace rp ON r.Id = rp.RoomId
          GROUP BY r.Id)

SELECT 
       r.Apartment     AS RoomNumber,
       p.Id            AS PersonId,
       p.PersonTypeId,
       p.WorkPlace,
       p.WorkPosition,
       a.Id            AS AccountId,
       pa.Id           AS PersonAccountId,
       pa.CompanyId    AS CompanyId,
       a.RentDocument,
       a.RentDate,
       a.ClosedDate,
       a.DateExpiring,
       rt.Name         as RelationName,
       a.BuildingId,

       privs.Privileges,
       sg.Name         AS [Group],
       sg.TrainingPeriod,
       p.FundingTypeId AS FundingTypeId,

       sg.DateCreated  AS GroupDateCreated,
       csg.ShortName   AS Institute,
       places.RoomPlaces,
       r.TotalSpace    AS RoomSpace,
       c.Name          AS Citizenship,

       ps.FullName,
       p.Gender,
       a.RoomId,
       rt.IsRenter

FROM LivingHistoryItem lhi
         JOIN Person p ON lhi.PersonId = p.Id
         JOIN Account a ON lhi.AccountId = a.Id
         JOIN supo.Room r ON a.RoomId = r.Id
         JOIN Building b ON r.BuildingId = b.Id

         LEFT JOIN PersonAccount pa ON lhi.AccountId = pa.AccountId AND lhi.PersonId = pa.PersonId
         LEFT JOIN RelationType rt ON pa.RelationTypeId = rt.Id
         LEFT JOIN [PASSPORTS] ps ON ps.PersonId = p.Id

         LEFT JOIN [PRIVILEGES] privs ON p.Id = privs.PersonId
         LEFT JOIN [PLACES] places ON places.RoomId = r.Id
         LEFT JOIN supo.StudentGroup sg ON p.StudentGroupId = sg.Id
         LEFT JOIN Company csg ON sg.CompanyId = csg.Id
         LEFT JOIN Citizenship c ON p.CitizenshipId = c.Id

         LEFT JOIN Account a1 ON a.ProlongedAccountId = a1.Id

WHERE 
      -- 直接用字段和预计算的截止日期比对，利用索引
      lhi.DormSettleDate <= @cutoffDate
  AND (lhi.DormEvictionDate IS NULL OR lhi.DormEvictionDate > @cutoffDate)
  AND (a.ClosedDate IS NULL OR a.ClosedDate > @cutoffDate)
  AND a.RentDate <= @cutoffDate

  AND (a.ProlongedAccountId IS NULL OR a1.RentDate > @cutoffDate)
  AND (b.Id = @buildingId OR @buildingId IS NULL)
  AND (pa.CompanyId = @companyId OR @companyId IS NULL)
  -- 核心过滤：匹配不到PersonAccount或RelationType的记录，替代EXCEPT逻辑
  AND (pa.Id IS NULL OR rt.Id IS NULL)
-- 仅在确认有重复行时取消注释
-- DISTINCT

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

获取两个子查询结果差异时遭遇执行超时问题求助

1. 替换EXCEPT为直接过滤逻辑 #

2. 移除或延迟DISTINCT #

3. 避免日期函数导致索引失效 #

4. 优化CTE的执行效率 #

1. 替换`EXCEPT`为直接过滤逻辑

2. 移除或延迟`DISTINCT`

3. 避免日期函数导致索引失效

4. 优化CTE的执行效率