Left Join中使用OR导致查询超时的解决方案咨询

你的核心问题是：大数据量下，Left Join使用OR关联两个列时，数据库无法有效利用索引，触发全表扫描导致超时。结合ENT.CensusSessions表两列互斥（仅填充其中一列）的特性，提供以下两种高效解决方案：

方案1：拆分OR关联为两个独立的Left Join #

把原来的单个OR条件拆成两个独立的Left Join，分别关联两个ID列，最后用Coalesce合并有效结果。这种方式能让数据库分别利用两个ID列的索引，避免全表扫描：

Select
     Coalesce(ONR.forename,OFR.forename) As Forename
    ,Coalesce(ONR.middlenames,OFR.middlenames) As Middlenames
    ,Coalesce(ONR.surname,OFR.surname) As Surname
    ,Coalesce(ONR.upn,OFR.upn) As UPN
    ,Coalesce(ONR.[schoolcensustableid],OFR.[schoolcensustableid]) As SchoolCensusTableID
    ,CSC.term As Term
    ,CSC.[year] As [Year]
    -- 合并两个Join的结果，取有效数值
    ,Case When Coalesce(TSO_ONR.Sessions, TSO_OFR.Sessions) IS NULL Then Cast('0.00' As Decimal(10,2)) 
          Else Coalesce(TSO_ONR.Sessions, TSO_OFR.Sessions) END As SessionsAuthorised
    ,Case When ONR.termlysessionspossible IS NULL Then Cast('0.00' As Decimal(10,2)) Else ONR.termlysessionspossible END As SessionsPossibleOnRoll
    ,Case When OFR.termlysessionspossible IS NULL Then Cast('0.00' As Decimal(10,2)) Else OFR.termlysessionspossible END As SessionsPossibleOffRoll
    ,ONR.termlysessionseducational As TermlySessionsEducationalOnRoll
    ,OFR.termlysessionseducational As TermlySessionsEducationalOffRoll
    ,ONR.termlysessionsexceptional As TermlySessionsExceptionalOnRoll
    ,OFR.termlysessionsexceptional As TermlySessionsExceptionalOffRoll
    ,ONR.termlysessionsauthorised As TermlySessionsAuthorisedOnRoll
    ,OFR.termlysessionsauthorised As TermlySessionsAuthorisedOffRoll
    ,ONR.termlysessionsunauthorised As TermlySessionsUnauthorisedOnRoll
    ,OFR.termlysessionsunauthorised As TermlySessionsUnauthorisedOffRoll
    ,Coalesce (ONR.DateTimeInserted, OFR.DateTimeInserted) As DateTimeInserted
    ,Coalesce (ONR.DateTimeUpdated,OFR.DateTimeUpdated) As DateTimeUpdated
    ,Coalesce (ONR.MD5Checksum, OFR.MD5Checksum) As MD5Checksum
    ,Coalesce (ONR.LoadID, OFR.LoadID) As LoadID
    ,Coalesce (ONR.UniversalID, OFR.UniversalID) As UniversalID
From STG.Census_PupilOnRoll As ONR
Full Outer Join STG.Census_PupilNoLongerOnRoll As OFR
On ONR.schoolcensustableid = OFR.schoolcensustableid
And ONR.upn = OFR.upn
Left Outer Join STG.Census_SchoolCensus As CSC
On ONR.schoolcensustableid = CSC.schoolcensustableid
Or OFR.schoolcensustableid = CSC.schoolcensustableid
-- 分别关联两个互斥的ID列
Left Outer Join ENT.CensusSessions As TSO_ONR
On TSO_ONR.pupilonrolltableid = ONR.pupilonrolltableid
Left Outer Join ENT.CensusSessions As TSO_OFR
On TSO_OFR.pupilnolongeronrolltableid = OFR.pupilnolongeronrolltableid

方案2：预处理CensusSessions表，生成统一关联键 #

提前给ENT.CensusSessions表生成一个统一的ID列（利用两列互斥的特性），然后用这个单列做等值Join，大幅提升关联效率：

