'Best practices' for managing data base connections

Gary Wheeler

My application currently opens its data base connection when it starts, and closes it when it exits. The application runs for long periods (days or even weeks). The problem lies in that it accesses the data base infrequently. Accesses may be minutes or even hours apart in time. In other instances, there may be several accesses within a few milliseconds of each other. At some sites, it appears that the data base server (SQL Server 2005) is dropping the connection because my application is not keeping up a steady traffic of operations. One option would be for me to change my code to open the data base connection, perform the operation, and close the connection, for each and every data base transaction. This approach seems clumsy, however. Does anyone have any better suggestions?

Software Zen: delete this;

Luc Pattyn

Gary Wheeler wrote:

Does anyone have any better suggestions?

No. AFAIK open-use-close is the normal pattern. And connection pooling takes care of most performance issues that result from frequent opens/closes. [ADDED] The above applies when using .NET Framework.[/ADDED] :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? Neen!

modified on Thursday, October 8, 2009 7:47 AM

Gary Wheeler · modified on Thursday, October 8, 2009 7:47 AM

Thanks, Luc! I had a feeling that was the case, but thought I would make sure.

Software Zen: delete this;

Luc Pattyn

you're welcome. :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? Neen!

i i i

will always prefer disconnected architecture means connect to db when their is needs to connect to db mostly in CRUD operations.

Best Of Regards, SOFTDEV If you have knowledge, let others light their candles at it

David Skelly

You could consider some sort of connection pooling strategy. Open-use-close is not very efficient when you are accessing the database very frequently. It is more efficient to pool and reuse open connections in this circumstance. One technique that connection pooling libraries use is to test the connection before returning it from the pool. Then if the connection is bad (e.g. timed out by the database) you can throw it away and open a new one. That way, if your application is making lots of database accesses you avoid the overhead of opening and closing connections all the time. When your application idles and does not access the database for a long time, the connection can be dropped and a new one will be opened, "invisibly" from the point of view of the code using the connection. You don't say what language of framework your application uses, but there are connection pooling libraries available for .NET, Java, Python, Ruby and many others.

Gary Wheeler

I'm using C++/MFC (stop laughing :rolleyes:). The data base activity is an 'add-on' to a process control application. I'm thinking of moving the data base handling off into a separate thread of its own, so that it can do the open/CRUD/close without affecting the other stuff that's going on.

Software Zen: delete this;

PIEBALDconsult

Instantiate the connection once, but open and close it only as needed.