在pyodb中执行SELECT查询时，ODBC MS Access驱动程序出现“系统资源超出”错误

Question

我编写了一个python程序，它使用pyodbc与microsoftaccess数据库交互。该数据库包含一个表，其中包括井的测量值（约630万）。在

该计划的基本工作流程是获得化学品清单。然后，对于每种化学品，它将使用select查询来查找表中包含该化学品数据的每个井。另一个查询将为每一个井选择另一个井的所有测量数据。在

一旦它有了这个数据集，它就会计算一些统计数据，用这些数据集为数据集所针对的化学和井对创建一个新行。然后，这些新行中的每一行都被输出到一个新的数据库表中。在

请注意，我使用python的多处理包来允许程序使用更多的cpu容量。程序总是有一个使用者进程，该进程将数据从队列中取出，并将其从输入数据库插入到新的独立数据库中。然后程序有一个可变数量的生产者进程，从队列中提取化学品，处理它们，并将这些数据添加到输出队列中。在

当我在化学物质清单上运行程序时，它有时会出错：

Starting: 01027
Failed on 01027 when selecting wells for chemical.
Failed. Wells processed: 371693
Traceback (most recent call last):
  File "C:\Users\jrutledge\Desktop\well_trend_processor_python_2016\python_processor\well_trends.py", line 230, in read_data_sets_into_queue
    raise e

对于一份96种化学品的清单来说，这种情况至少会发生一次（有时会更多），但不会发生在10种或更少的化学品上，不确定截止点在哪里。此错误消息的预期原因似乎是运行占用太多内存的查询，但这似乎不是这里的结果。一个原因是，它出错的化学物质在两次尝试中并不一致。此外，如果我运行一个包含失效化学物质的更小的清单，那么它们的处理没有错误。在

当我为一种化学品选择油井时，问题似乎发生了：

^{pr2}$

尽管，正如前面所说，这个SQL查询执行时没有问题的次数要比它引起错误的频率高，因此看起来SQL中似乎没有错误。然而，这是程序进行的最密集的查询，因此它将是超出资源的查询。在

如果删除程序的插入部分，此问题仍然存在。这样，它就可以完成正常情况下的所有操作，但是在计算完统计信息之后，它不会将它们插入到新的表中。在

可能是因为我把连接留下的时间太长了。这导致我让python程序为每个化学物质打开一个新的到数据库的连接，然后在处理完该化学物质后关闭它。然而，问题依然存在。在

值得注意的是，总内存使用率永远不会超过60%，无论我如何运行它，所以我认为这不是问题所在。在

此外，数据库的大小小于800MB，这与MS Access数据库的2GB大小限制不符。在

我用一个进程运行这个程序，它成功了。这使我想到，尽管任何select查询对于ODBC驱动程序来说可能不会太昂贵，但同时执行多个非常昂贵的查询可能会导致驱动程序达到其资源限制。现在，我修改了程序并在数据库中执行任何SQL查询时添加了pythonrlock，这样一次只有一个程序可以从数据库中读取数据，这将消除这个问题。昨天成功地完成了一个包含四个进程的运行，我认为这解决了这个问题，但是今天在select well查询中仍然存在相同的错误，即使我只使用一个进程运行它。在

（当我认为这是一个解决方案时，我将此作为一个答案发布，现在已将其删除）

另外，当使用这种方法时，cpu使用率永远不会超过80%，因为进程必须等待另一个进程进行查询，并且仍然出错。这意味着ODBC驱动程序与db的接口在使用上必须有一些编码限制。在

你怎么办你认为是造成这个错误的原因，我应该如何解决它？在

如果你想看到更多的代码，让我知道是哪一部分（有很多）。在

Answer 1

不幸的是，MS Access'System Resources Exceeded是一个有点模糊的消息，它可能与实际的CPU资源、网络环境或低效的SQL查询或VBA模块有关，从而影响引擎的性能。在

但是，您的SQL可以优化。考虑在well查询中使用GROUP BY将DISTINCT子句替换为聚合查询。DISTINCT是大多数rdm中常见的SQL性能消耗，需要使用完整的resultset排序来删除重复项。此外，DISTINCT往往是一个临时修复，用于补偿不充分的数据库设计或计划的过程：

sql_string = """SELECT {0}.Well_ID, PSCODE, STAID, Status
                FROM {0}
                LEFT JOIN {1} ON {0}.Well_ID = {1}.Well_ID
                WHERE STORE_NUM = ?
                GROUP BY {0}.Well_ID, PSCODE, STAID, Status"""\
                .format(well_records_table_name, well_sites_table_name)

在表的设计和其他方面的错误处理可能有帮助。因此，请检查其他问题，包括复杂的嵌套子查询、WHERE和JOIN子句中的函数、非常宽的表的使用（在一对多/多对多关系中规范化的一个符号）；以及大型事务，如make table/append/update大查询，Access为回滚需要保存resultset的副本（有时达到2 GB大小）。在

访问数据库提示

1. 尝试compacting & repair 偶尔一次以避免膨胀和刷新数据库 查询优化器的统计信息。虽然你的数据库是800 MB， 众所周知，数据库在操作期间会扩展，对于上面提到的大型事务，可以达到文件限制。在
2. 除主键/复合键外，在表中使用索引。在表设计视图中，您可以在字段属性部分中选择单个字段作为索引，或者使用SQL的^{}。理想情况下，通过这样的索引连接表。在
3. 将存储的查询保存在Access中，这些查询是预先编译并优化为最佳计划的，并使用PythonSELECT * FROM Query1调用命名对象，而不是动态运行它的引擎。这是关于VBA查询与msaccess中存储查询的常见讨论。甚至使用临时表进行计算或迁移到Python的pandas数据帧进行分析。在
4. 尝试将后端数据库拆分为多个数据库，这样可以减少单个文件的大小。Recall Access可以使用链接表（Python可以访问）链接到其他Access文件，甚至可以使用服务器级rdm（如sqlserver、Oracle、MySQL等）链接到其他Access文件
5. 始终注意Access's limitations，如表列、查询联接、文件/表大小。毕竟，访问是一个文件级的RDM，而不是其基础结构是开放式的服务器RDM。在