第一步：创建预处理视图/持久化表 #

-- 创建预处理视图（如果数据更新频繁，用视图；如果数据相对稳定，可以做成持久化表）
CREATE VIEW ENT.vw_CensusSessions_Unified AS
Select
    Coalesce(pupilonrolltableid, pupilnolongeronrolltableid) As UnifiedPupilID,
    Sessions -- 原表已聚合，直接取统计值
From ENT.CensusSessions

第二步：主查询用统一ID关联 #

Select
     Coalesce(ONR.forename,OFR.forename) As Forename
    ,Coalesce(ONR.middlenames,OFR.middlenames) As Middlenames
    ,Coalesce(ONR.surname,OFR.surname) As Surname
    ,Coalesce(ONR.upn,OFR.upn) As UPN
    ,Coalesce(ONR.[schoolcensustableid],OFR.[schoolcensustableid]) As SchoolCensusTableID
    ,CSC.term As Term
    ,CSC.[year] As [Year]
    ,Case When TSO.Sessions IS NULL Then Cast('0.00' As Decimal(10,2)) Else TSO.Sessions END As SessionsAuthorised
    ,Case When ONR.termlysessionspossible IS NULL Then Cast('0.00' As Decimal(10,2)) Else ONR.termlysessionspossible END As SessionsPossibleOnRoll
    ,Case When OFR.termlysessionspossible IS NULL Then Cast('0.00' As Decimal(10,2)) Else OFR.termlysessionspossible END As SessionsPossibleOffRoll
    ,ONR.termlysessionseducational As TermlySessionsEducationalOnRoll
    ,OFR.termlysessionseducational As TermlySessionsEducationalOffRoll
    ,ONR.termlysessionsexceptional As TermlySessionsExceptionalOnRoll
    ,OFR.termlysessionsexceptional As TermlySessionsExceptionalOffRoll
    ,ONR.termlysessionsauthorised As TermlySessionsAuthorisedOnRoll
    ,OFR.termlysessionsauthorised As TermlySessionsAuthorisedOffRoll
    ,ONR.termlysessionsunauthorised As TermlySessionsUnauthorisedOnRoll
    ,OFR.termlysessionsunauthorised As TermlySessionsUnauthorisedOffRoll
    ,Coalesce (ONR.DateTimeInserted, OFR.DateTimeInserted) As DateTimeInserted
    ,Coalesce (ONR.DateTimeUpdated,OFR.DateTimeUpdated) As DateTimeUpdated
    ,Coalesce (ONR.MD5Checksum, OFR.MD5Checksum) As MD5Checksum
    ,Coalesce (ONR.LoadID, OFR.LoadID) As LoadID
    ,Coalesce (ONR.UniversalID, OFR.UniversalID) As UniversalID
From STG.Census_PupilOnRoll As ONR
Full Outer Join STG.Census_PupilNoLongerOnRoll As OFR
On ONR.schoolcensustableid = OFR.schoolcensustableid
And ONR.upn = OFR.upn
Left Outer Join STG.Census_SchoolCensus As CSC
On ONR.schoolcensustableid = CSC.schoolcensustableid
Or OFR.schoolcensustableid = CSC.schoolcensustableid
-- 用统一ID做等值Join，数据库可以高效利用索引
Left Outer Join ENT.vw_CensusSessions_Unified As TSO
On TSO.UnifiedPupilID = Coalesce(ONR.pupilonrolltableid, OFR.pupilnolongeronrolltableid)

额外优化建议 #

• 给ENT.CensusSessions表的pupilonrolltableid和pupilnolongeronrolltableid分别创建非聚集索引，加速方案1中的独立Join。
• 如果用方案2，给预处理视图的UnifiedPupilID创建索引（若为持久化表），进一步提升关联效率。
• 检查STG.Census_PupilOnRoll和STG.Census_PupilNoLongerOnRoll的对应ID列是否有索引，无索引则补上。

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