优化多层分组场景下的SUM OVER PARTITION BY查询性能

Hey there, let's figure out how to speed up that slow query! First, let's lay out your context clearly:

Sample Data #

Region Country Manufacturer Brand Period Spend
R1 C1 M1 B1 2016 5
R1 C1 M1 B1 2017 10
R1 C1 M1 B1 2017 20
R1 C1 M1 B2 2016 15
R1 C1 M1 B3 2017 20
R1 C2 M1 B1 2017 5
R1 C2 M2 B4 2017 25
R1 C2 M2 B5 2017 30
R2 C3 M1 B1 2017 35
R2 C3 M2 B4 2017 40
R2 C3 M2 B5 2017 45

Required Aggregations #

You need to calculate total spend at these different levels:

1. Total spend across the entire table
2. Total spend per Region
3. Total spend per Region + Country combination
4. Total spend per Region + Country + Manufacturer combination (note: you mentioned "Advertiser" but the data uses Manufacturer)

Current Query (Slow Performance) #

SELECT [Period]
,[Region]
,[Country]
,[Manufacturer]
,[Brand]
,SUM([Spend]) OVER (PARTITION BY [Period]) AS [SumOfSpendWorld]
,SUM([Spend]) OVER (PARTITION BY [Period], [Region]) AS [SumOfSpendRegion]
,SUM([Spend]) OVER (PARTITION BY [Period], [Region], [Country]) AS [SumOfSpendCountry]
,SUM([Spend]) OVER (PARTITION BY [Period], [Region], [Country], [Manufacturer]) AS [SumOfSpendManufacturer]
FROM myTable

Performance Issue #

This query takes over 15 minutes to run on a table with only 450k rows—way slower than it should be. Let's go through some optimizations:

Optimization Suggestions #

1. Ditch Redundant Window Functions If You Don't Need Row-Level Aggregates

Your current query attaches all four aggregation values to every single row in the table. But if your actual goal is just to get the summary totals for each grouping (not row-level context), using GROUP BY queries for each level will be far more efficient. For example:

• Full table total:

  SELECT SUM(Spend) AS SumOfSpendWorld FROM myTable;
  

• Per Region total:

  SELECT Region, SUM(Spend) AS SumOfSpendRegion FROM myTable GROUP BY Region;
  

• Per Region + Country total:

  SELECT Region, Country, SUM(Spend) AS SumOfSpendCountry FROM myTable GROUP BY Region, Country;
  

• Per Region + Country + Manufacturer total:

  SELECT Region, Country, Manufacturer, SUM(Spend) AS SumOfSpendManufacturer FROM myTable GROUP BY Region, Country, Manufacturer;
  

Each of these queries does a single, focused aggregation instead of recalculating four different window functions for every row.

2. Add a Targeted Covering Index

Window functions with PARTITION BY rely heavily on indexes to speed up partitioning and aggregation. Create a covering index that includes all your partition columns plus the Spend column (so the database doesn't need to go back to the main table):

CREATE NONCLUSTERED INDEX IX_myTable_Partition_Spend ON myTable (Period, Region, Country, Manufacturer) INCLUDE (Spend);

This index lets the database pull all needed data directly from the index, cutting down on expensive disk I/O.

3. Remove Unnecessary Period Partitioning (If You Don't Need It)

Looking at your original aggregation requirements, none of them mention splitting totals by Period—but your current query adds PARTITION BY [Period] to every window function. If you don't need totals broken out by year, removing this will drastically reduce computation:

• Full table total becomes SUM(Spend) OVER () AS SumOfSpendWorld
• Per Region total becomes SUM(Spend) OVER (PARTITION BY Region) AS SumOfSpendRegion
  And so on. This eliminates the need to split all aggregations by period, which is a big unnecessary overhead.

4. Analyze the Execution Plan

Always check the query execution plan (in SQL Server, press Ctrl+M before running the query) to spot bottlenecks:

• Look for table scans (a sign you're missing indexes)
• Look for expensive sort operations (if the database is sorting data for partitioning, your index isn't optimized)
• Check if memory constraints are forcing the database to use disk for sorting (this kills performance)

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