为何Excel数据透视表的Running total选项比Access的DSUM运行速度快很多

性能差异核心原因 #

• Access的DSUM是域聚合函数，执行逻辑为O(n²)时间复杂度：计算每一行的运行总计时，都会独立遍历一次所有符合条件的记录完成求和。5万条数据的场景下，累计需要执行约12.5亿次记录扫描，计算量本身就极大。如果日期字段没有建立索引，每次扫描的开销还会进一步升高，最终耗时达到半小时级是非常典型的表现。
• Excel数据透视表的运行总计计算为O(n)时间复杂度：拿到数据源后首先会将全量数据加载到内存中的*Pivot Cache（透视缓存）*中，不需要反复回源查询；后续计算时仅需要按日期排序后单次遍历全量数据，逐行累加当前值到上一行的运行总计结果即可，5万条数据仅需1次遍历就能完成全部计算。

Excel透视表的额外优化机制 #

• 透视缓存为压缩存储结构，占用内存远小于原始数据，CPU读取缓存的速度远高于Access的磁盘/表扫描速度
• 透视表计算引擎采用了向量化执行优化，可批量处理连续数据块，进一步提升了计算效率

Access侧的优化建议 #

如果需要在Access中实现同性能的运行总计，可放弃DSUM方案，改用两种方案：

1. 给日期字段建立索引后，编写带排序的自连接SQL完成累加计算
2. 用VBA读取排序后的记录集，逐行累加计算运行总计后写入表中
   两种方案的耗时都可以降到和Excel透视表相当的水平。

内容的提问来源于stack exchange，提问作者Barrie van Boven