MS Access直通查询性能异常及优化方案咨询

我们公司采用基于Visual FoxPro开发的MRP系统管理库存、生成销售订单、开票等业务，系统支持将表数据导出为Excel表格。我曾将其作为Access数据库的链接表使用，后来因为开发的多数数据库供其他部门使用，且终端用户计算机操作能力有限，转而通过ODBC直接连接MRP系统的.dbf表，免去用户手动导出数据的步骤。

了解到直通查询（pass-through query）通常比链接表后在Access本地运行查询性能更优，测试后确实如此，但这类直通查询仍运行缓慢。示例代码如下：

SELECT sales.Accountno, sales.sono, sales.itemno, sales.datereq, sales.shipvia, sales.orqtyreq, sales.qtyship, sales.custpono, sales.partno, sales.terms, sales.complete, sales.confirmed
FROM sales
WHERE complete = "N" AND confirmed = .T.
ORDER BY sales.Accountno;

该查询仅返回约2000条记录，但运行速度远慢于查询sales表全部约100000条记录的速度。

疑问 #

1. 为何筛选后仅返回2000条记录的查询，比返回全部100000条记录的查询速度更慢？
2. 如何提升直通查询的性能？是否有其他更高效的直接提取MRP表数据的方法？
3. 通过VBA运行查询是否比在查询设计器的SQL视图中运行更优？

补充说明 #

有时查询耗时约5秒（虽慢但可接受），有时则会导致数据库卡顿，耗时数分钟。这是否与其他用户正在使用该MRP表有关？

1. 筛选查询更慢的核心原因 #

• 索引缺失：WHERE条件用到的complete和confirmed字段如果没有联合索引，甚至单个索引都不存在，FoxPro需要对全表逐行扫描来筛选符合条件的记录。而查询全表时无需筛选，直接连续读取数据返回，反而避免了筛选时的全表扫描+逐行判断开销，导致筛选查询更慢。
• 数据分布零散：如果符合complete="N" AND confirmed=.T.的记录在表中分布非常分散，FoxPro需要频繁定位磁盘上的零散数据块，IO开销远大于连续读取全表的IO成本。
• 排序额外开销：查询末尾的ORDER BY sales.Accountno如果没有对应的索引支持，筛选出2000条记录后还需要在服务器端做排序操作；而全表查询若没有排序要求（或全表排序的优化策略不同），返回速度反而更快。

2. 提升直通查询性能的方案 #

• 添加针对性索引：联系MRP系统管理员，在sales表的complete、confirmed字段上创建联合索引，同时给Accountno单独建索引（或把Accountno加入联合索引末尾，这样排序时可直接利用索引，避免额外排序开销）。索引是解决这类筛选+排序查询性能问题最有效的手段。
• 优化SQL语句：
  • 保持字段类型匹配：确保complete（字符型）、confirmed（逻辑型）的条件写法正确，避免隐式类型转换（你的示例SQL写法没问题，保持即可）。
  • 精简返回字段：去掉SELECT列表中不需要的字段，减少跨系统的数据传输量。
• 替代提取方法：
  • 利用FoxPro原生工具生成临时表：若有操作权限，可在FoxPro端先执行筛选查询生成临时.dbf文件，再通过ODBC读取临时表，减少跨系统的查询解析开销。
  • 定时同步数据：如果业务对数据实时性要求不高，每天定时将sales表的增量数据同步到Access本地表，用户查询本地数据的速度会大幅提升。

3. VBA运行与查询设计器的性能对比 #

两者核心性能差异不大，关键还是看查询本身的优化和索引情况。但VBA可以做额外优化：

• 使用ADODB.Recordset执行直通查询时，设置CursorType=adOpenForwardOnly、LockType=adLockReadOnly，这种只读向前游标比默认游标更轻量，能小幅提升速度。
• 可在VBA中控制查询执行时机，比如避开MRP系统的使用高峰，或在查询前关闭其他占用资源的操作。

关于卡顿的原因 #

是的，卡顿情况和其他用户使用MRP表直接相关：

• FoxPro的.dbf采用文件级锁机制，当其他用户对sales表进行写操作（如新增订单、修改状态），会锁定整个表或部分数据块，你的查询需要等待锁释放，从而出现卡顿、耗时变长的情况。
• 高峰时段系统的IO、CPU资源被大量占用，也会拖慢查询的响应速度。

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