单条语句拖慢SQL查询效率？求优化方案

咱们先来搞清楚为什么加了那行语句后性能暴跌，然后再给你几个靠谱的优化方案。

为什么执行时间从1秒涨到15秒？ #

你猜的没错！问题就出在那个无分区的全局窗口函数SUM(CASE WHEN F.Order_Status <> 'CANCELLED' AND F.Odet_Line_Number = 1 THEN 1 ELSE 0 END) OVER ()上：

• 之前所有的窗口函数都是按Item_Number分区计算的，数据库可以利用Item_Number的索引或者数据分区，把数据分成小批量处理，计算成本很低。
• 但这个全局窗口不一样——它需要扫描所有符合WHERE条件的数据集来计算总和，相当于在全量数据上做了一次全局聚合。而且因为你用了DISTINCT，数据库的执行计划可能被迫改变（比如从高效的分区扫描变成全表扫描，或者额外做排序/去重操作），这就直接把开销拉满了。
• 更糟的是，全局窗口函数会对每一条记录都重复计算这个总和，相当于做了N次全局聚合（N是结果集行数），这性能能不崩吗？

重写语句提升效率的方法 #

核心思路是：把这个全局聚合的计算提前做一次，而不是让数据库每条记录都重复算。这里给你两种常用的写法：

方法1：用CTE预计算全局总和（推荐，可读性高） #

WITH GlobalOrderStats AS (
    SELECT 
        SUM(CASE 
            WHEN Order_Status <> 'CANCELLED' AND Odet_Line_Number = 1 
            THEN 1 ELSE 0 
        END) AS Total_Valid_Orders
    FROM FinalEcomTable F
    WHERE 1=1 
    AND (F.Company_Code = '09' OR '09' IS NULL) 
    AND (F.Division_Code = '001' OR '001' IS NULL) 
    AND COALESCE(F.Shopify_Ordered, F.Date_Entered) BETWEEN '1/1/2018' AND DATEADD(dayofyear, 1, '9/1/2019')
)
SELECT 
    DISTINCT 
    Master_Item,
    Item_Number,
    '--' Color_Code,
    Description,
    '--' Color_Description,
    -- 以下是你原有所有窗口函数，省略重复部分
    SUM(Unit_Retail) OVER (PARTITION BY Item_Number) Sum_Unit_Retail,
    AVG(Unit_Retail) OVER (PARTITION BY Item_Number) Avg_Unit_Retail,
    -- ...（其他原有字段）
    (DENSE_RANK() OVER (PARTITION BY Item_Number ORDER BY Customer_Purchase_Order_Number ASC) + 
     DENSE_RANK() OVER (PARTITION BY Item_Number ORDER BY Customer_Purchase_Order_Number DESC) - 1) AS Total_Orders_Cont,
    -- 优化后的百分比计算
    Total_Orders_Cont / NULLIF(GOS.Total_Valid_Orders, 0) AS Percent_Of_Orders_Cont
FROM FinalEcomTable F
CROSS JOIN GlobalOrderStats GOS
WHERE 1=1 
AND (F.Company_Code = '09' OR '09' IS NULL) 
AND (F.Division_Code = '001' OR '001' IS NULL) 
AND COALESCE(F.Shopify_Ordered, F.Date_Entered) BETWEEN '1/1/2018' AND DATEADD(dayofyear, 1, '9/1/2019')

方法2：用子查询预计算（兼容老版本数据库） #

如果你的数据库不支持CTE，用子查询也能达到同样效果：

SELECT 
    DISTINCT 
    Master_Item,
    Item_Number,
    '--' Color_Code,
    Description,
    '--' Color_Description,
    -- 原有窗口函数省略
    SUM(Unit_Retail) OVER (PARTITION BY Item_Number) Sum_Unit_Retail,
    -- ...（其他原有字段）
    (DENSE_RANK() OVER (PARTITION BY Item_Number ORDER BY Customer_Purchase_Order_Number ASC) + 
     DENSE_RANK() OVER (PARTITION BY Item_Number ORDER BY Customer_Purchase_Order_Number DESC) - 1) AS Total_Orders_Cont,
    Total_Orders_Cont / NULLIF(GA.Total_Valid_Orders, 0) AS Percent_Of_Orders_Cont
FROM FinalEcomTable F
CROSS JOIN (
    SELECT 
        SUM(CASE 
            WHEN Order_Status <> 'CANCELLED' AND Odet_Line_Number = 1 
            THEN 1 ELSE 0 
        END) AS Total_Valid_Orders
    FROM FinalEcomTable F
    WHERE 1=1 
    AND (F.Company_Code = '09' OR '09' IS NULL) 
    AND (F.Division_Code = '001' OR '001' IS NULL) 
    AND COALESCE(F.Shopify_Ordered, F.Date_Entered) BETWEEN '1/1/2018' AND DATEADD(dayofyear, 1, '9/1/2019')
) AS GA
WHERE 1=1 
AND (F.Company_Code = '09' OR '09' IS NULL) 
AND (F.Division_Code = '001' OR '001' IS NULL) 
AND COALESCE(F.Shopify_Ordered, F.Date_Entered) BETWEEN '1/1/2018' AND DATEADD(dayofyear, 1, '9/1/2019')

额外的性能小技巧 #

• 索引优化：给FinalEcomTable建一个复合索引，覆盖查询用到的过滤字段和计算字段：

  CREATE INDEX IX_FinalEcomTable_QueryOptimized 
  ON FinalEcomTable (Company_Code, Division_Code, Item_Number)
  INCLUDE (Shopify_Ordered, Date_Entered, Order_Status, Odet_Line_Number, Customer_Purchase_Order_Number, Unit_Retail, Unit_MarkDown);
  

  这样数据库可以直接从索引里取数据，不用扫全表。
• 替换DISTINCT：如果Item_Number对应的Master_Item、Description等字段是唯一的，换成GROUP BY Item_Number, Master_Item, Description往往比DISTINCT更高效，因为分组逻辑和窗口函数的分区逻辑更匹配。
• 避免重复过滤：把WHERE条件提到CTE/子查询里，主查询直接用过滤后的数据集，减少数据库重复解析过滤条件的开销。

最后再确认下核心问题 #

没错，就是因为你把窗口从PARTITION BY Item_Number改成了全局无分区，导致数据库需要处理全量数据的聚合，而且重复计算了N次。预计算全局总和后，这个开销就变成了一次性的，性能肯定能回到接近原来的水平。

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