如何优化无嵌套子查询的最新数据更新存储过程？

针对你现有的存储过程，这里提供两种无需嵌套子查询的优化方案，同时提升执行效率：

方案一：预存最大时间值后更新 #

先一次性获取目标分组的最新CreatedOn值，再执行更新操作，避免每条记录重复计算最大值：

CREATE PROCEDURE [dbo].[UpdateLatestForecast] 
    @PID INT,
    @CID INT
AS
BEGIN
    -- 声明变量存储最新时间，注意变量类型需与CreatedOn字段一致
    DECLARE @LatestCreatedOn DATETIME;

    -- 仅执行一次MAX计算
    SELECT @LatestCreatedOn = MAX(CreatedOn)
    FROM masterTable
    WHERE PID = @PID
      AND CID = @CID;

    -- 批量更新分组内所有记录
    UPDATE masterTable
    SET STATE = CASE 
                    WHEN CreatedOn = @LatestCreatedOn THEN 'Latest data'
                    ELSE 'old data'
                END
    WHERE PID = @PID
      AND CID = @CID;
END

优势：减少重复查询开销，仅执行一次最大值计算，逻辑简单易懂，适合数据量较大的分组场景。

方案二：使用窗口函数标记最新记录 #

通过窗口函数对分组内的记录按CreatedOn排序并排名，直接根据排名更新状态，全程无嵌套子查询：

CREATE PROCEDURE [dbo].[UpdateLatestForecast] 
    @PID INT,
    @CID INT
AS
BEGIN
    WITH RankedRecords AS (
        SELECT 
            STATE,
            -- 按PID、CID分组，CreatedOn降序排名，最新记录排第1
            RANK() OVER (PARTITION BY PID, CID ORDER BY CreatedOn DESC) AS RecordRank
        FROM masterTable
        WHERE PID = @PID
          AND CID = @CID
    )
    UPDATE RankedRecords
    SET STATE = CASE 
                    WHEN RecordRank = 1 THEN 'Latest data'
                    ELSE 'old data'
                END;
END

优势：无需单独查询最大值，逻辑连贯，若后续需要扩展分组规则（比如多字段分组），修改窗口函数的PARTITION BY即可，灵活性更强。

额外性能建议 #

为masterTable创建复合索引IX_masterTable_PID_CID_CreatedOn，包含PID、CID、CreatedOn字段：

CREATE NONCLUSTERED INDEX IX_masterTable_PID_CID_CreatedOn
ON masterTable (PID, CID, CreatedOn);

该索引可以让最大值查询、窗口函数排序操作直接利用索引完成，避免全表扫描，大幅提升执行速度。

内容的提问来源于stack exchange，提问作者MD Akram Alam