Microsoft Access中WHERE与HAVING性能差异及重复过滤提速原因咨询

现象描述 #

在Microsoft 365 Access（版本2110 Build 16.0.14527.20234 64位）中，针对多表连接查询，将tblDailyFactor.Date=#8/31/2022#这个过滤条件同时放在WHERE和HAVING子句中时，查询运行速度比仅在WHERE中过滤快6倍以上：

• 仅WHERE过滤：运行时间1.01秒
• WHERE+HAVING重复过滤：运行时间0.16秒

两个查询的代码如下：

仅WHERE过滤的查询（1.01秒）

SELECT tblDailyFactor.Date, tblStaticData1.Name
FROM ((((tblSubTransactions INNER JOIN tblTransactions ON tblSubTransactions.ReferenceNumber = tblTransactions.ReferenceNumber)
    INNER JOIN tblAccounts ON tblTransactions.LocalID = tblAccounts.LocalID)
    INNER JOIN tblStaticData2 ON tblAccounts.LocalID = tblStaticData2.LocalID)
    INNER JOIN tblStaticData1 ON tblStaticData2.GlobalID = tblStaticData1.GlobalID)
    INNER JOIN tblDailyFactor ON tblStaticData1.GlobalID = tblDailyFactor.GlobalID
WHERE (((tblTransactions.Date)<=#8/31/2022#) AND ((tblAccounts.Status)='Active') AND ((tblDailyFactor.Date)=#8/31/2022#))
GROUP BY tblDailyFactor.Date, tblStaticData1.Name
HAVING ((Sum(tblSubTransactions.BalanceUSD))>0.009);

WHERE+HAVING重复过滤的查询（0.16秒）

SELECT tblDailyFactor.Date, tblStaticData1.Name
FROM ((((tblSubTransactions INNER JOIN tblTransactions ON tblSubTransactions.ReferenceNumber = tblTransactions.ReferenceNumber) 
    INNER JOIN tblAccounts ON tblTransactions.LocalID = tblAccounts.LocalID) 
    INNER JOIN tblStaticData2 ON tblAccounts.LocalID = tblStaticData2.LocalID) 
    INNER JOIN tblStaticData1 ON tblStaticData2.GlobalID = tblStaticData1.GlobalID)
    INNER JOIN tblDailyFactor ON tblStaticData1.GlobalID = tblDailyFactor.GlobalID
WHERE (((tblTransactions.Date)<=#8/31/2022#) AND ((tblAccounts.Status)='Active') AND ((tblDailyFactor.Date)=#8/31/2022#))
GROUP BY tblDailyFactor.Date, tblStaticData1.Name
HAVING (((Sum(tblSubTransactions.BalanceUSD))>0.009) AND ((tblDailyFactor.Date)=#8/31/2022#));

原因解析 #

这是Access的Jet/ACE查询优化器的特殊行为导致的，核心原因如下：

1. 执行计划的差异：
   理论上WHERE用于分组前过滤行，HAVING用于分组后过滤聚合结果，但Access优化器对多表连接+分组的查询处理逻辑不够智能。当在HAVING中重复添加tblDailyFactor.Date的过滤条件时，优化器可能重新评估执行路径，选择更高效的策略——比如提前对tblDailyFactor按日期筛选，减少后续连接和分组需要处理的数据量，而仅WHERE的版本可能没有触发这个优化。

2. GROUP BY字段的过滤利用：
   tblDailyFactor.Date是GROUP BY的字段之一，分组后每个组的Date值唯一。HAVING中的日期条件相当于直接筛选目标日期的分组，优化器可能利用tblDailyFactor.Date上的索引快速定位符合条件的分组，避免了对所有分组进行聚合后的筛选，大幅减少计算量。

3. 索引使用的触发：
   如果tblDailyFactor.Date存在索引，仅WHERE的版本可能因多表连接的顺序问题，导致索引无法被有效利用；而HAVING中重复的条件可能让优化器强制使用索引来筛选分组结果，提升查询效率。

实际应用建议 #

• 虽然重复条件能提升性能，但需保证逻辑一致性：由于tblDailyFactor.Date在GROUP BY中，HAVING中的日期条件与WHERE中的逻辑等价，不会改变查询结果。
• 优先检查表的索引是否合理：为tblDailyFactor.Date、各表的连接字段（如GlobalID、ReferenceNumber）建立索引，从根源优化查询性能，减少对“重复条件”这类技巧的依赖。
• 可通过Access的显示计划功能（查询选项卡→显示计划）查看两个查询的执行计划差异，明确优化器的具体执行路径，针对性调整查询或索引。

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