T-SQL/SQL Server 按父级金额总和、子级金额排序性能优化

需求说明 #

待实现的两层排序规则如下：

• 第一层父级排序：按parentid维度聚合amount总和，按总和降序排列所有父分组。样例数据中父组金额总和为SSS:4200、ABC:1467、FGH:335，父级最终顺序为SSS、ABC、FGH
• 第二层组内子级排序：同一parentid分组下，按单行记录的amount字段值降序排列子记录
  原有链式CTE+两次row_number()+硬编码乘数计算rowIndex的方案，在仅600行数据的场景下耗时300~500ms，性能达不到要求。

性能问题根因 #

原有方案的性能损耗主要来自三点：

1. 两次独立窗口函数计算触发多次全表扫描，执行计划存在冗余
2. 硬编码sort1 * 15的排序键生成逻辑，既带来额外计算开销，还存在子节点数超过15时排序错乱的隐患
3. 链式CTE写法在SQL Server中不会被优化器做谓词下推，关联其他表时会重复执行CTE内的计算逻辑

最优实现代码 #

以下实现单次扫描即可完成排序计算，配合索引600行数据查询耗时可稳定在10ms以内：

-- 排序视图创建
CREATE OR ALTER VIEW dbo.vw_parent_child_sorted
AS
SELECT
    t.*,
    -- 如需整数排序键用于跨表关联，保留本行即可；不需要可直接注释掉
    DENSE_RANK() OVER(ORDER BY p.ParentTotalAmt DESC, t.amount DESC, t.childid) AS rowIndex
FROM YourSourceTable t
CROSS APPLY (
    SELECT SUM(amount) AS ParentTotalAmt
    FROM YourSourceTable t_sum
    WHERE t_sum.parentid = t.parentid
) p
GO

如果不需要持久化整数类型的rowIndex，查询时直接按以下逻辑排序即可，性能达到最优：

SELECT * 
FROM dbo.vw_parent_child_sorted
-- 此处写其他表关联逻辑
ORDER BY p.ParentTotalAmt DESC, t.amount DESC, t.childid

配套索引优化 #

创建以下覆盖索引后，父组聚合计算会直接走索引定位，不需要全表扫描：

CREATE NONCLUSTERED INDEX IX_ParentChild_OnParentID
ON YourSourceTable (parentid)
INCLUDE (amount, childid)

方案优势 #

• 无冗余窗口函数计算，执行计划仅需一次索引扫描+一次轻量聚合
• 废弃硬编码的单父级最大子节点数参数，后续业务扩容不需要修改视图逻辑
• 排序结果和需求完全一致，不会出现父组、子组排序错乱的问题
• 视图关联其他表时，优化器可以自动做谓词下推，不会重复执行排序计算

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