如何优化SQL Azure中多表关联的布尔状态查询性能？

原查询通过LEFT JOIN关联多个子表后使用DISTINCT去重，当子表中存在多条匹配同一PersonID的记录时，会先生成大量重复行，再通过DISTINCT过滤，这会额外消耗CPU、IO资源，是导致DTU耗尽的主要原因。以下是更高效的改写方式：

改写后的SQL #

SELECT 
    P.PersonID,
    CASE WHEN EXISTS (SELECT 1 FROM Visitors.AcademicVisitors av WHERE av.PersonID = p.PersonID) THEN 1 ELSE 0 END AS VisitorInfo,
    CASE WHEN EXISTS (SELECT 1 FROM AssociateMembers.AssociateMembershipDetails amd WHERE amd.PersonID = p.PersonID) THEN 1 ELSE 0 END AS AssociateMemberInfo,
    CASE WHEN EXISTS (SELECT 1 FROM Workload.PostHolderWorkload phw WHERE phw.PersonID = p.PersonID) THEN 1 ELSE 0 END AS WorkloadInfo,
    CASE WHEN EXISTS (SELECT 1 FROM Finance.FundingApplications fa WHERE fa.ApplicantPersonID = p.PersonID) THEN 1 ELSE 0 END AS FundingInfo,
    CASE WHEN EXISTS (SELECT 1 FROM HR.PostHolders ph WHERE ph.PersonID = p.PersonID) THEN 1 ELSE 0 END AS PostHolderInfo,
    CASE WHEN EXISTS (SELECT 1 FROM FacultyOffices.FacultyOfficeHolders foh WHERE foh.PersonID = p.PersonID) THEN 1 ELSE 0 END AS FacultyOfficeHolderInfo,
    CASE WHEN EXISTS (SELECT 1 FROM People.PersonSpecialisms ps WHERE ps.PersonID = p.PersonID) THEN 1 ELSE 0 END AS PersonSpecialismInfo,
    CASE WHEN EXISTS (SELECT 1 FROM Workload.LeaveBooked lb WHERE lb.PersonID = p.PersonID) THEN 1 ELSE 0 END AS PersonLeaveInfo
FROM People.People p

优化逻辑说明 #

1. 用EXISTS替代LEFT JOIN：EXISTS是半连接查询，只要找到匹配的记录就会停止检索，不需要返回子表的具体数据，相比LEFT JOIN能减少数据传输和处理量。
2. 去除DISTINCT：因为每个EXISTS判断都是独立的，不会产生重复行，因此不需要再用DISTINCT去重，省去了大量的去重计算开销。

额外优化建议 #

确保各子表的关联字段（如Visitors.AcademicVisitors.PersonID、Finance.FundingApplications.ApplicantPersonID等）都创建了非聚集索引，这样EXISTS查询能快速定位到匹配的记录，进一步降低DTU消耗。

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