利用T-SQL新特性改写低效查询以提升执行效率

原查询的性能瓶颈在于大量重复的相关子查询——每个子查询都会针对主查询的每一行单独执行，且多个子查询逻辑重复，多次扫描StockPrices表，导致IO和CPU开销剧增。比如获取LatestPrice、PL、YearTPrice的子查询，本质都是访问同一组数据，却重复执行了多次。

可以用**CTE（公用表表达式）**结合窗口函数，提前预计算所有需要的聚合值和关联数据，避免重复扫描表。同时通过合理的索引设计进一步提升性能。

优化后的查询代码 #

WITH StockPriceCTE AS (
    SELECT
        sp.StockId,
        sp.StockDate,
        sp.StockPrice,
        -- 计算当前日期三年前的日期
        DATEADD(YEAR, -3, sp.StockDate) AS YearTDate,
        -- 用窗口函数获取三年前对应日期的最新记录（按StockPriceId倒序取第一条）
        FIRST_VALUE(spm.StockPrice) OVER (
            PARTITION BY sp.StockId, DATEADD(YEAR, -3, sp.StockDate)
            ORDER BY spm.StockPriceId DESC
        ) AS LatestPrice,
        FIRST_VALUE(spm.StockDate) OVER (
            PARTITION BY sp.StockId, DATEADD(YEAR, -3, sp.StockDate)
            ORDER BY spm.StockPriceId DESC
        ) AS LatestDate,
        -- 计算过去三年到当前日期前一天的最低价
        MIN(spm2.StockPrice) OVER (
            PARTITION BY sp.StockId
            ORDER BY sp.StockDate
            ROWS BETWEEN 3 YEARS PRECEDING AND 1 DAY PRECEDING
        ) AS ThreeYearMinPrice
    FROM dbo.StockPrices sp WITH(NOLOCK)
    -- 左连接三年前的StockPrices数据，一次性获取关联记录
    LEFT JOIN dbo.StockPrices spm WITH(NOLOCK)
        ON spm.StockId = sp.StockId
        AND spm.StockDate = DATEADD(YEAR, -3, sp.StockDate)
    -- 左连接用于计算三年区间最低价的StockPrices数据
    LEFT JOIN dbo.StockPrices spm2 WITH(NOLOCK)
        ON spm2.StockId = sp.StockId
        AND spm2.StockDate BETWEEN DATEADD(YEAR, -3, sp.StockDate) AND DATEADD(DAY, -1, sp.StockDate)
)
SELECT
    e.ExchangeId,
    e.ExchangeName,
    s.StockId,
    s.StockName,
    cte.StockDate,
    cte.StockPrice,
    cte.YearTDate,
    cte.LatestPrice AS YearTPrice,
    cte.LatestDate,
    cte.LatestPrice,
    cte.LatestPrice - cte.StockPrice AS PL,
    CASE 
        WHEN cte.StockPrice < cte.ThreeYearMinPrice THEN 'Opportunity' 
        ELSE 'None' 
    END AS [Status]
FROM StockPriceCTE cte
INNER JOIN dbo.Stocks s WITH(NOLOCK)
    ON s.StockId = cte.StockId
INNER JOIN dbo.Exchanges e WITH(NOLOCK)
    ON e.ExchangeId = s.ExchangeId
GO

优化说明 #

• CTE预计算：将所有需要的聚合值、关联数据提前计算完成，只扫描StockPrices表有限次数，避免重复子查询的多次执行。
• 窗口函数替代子查询：用FIRST_VALUE和MIN OVER窗口函数，替代原查询中的TOP 1和MIN子查询，逻辑更清晰且执行效率更高。
• 减少表扫描：通过JOIN一次性获取所需关联数据，避免每行触发子查询的额外开销。

索引优化建议 #

为进一步降低IO开销，建议创建以下非聚集索引，覆盖查询所需的所有字段：

CREATE NONCLUSTERED INDEX IX_StockPrices_StockId_StockDate
ON dbo.StockPrices(StockId, StockDate)
INCLUDE (StockPrice, StockPriceId);

该索引可以直接支持CTE中对StockPrices的过滤、排序和字段读取需求，无需回表查找数据。

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