为什么要添加“开始交易”？ /“commit transaction”语句可以防止内存不足？ [英] why would adding "begin transaction" / "commit transaction" statements prevent running out of memory?

问题描述

我有一个Access项目，其中包含一个处理23,000项记录集的过程。对于每个项目，完成了许多计算并将其保存在结果表中。

在Access 2003中针对SQL-2005数据库已经运行了好几年。我们刚刚将所有内容转换为Windows 7 64位，Access 2010 32位和相同的SQL-2005数据库。当我现在运行该程序时，我收到错误消息97％
通过"内存不足"的方式。使用resmon我看到MSACCESS.EXE在发生错误时使用了1.45GB的RAM（在具有8GB RAM的工作站上）。

我添加了一个connection.BeginTransaction和connection.CommitTransaction在循环中的步骤的开始和结束处，程序现在运行完成。所以现在完成了23,000笔交易。如果没有交易，它就会耗尽内存。

为什么会这样？还有其他方法可以解决这个问题吗？在我看来，这些事务只是浪费了SQL开销。但也许他们不是。

---

Gordon Prince *[phone]

解决方案

< blockquote>

嗨Gordon，

一个非常有趣的观察结果！

我只能猜测，通过在SQL Server中放置事务，它强制访问不缓存结果，而是将此函数卸载到服务器。

相反，也许你可以更新结果集每千条记录左右？

I've got an Access project with a procedure that processes a recordset of 23,000 items. For each item, many calculations are done and saved in a result table.

Things have been running properly for several years in Access 2003 against a SQL-2005 database. We've just converted everything to Windows 7 64-bit, Access 2010 32-bit and the same SQL-2005 database. When I run the procedure now, I get error messages 97% of the way through "out of memory". Using resmon I see that MSACCESS.EXE is using 1.45GB of RAM at the time of the error (on workstations with 8GB of RAM).

I added a connection.BeginTransaction and connection.CommitTransaction at the beginning and end of the steps in the loop and the procedure now runs to completion. So now with 23,000 transactions it finishes. Without the transactions it runs out of memory.

Why is that? And are there other ways to work around this? It seems to me the transactions are simply wasted SQL overhead. But maybe they're not.

---

Gordon Prince * [phone]

解决方案

Hi Gordon,

A very interesting observation!

I can only guess that by placing the transaction in SQL Server it is forcing Access to Not Cache the results and instead off load this function to the server.

Instead perhaps you could update the resultset every thousand records or so?

这篇关于为什么要添加“开始交易”？ /“commit transaction”语句可以防止内存不足？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！