Visual Basic

When to Open and Close Connections

One of the most expensive things you can do with a database (at least in terms of time) is opening a connection to it. Try it out with the RDOvsADO sample project in the samples directory for this chapter. This expense in time has led to most database applications, one way or another, opening connections and keeping them open for extended periods, rather than taking the hit of opening the connections more frequently. You'll often see the strategy of a Visual Basic application opening its database connection on application startup, holding it as Public property of the application (a Global variable in old-time Visual Basic speak), using it everywhere throughout the data access work, and finally closing the connection only when the application shuts down. It once was a good strategy, but the world is changing.

If applications didn't use connections this way, they implemented some kind of connection pooling, wherein a pool of already-open connections was used to supply any application. This is because, although database connections are expensive, they are also finite. To begin with, if only for licensing reasons, we must usually manage them, but also each connection uses up memory on both client and server machines. Thus when certain database access technologies require multiple connections per user or application to carry out a task, they can severely impact application performance.

One of the interesting aspects of building middle-tier objects that talk to the database (rather than allowing an application on the client to open its own database connection) is that objects in the middle-tier can open the connections. They might even be on the same machine as the database server, and they can provide connection pooling. When Microsoft Transaction Server (MTS) was introduced as a middleware provider of both Object Request Brokering services and Transaction Process Monitoring, one of its subsidiary roles was to provide pooling of shared resources. Currently ODBC connection pooling is already provided by MTS. Even without MTS, ODBC 3.5 now provides connection pooling itself. (See Figure 12-1.)

Figure 12-1 Connection pooling with ODBC 3.5

The effect of connection pooling can best be seen by a project that opens components, which each open and use a connection.

With connection pooling in place it becomes viable to change the connection opening and holding strategy. It can even be worthwhile to open a connection every time one is required, use it, and then close it, because in reality the connection will not be opened and closed but pooled, so as to avoid the cost of repeated connection openings. However, the pool can be limited in size so that the minimum number of active useful connections exist, but idle connections using resources without doing work are all but eradicated.