立即关闭与 Jet/ADO 的连接真的明智吗？
我对这个主题进行了广泛的研究，并与我的同事进行了深入的讨论。
普遍的共识似乎是，数据库连接应该在需要时打开并立即关闭。 任何连接的缓存/池化都应该由数据库驱动程序或其他层完成，而不是由应用程序本身完成。
但我对 Jet/ADO 持怀疑态度。 Jet 和/或 ADO 使用读取缓存和延迟写入，这可能会导致 “不同步”连接。 当然，我可以在每次使用 JRO 时重新同步连接，但这似乎很麻烦，而且可能会消耗性能。
我应该采用关闭每个连接并重新同步每个新连接的“最佳实践”，还是应该因为 Jet/ADO 的怪癖而采用全局连接对象？
I've done extensive research on this topic and intensive discussions with my co-workers.
The general consensus seems to be, that a database connection should be opend when needed and closed immediately. Any caching/pooling of connections should be done by the database driver or some other layer, but not by the application itself.
But I have my doubts when it comes to Jet/ADO. Jet and/or ADO use read caches and lazy writes which can lead to "unsynchronized" connections. Of course I could just re-synchronize the connections every time I use them using JRO, but that seems like quite a hassle and a possible performance drain.
Should I go with the "best practice" of closing each connection and re-sync every new connection or should I just go with a global connection object because of the Jet/ADO quirks?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Jet/ACE 的问题之一是创建锁定文件 (*.ldb) 可能会造成严重的性能损失。 此外，如果 LDB 已经存在（因为另一个用户已连接），则设置锁定可能会花费大量时间。
因此，您希望最大限度地减少应用程序的连接次数，因为您可能会浪费时间不断重新创建和重新连接到 LDB 文件。
在 Access 应用程序中，许多程序员的常见做法是维护持久连接。
通常的建议是以隐藏形式打开表或打开表上的记录集。 我更喜欢初始化一个指向后端的数据库变量。 托尼在引用的文章中的反对意见对我来说似乎微不足道——解析连接字符串并不难，例如 Mid(CurrentDB.TableDefs("MyLinkedTable").Connect, 11)。
在非 Access 环境中，您也会遇到同样的问题，因为这是 Jet/ACE 数据库引擎问题。 当然，维护打开连接的特定方法会有所不同，但要点是保持连接打开意味着数据库引擎对锁定文件的争用更少。
One of the issues with Jet/ACE is that the creation of the locking file (*.ldb) can impose a significant performance penalty. Also, if the LDB already exists (because another user is connected), setting up the locking can take significant time.
Thus, you want to minimize the number of times your app connects, because you could be wasting time on constantly recreating and reconnecting to the LDB file.
In Access apps, it's common practice among many programmers to maintain a persistent connection.
The usual recommendation is to open a table in a hidden form or open a recordset on a table. I prefer initializing a database variable pointing to the back end. Tony's objections in the cited article seem pretty insignificant to me -- it's simply not that hard to parse the connect string, e.g., Mid(CurrentDB.TableDefs("MyLinkedTable").Connect, 11).
In a non-Access context, you have the same problem, because this is a Jet/ACE database engine issue. The particular methods of maintaining an open connection would be different, of course, but the point is that keeping it open is going to mean less contention by the db engine for the locking file.
我认为 VB6 桌面应用程序最好使用一个全局连接。 理想情况下，数据库驱动程序会透明地管理连接池，但是当您在 VB6 中将 Jet 与 ADO 或 DAO 一起使用时，它不会：OLE DB Jet Provider 没有连接池。 我相信 .NET 提供商会这样做。
我假设您的应用程序是桌面应用程序 - 您实际上并未指定。 我没有 IIS 应用程序或 COM+ 的经验，所以我不知道什么是最好的。 可能不会使用 Jet Access！ 不过对于桌面应用程序来说这很好。
I think it's best to use one global connection for VB6 desktop applications. Ideally the database driver would manage connection pooling transparently, but when you're using Jet with ADO or DAO in VB6 it doesn't: the OLE DB Jet Provider doesn't have connection pooling. I believe the .NET providers do.
I've assumed your application is a desktop app - you haven't actually specified. I've no experience of IIS applications or COM+ so I don't know what's best there. Probably not to use Jet Access! It's fine for desktop apps though.
我工作的最后两个大型 VB6 + ADO + Access 数据库引擎应用程序（两个不同的团队/雇主）使用了相同的方法：
ADODB.Connection打开ADODB.Recordset使用Setrs.ActiveConnection = 无
The last two large VB6 + ADO + Access database engine apps I worked on (two different teams/employers) used the same approach:
ADODB.ConnectionopenADODB.RecordsetusingSetrs.ActiveConnection = Nothing
大多数现代 DBMS 都提供 ConnectionPool 来管理连接。 但是当你使用
连接.close();
你并没有真正关闭连接，它只是将其返回到池中，当你写入时
连接.open();
事实上，它不会创建新的连接，您只需在 ConnectionPool 中获取准备好的连接并使用它即可。
因此，每次想要将查询传递给 DBMS 时打开连接并关闭它是最好的方法，而且我不会导致性能问题，除了
您不能将同一个连接与多个数据读取器 SQLDataReader 一起使用。
为此，不要使用全球连接，尤其是在开发网站时
因为当您的网站流量很大时，当两个用户解析您的页面时，它会随机抛出异常，导致 2 个数据读取器尝试使用同一连接。
Most of the modern DBMS provide ConnectionPool to mange the connections. however when you use
connection.close();
you don't really close the connection it just return it back to the Pool and when you write
connection.open();
in fact it don't create a new connection you are just take a prepared connection in ConnectionPool and use it.
So Open the connection and close it every time you want to pass the query to the DBMS is the best way and i don't cause problem in performance, In addition to
you can't use the same connection with several data readers SQLDataReader.
for that don't use global connection especially when developing websites
because when there is heavy traffic on your site it will throw exceptions randomly when two users are parsing your pages causing 2 data readers trying to use the same connection.