能否优化SQL存储过程中对同一张Users表的多次重复JOIN逻辑？

现有实现的核心痛点 #

• 冗余关联开销：当前8张带笔记/描述字段的业务表，每张都需要单独LEFT JOIN一次Users表获取创建人姓名，累计要和Users表做8次重复关联，查询的逻辑读取开销会跟着关联次数线性上涨。
• 隐含行膨胀问题：原实现将一对多的业务表（如Invoice、ResidentCourse）直接关联到Resident主表，再通过CROSS APPLY拼接各表笔记值，当单个居民存在多笔业务记录时，会生成笛卡尔积式的冗余中间结果，最终靠DISTINCT去重的逻辑，会产生大量无效的计算开销。
• 维护成本高：每新增1张带笔记字段的业务表，都需要手动加1次Users表关联、1个CROSS APPLY的VALUES分支，代码冗余度极高。

核心优化思路 #

彻底摒弃“逐业务表单独关联Users表”的写法，改成统一归集笔记源数据 + 单次关联Users表的逻辑，从根源上减少Join次数，同时消除行膨胀隐患。
具体实现逻辑分为3步：

1. 用UNION ALL把所有8张业务表的笔记数据，统一转换为相同结构的结果集，每个分支仅取核心字段：居民ID、笔记内容、创建时间、创建人ID、笔记类型ID，在分支内部提前过滤掉笔记内容为空、创建时间为空的无效数据，减少后续数据处理量。
2. 对UNION聚合后的统一结果集，仅做1次LEFT JOIN关联Users表，批量匹配所有创建人ID对应的姓名，拼接成完整的创建人展示字段，无论有多少张笔记源表，这步的Users关联次数始终为1。
3. 对最终结果按创建时间倒序排序，加上类型过滤逻辑即可，因为每个业务表分支独立取数，不会产生行重复，不需要额外加DISTINCT去重。

优化后代码示例 #

ALTER PROCEDURE GetNotes
    @ResidentId INT,
    @Type INT = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 统一归集所有源表的笔记数据，仅记录创建人ID，不提前关联Users
    WITH AllNotesRaw AS (
        -- 第1个笔记源：Resident表自身笔记
        SELECT
            ResidentId = R.Id,
            Description = R.Notes,
            CreatedOn = R.CreatedDate,
            CreatedByUserId = R.CreatedBy,
            NoteTypeId = 1
        FROM Resident R
        WHERE R.Id = @ResidentId
            AND R.Notes IS NOT NULL
            AND R.CreatedDate IS NOT NULL
        
        UNION ALL
        -- 第2个笔记源：Invoice表笔记
        SELECT
            ResidentId = I.ResidentId,
            Description = I.Description,
            CreatedOn = I.Date,
            CreatedByUserId = I.CreatedBy,
            NoteTypeId = 3
        FROM Invoice I
        WHERE I.ResidentId = @ResidentId
            AND I.Description IS NOT NULL
            AND I.Date IS NOT NULL
        
        UNION ALL
        -- 第3个笔记源：ResidentCourse表笔记
        SELECT
            ResidentId = RC.ResidentId,
            Description = RC.Notes,
            CreatedOn = RC.Date,
            CreatedByUserId = RC.CreatedBy,
            NoteTypeId = 4
        FROM ResidentCourse RC
        WHERE RC.ResidentId = @ResidentId
            AND RC.Notes IS NOT NULL
            AND RC.Date IS NOT NULL
        
        -- 剩余5个业务表的笔记源，按照上面的格式依次追加UNION ALL分支即可
    )

    -- 2. 仅单次关联Users表，批量获取所有创建人姓名
    SELECT
        anr.ResidentId,
        anr.Description,
        CreatedBy = ISNULL(U.FirstName + ' ' + U.LastName, ''),
        anr.CreatedOn,
        anr.NoteTypeId
    FROM AllNotesRaw anr
    LEFT JOIN Users U 
        ON anr.CreatedByUserId = U.Id
    WHERE (@Type = 0 OR anr.NoteTypeId = @Type)
    ORDER BY anr.CreatedOn DESC
END

额外维护优化建议 #

• 如果后续笔记源表的数量还会持续增加，可以把AllNotesRaw对应的UNION逻辑封装为独立视图vw_AllResidentNotes，存储过程直接查询视图即可，后续新增笔记源仅需修改视图定义，无需改动存储过程逻辑。
• 如果对查询性能有更高要求，可以在业务表的ResidentId、CreatedBy字段上建立合适的非聚集索引，加速单表分支的取数效率。

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