Keep a few connections open all the time or open/close connections on the fly?

[Robin Tucker]

Just a quick question about connection management. My application will
never need more than 1 or 2 connections about at any given time. Also, I do
not expect many users to be connected at any given time. For efficiency, I
would like to keep connections alive throughout the lifetime of the objects
requiring them, rather than opening a new connection, executing code and
then closing it again. What is the most efficient way of doing this?
Should I perform the open/close or just one open when I create the object
and a close when I dispose of it?

[Erland Sommarskog]

Re: Keep a few connections open all the time or open/close connections on the fly?

Robin Tucker (r.tucker@therm oteknix.com) writes:[color=blue]
> Just a quick question about connection management. My application will
> never need more than 1 or 2 connections about at any given time. Also,
> I do not expect many users to be connected at any given time. For
> efficiency, I would like to keep connections alive throughout the
> lifetime of the objects requiring them, rather than opening a new
> connection, executing code and then closing it again. What is the most
> efficient way of doing this? Should I perform the open/close or just one
> open when I create the object and a close when I dispose of it?[/color]

First of all, what client library are you using?

Some client libraries, ADO and ADO.Net employs connection pooling, so
when you officially disconnects, the client library actually keeps the
connection around for some 60 seconds, in case you like to reuse it.
Connection pooling can be quite essential in web applications.

If you are writing a VB application and you know you will never have
any users connected, I don't see anything wrong in creating a connection
and keep the connection object alive as a global variable.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp