SQL Server子查询性能优化求助：滚动订单数量计算过慢

需求 #

计算每个销售订单行对应的零件，在该订单创建日期到发货日期之间的总数量。

现有性能问题 #

移除rollingQty字段后，查询150万条数据仅需9秒，但包含该字段的语句已运行30分钟仍未完成。

已尝试的方案 #

1. 相关子查询方案（性能极差） #

原查询语句：

SELECT sol.sales_order_quantity AS sales_order_quantity,
       sol.business_unit_key AS business_unit_key,
       sol.order_date_key AS order_date_key,
       sol.ship_day_key AS ship_day_key,
       sol.promised_ship_day_key AS promised_ship_day_key,
       sol.part_key AS part_key,
       sol.sales_order_line_classification_key AS sales_order_line_classification_key,
       sol.last_supplier_key AS last_supplier_key,
       sol.part_description AS part_description,
       sol.unit_price_usd AS unit_price_usd,
       sol.customer_discount_usd AS customer_discount_usd,
       sol.sales_order_number,
       sol.sales_order_line,
       sol.shipping_record_id,
       ISNULL(
       (
           SELECT SUM(solp.sales_order_quantity)
           FROM edap_dw.fact_sales_order_line solp
               INNER JOIN edap_dw.fact_sales_order_header sohp
                   ON solp.business_unit_key = sohp.business_unit_key
                      AND solp.sales_order_number = sohp.sales_order_number
                       AND ISNULL(sohp.Drop_ship, 'NO') <> 'YES'
           WHERE (sol.part_key) = solp.part_key
           AND (solp.sales_order_quantity * solp.unit_price_usd) <> 0
                 AND (sol.order_date_key) >= solp.order_date_key
                 AND (sol.order_date_key) <= CASE
                                                 WHEN solp.ship_day_key = -1 THEN
                                                     FORMAT(GETDATE(), 'yyyyMMdd')
                                                 ELSE
                                                     solp.ship_day_key
                                             END
       ),
       0
             ) AS rollingQty
FROM edap_dw.fact_sales_order_line sol
WHERE sol.order_date_key > 20191231
AND (sol.sales_order_quantity * sol.unit_price_usd) <> 0
AND CASE
          WHEN sol.business_unit_key = 10
               AND sol.sales_order_number LIKE '%-%' THEN
              0
          ELSE
              1
      END = 1

简化版查询（性能同样糟糕）：

SELECT sol.sales_order_number,
       sol.sales_order_line,
       sol.shipping_record_id,
       ISNULL(
       (
           SELECT SUM(solp.sales_order_quantity)
           FROM edap_dw.fact_sales_order_line solp
               INNER JOIN edap_dw.fact_sales_order_header sohp
                   ON solp.business_unit_key = sohp.business_unit_key
                      AND solp.sales_order_number = sohp.sales_order_number
           WHERE (sol.part_key) = solp.part_key
                 AND (sol.order_date_key) >= solp.order_date_key
                 AND (sol.order_date_key) <= CASE
                                                 WHEN solp.ship_day_key = -1 THEN
                                                     20230921
                                                 ELSE
                                                     solp.ship_day_key
                                             END
       ),
       0
             ) AS rollingQty
FROM edap_dw.fact_sales_order_line sol
WHERE sol.order_date_key > 20191231

2. SUM OVER 窗口函数尝试（未实现正确过滤） #

尝试用窗口函数但无法正确添加日期范围过滤：

WITH SalesOrderLines
AS (SELECT sol.sales_order_quantity AS sales_order_quantity,
           sol.business_unit_key AS business_unit_key,
           sol.order_date_key AS order_date_key,
           sol.ship_day_key AS ship_day_key,
           sol.promised_ship_day_key AS promised_ship_day_key,
           sol.part_key AS part_key,
           sol.sales_order_line_classification_key AS sales_order_line_classification_key,
           sol.last_supplier_key AS last_supplier_key,
           sol.part_description AS part_description,
           sol.unit_price_usd AS unit_price_usd,
           sol.customer_discount_usd AS customer_discount_usd,
           sol.sales_order_number,
           sol.sales_order_line,
           sol.shipping_record_id,
           CASE
               WHEN sol.ship_day_key = -1 THEN
                   FORMAT(GETDATE(), 'yyyyMMdd')
               ELSE
                   sol.ship_day_key
           END CalculatedShipDate,
           ISNULL(soh.Drop_ship, 'NO') IsDropShip,
           CASE
               WHEN ISNULL(soh.Drop_ship, 'NO') <> 'YES' THEN
                   sol.sales_order_quantity
               ELSE
                   0
           END CalculatedSalesQuantity
    FROM edap_dw.fact_sales_order_line sol
        INNER JOIN edap_dw.fact_sales_order_header soh
            ON sol.business_unit_key = soh.business_unit_key
               AND sol.sales_order_number = soh.sales_order_number
    WHERE sol.order_date_key > 20191231
          AND (sol.sales_order_quantity * sol.unit_price_usd) <> 0
          AND CASE
                  WHEN sol.business_unit_key = 10
                       AND sol.sales_order_number LIKE '%-%' THEN
                      0
                  ELSE
                      1
              END = 1)
SELECT *,
       sol.sales_order_quantity,
       SUM(sol.CalculatedSalesQuantity) OVER (PARTITION BY sol.business_unit_key,
                                                           sol.part_key
                                              ORDER BY sol.business_unit_key,
                                                       sol.part_key,
                                                       sol.CalculatedShipDate,
                                                       sol.sales_order_number
                                             ) rollingQty
FROM SalesOrderLines sol
ORDER BY sol.business_unit_key,
         sol.part_key,
         sol.CalculatedShipDate;

优化方案 #

1. 预计算有效数据集+自连接聚合 #

先预处理所有符合条件的订单行，计算好每个行的有效日期范围，再通过自连接匹配日期条件并聚合，避免逐行执行的相关子查询：

WITH PreprocessedLines AS (
    SELECT 
        sol.part_key,
        sol.order_date_key,
        CASE 
            WHEN sol.ship_day_key = -1 THEN CONVERT(INT, FORMAT(GETDATE(), 'yyyyMMdd'))
            ELSE sol.ship_day_key 
        END AS calculated_ship_date,
        sol.sales_order_quantity,
        -- 保留原查询需要的所有字段
        sol.business_unit_key,
        sol.ship_day_key,
        sol.promised_ship_day_key,
        sol.sales_order_line_classification_key,
        sol.last_supplier_key,
        sol.part_description,
        sol.unit_price_usd,
        sol.customer_discount_usd,
        sol.sales_order_number,
        sol.sales_order_line,
        sol.shipping_record_id
    FROM edap_dw.fact_sales_order_line sol
    INNER JOIN edap_dw.fact_sales_order_header soh
        ON sol.business_unit_key = soh.business_unit_key
        AND sol.sales_order_number = soh.sales_order_number
    WHERE 
        sol.order_date_key > 20191231
        AND (sol.sales_order_quantity * sol.unit_price_usd) <> 0
        AND ISNULL(soh.Drop_ship, 'NO') <> 'YES'
        AND NOT (sol.business_unit_key = 10 AND sol.sales_order_number LIKE '%-%')
)
SELECT 
    pl.*,
    ISNULL(SUM(pl2.sales_order_quantity), 0) AS rollingQty
FROM PreprocessedLines pl
LEFT JOIN PreprocessedLines pl2
    ON pl.part_key = pl2.part_key
    AND pl.order_date_key >= pl2.order_date_key
    AND pl.order_date_key <= pl2.calculated_ship_date
GROUP BY 
    pl.part_key,
    pl.order_date_key,
    pl.calculated_ship_date,
    pl.sales_order_quantity,
    pl.business_unit_key,
    pl.ship_day_key,
    pl.promised_ship_day_key,
    pl.sales_order_line_classification_key,
    pl.last_supplier_key,
    pl.part_description,
    pl.unit_price_usd,
    pl.customer_discount_usd,
    pl.sales_order_number,
    pl.sales_order_line,
    pl.shipping_record_id
ORDER BY 
    pl.business_unit_key,
    pl.part_key,
    pl.calculated_ship_date;

2. 索引优化 #

• 给edap_dw.fact_sales_order_line创建复合索引：

  CREATE NONCLUSTERED INDEX IX_fact_sales_order_line_part_date 
  ON edap_dw.fact_sales_order_line (part_key, order_date_key, ship_day_key)
  INCLUDE (sales_order_quantity, unit_price_usd, business_unit_key, sales_order_number);
  

• 给edap_dw.fact_sales_order_header创建复合索引：

  CREATE NONCLUSTERED INDEX IX_fact_sales_order_header_bu_order 
  ON edap_dw.fact_sales_order_header (business_unit_key, sales_order_number)
  INCLUDE (Drop_ship);
  

3. 简化过滤条件 #

将原查询中复杂的CASE过滤条件改为更简洁的逻辑，帮助SQL优化器生成更优执行计划：
原条件：

AND CASE
          WHEN sol.business_unit_key = 10
               AND sol.sales_order_number LIKE '%-%' THEN
              0
          ELSE
              1
      END = 1

改为：

AND NOT (sol.business_unit_key = 10 AND sol.sales_order_number LIKE '%-%')

4. 物化预计算数据集 #

如果预计算的数据集较大，可将PreprocessedLinesCTE改为临时表并创建索引，进一步提升连接性能：

SELECT 
    sol.part_key,
    sol.order_date_key,
    CASE 
        WHEN sol.ship_day_key = -1 THEN CONVERT(INT, FORMAT(GETDATE(), 'yyyyMMdd'))
        ELSE sol.ship_day_key 
    END AS calculated_ship_date,
    sol.sales_order_quantity,
    sol.business_unit_key,
    sol.ship_day_key,
    sol.promised_ship_day_key,
    sol.sales_order_line_classification_key,
    sol.last_supplier_key,
    sol.part_description,
    sol.unit_price_usd,
    sol.customer_discount_usd,
    sol.sales_order_number,
    sol.sales_order_line,
    sol.shipping_record_id
INTO #PreprocessedLines
FROM edap_dw.fact_sales_order_line sol
INNER JOIN edap_dw.fact_sales_order_header soh
    ON sol.business_unit_key = soh.business_unit_key
    AND sol.sales_order_number = soh.sales_order_number
WHERE 
    sol.order_date_key > 20191231
    AND (sol.sales_order_quantity * sol.unit_price_usd) <> 0
    AND ISNULL(soh.Drop_ship, 'NO') <> 'YES'
    AND NOT (sol.business_unit_key = 10 AND sol.sales_order_number LIKE '%-%');

CREATE NONCLUSTERED INDEX IX_temp_part_date ON #PreprocessedLines (part_key, order_date_key, calculated_ship_date);

然后基于临时表执行后续的自连接聚合逻辑。

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