Why one Public OleDbConnection is deprecated? Alternative to solve the bug: too many connections opened

Question

I have to work with a Project made by another developer. A project Win-Form with Visual-Basic code, with MS-Access as db and some OleDbConnections. There is a bug: sometimes the application can't open the OleDbConnection because the max number of connections has been reached on the db. I know the best way to use the connections is this:

Using cn As New OleDbConnction(s)
  ...
  cn.Close()
End Using

But in the project there are many classes to work with the db, and in many of these classes there are OleDbConnections with "Friend" visibility, that are opened and closed in different times. For this reason it's impossible to put all the OleDbConnections in a Using construct, and it's very very hard to find what operation "forgets" to close one of these OleDbConnection.

A possible solution could be to use only one unique public OleDbConnection, and to check, before opening it, if it isn't already opened. But someone have told me it's a very bad practice. I suppose he told me this about the performance, but I don't know it exactly. Can you tell me why one unique public OleDbConnection is so deprecated? Have you got, for me, an "easy" solution for my problem? Thank you, Pileggi

Excitability

Using a single persistent connection with a Jet/ACE database is THE PROPER WAY TO DO IT. It's a file, not a server process at the other end of the connection, and there's significant overhead opening the connection (creating the LDB file). In other words, there's no actual benefit in opening/closing multiple connections and no real downside to using a single persistent connection. The advice you are following is applicable to server-based database engines, but not to file-based databases like Jet/ACE. – Hartshorn

Agreed. Having a single connection object declared as Public is a good idea in your case. – Burhans

Answer 1

From your description, I see a couple of possible issues that could result in your problem:

• nested connections:
  You open multiple connections within each-other
• open/release connections too fast:
  As David-W-Fenton mentionned, with access, every time you open/close a single connection, the lock file will be created/removed. This operation is quite slow and if you quickly open/close the database within you application (execute lots of atomic queries), you may get this issue.

A few possible ways to investigate and solve the issue:

Trace all open/close calls
Add some debug traces that show every time you open and close a connection.
It will allow you to detect nested connections and where your connection pool is being wasted.

Force connection polling An easy 'fix' may be to explicitely set connection pooling in your connection string. It should be the default behaviour, so maybe it won't do anything to solve your problem, but it's so simple that there is no reason not to try it:

OLE DB Services=-1

Use a connection manager class to create/release connections for you.
Replace all the explicit creations of new OleDbConnection and close operations by your own code. This would allow you to always re-use a single existing connection throughout your application and allow you to quickly make tweaks for the whole of your app by centralising the behaviour in a single place.

So why holding a single connection is generally deprecated?

• Generally, you should not keep connections open throughout your application as they force the database server to keep resources available for you, and it decreases the number of client that can connect (there is always a limited number of connections available).
  For Access though -a file-based database without server part- keeping a single connection open is actually preferable because of the delay associated with opening new connections (creation of the lock file). Since Access is not meant to be used with a large number of concurrent users, the resource cost of keeping the connection open is not significant enough to be an issue.
  From simple tests, it can be shown that keeping a connection always open allows subsequent connections to open about 10x faster!

• The OleDb driver does connection pooling for you, so it is able to re-use connections when they are freed.

• By keeping your connections and database operations small and contained, you would be less likely to run into concurrency issues when using threads. Keeping a global connection may become an issue if you are executing multiple operations using the same pipeline to the database.

Moneylender

Answer 2

Just adding some information that works for years successfully for me (it is somewhat similar to what David-W-Fenton suggests)

First, an OleDbConnection to Microsoft Access (MDB, JET) is not using connection pooling. As Microsoft states in KB191572:

Connections that use the Jet OLE DB providers and ODBC drivers are not pooled because those providers and drivers do not support pooling.

Regarding connection pooling, there is also this blog post from Ivan Mitev that states:

So what does this mean? It is apparent that that the presence of an actively opened connection made the test with multiple connection closing and opening finish a lot faster (2-3 times). The only possible explanation for me is that the connection pool is released each time there are no active connections. I have to make further investigations and read something like Pooling in the Microsoft Data Access Components. Or maybe hold a single opened connection just for the sake of keeping the pool alive. This would be ugly, but still it is a good enough workaround! If anyone has a better idea, please share it.

And Microsoft notes in MSDN:

The ADO Connection object implicitly uses IDataInitialize. However, this means your application needs to keep at least one instance of a Connection object instantiated for each unique user—at all times. Otherwise, the pool will be destroyed when the last Connection object for that string is closed.

Based on all this and my own tests, my solution to "simulate" connection pooling even with Microsoft Access databases roughly follows these steps:

1. Open one OleDbConnection to the Access database as early as possible in application lifecycle.
2. Do your normal SQL queries, disposing OleDbConnections as early as possible, just like recommended.
3. Dispose that one always-open OleDbConnection as late as possible in application lifecycle.

This sped up my applications (mostly WinForms) tremendously.

Please note that this also works for Sqlite which seems to not support connection pooling, too.

Fillbert

+1 thanks uwe, except i'm a bit confused - if your application lifecycle is basically "all day" cos it's in use all day ... do you mean to just open a single connection at 9am and close it at 5.30? or do you mean by "application lifecycle" a series of requests over a few seconds? – Nonrestrictive

i guess what i'm saying is ... in your three recommendations 1) and 3) sort of say keep the single global connection as open as long as possible whereas 2) is saying close it as early as possible. it depends what you mean by application lifecycle? – Nonrestrictive

@Nonrestrictive Yes, open once at the beginning. Close when exiting the application. – Fillbert