我目前正在阅读一本关于 SQL Server 2014 的书。它与我发现的所有其他在线资源一样,告诉您这是一种PERCENTILE_CONT
非常慢的计算中位数的方法,并且没有显示据称糟糕的执行计划。PERCENTILE_CONT
在最新版本(即 2022 或更高版本)的 SQL Server 中,执行此任务仍然非常慢吗?
“非常慢”是主观的,因此表明执行计划在 SQL Server 2014 和 2022 之间没有变化就足够了。我会亲自检查一下,但我拥有的最新服务器是 2012 年版本。
我目前正在阅读一本关于 SQL Server 2014 的书。它与我发现的所有其他在线资源一样,告诉您这是一种PERCENTILE_CONT
非常慢的计算中位数的方法,并且没有显示据称糟糕的执行计划。PERCENTILE_CONT
在最新版本(即 2022 或更高版本)的 SQL Server 中,执行此任务仍然非常慢吗?
“非常慢”是主观的,因此表明执行计划在 SQL Server 2014 和 2022 之间没有变化就足够了。我会亲自检查一下,但我拥有的最新服务器是 2012 年版本。
我想您参考的在线资源之一是计算中位数的最快方法是什么?
下面的“2012_A”和“2012_B”指的是该文章中的查询。
使用类似的测试数据...
2012_A 与 2014 兼容级别
尝试使用与后续测试相同的 SQL Server 2022 实例,但兼容级别为 120 (SQL Server 2014),耗时 1 分 56 秒
2012_2022年A
在 SQL Server 2019+ 中,执行计划现在可以使用批处理模式窗口聚合,这比以前的工作要快得多,运行时间为 7.5 秒,但仍然比该文章中提出的最快方法慢(因此可能升级此方法)方法从“糟糕”到“差”)。
PERCENTILE_CONT
被实现为分析函数,并且全部 10,000,000 个源行被假脱机到右侧窗口聚合中,以便在计算后可以将结果添加到其中。这本质上是资源密集型的。PERCENTILE_CONT
无法作为流聚合实现,因为您需要读取整个流来获取计数,从而了解您关心的行。SQL 性能文章中的“获胜”方法在 2022 年仍然获胜 - 运行时间约为 1 秒。
2012_B
大概结果
不过, SQL Server 2022 确实包含了一个新的聚合函数
APPROX_PERCENTILE_CONT
。由于这是一个聚合函数,因此不需要子句OVER
- 或者TOP 1
我添加到PERCENTILE_CONT
上面的查询中虽然所用时间与 2012_B 相当,但它总体上使用了更多的 CPU 时间,而且无论如何也只返回正确结果的近似值 - 所以对于这个测试,我仍然更喜欢 2012_B
是的。没有一个百分位数设施专门针对中位数情况进行优化。实现的一般性质(对于任何百分位数,而不仅仅是 0.5)意味着它更灵活,但比特定的仅中值窗口函数或聚合效率低。
使用 Martin 的测试数据,我发现 2012_B 运行了大约400ms:
这可以在兼容性级别 150 (SQL Server 2019) 或更高版本下使用行存储上的批处理模式(BMOR) 通过相同基本算法的不同表达式进行改进:
此实现运行时间约为275 毫秒,所有昂贵的操作都使用批处理模式处理(蓝色突出显示)。
注意 BMOR 需要企业版或同等版本。