Using Connection Pooling

   

Standard client/server applications typically create a single connection to the database and use it for the duration of the session. Because of the stateless nature of the Web, however, a Web-based database application would — using the same approach — have to open and close a new connection for each access. Establishing a connection takes time and memory on both the client and the server. Your application's performance would be seriously impaired if it had to open and close the connection for each data access.

One possible solution for a Web application would be to create the database connection and store the connection as part of the user's session definition. However, this method increases the number of connections as Web site usage increases. This is still not only slow (because you have to open a new connection for each user), but it consumes an enormous amount of resources for each additional connection. The risk is that the number of idle connections will grow so large as to use up all available memory.

Wouldn't it be nice if you could specify the connection attributes once, and create a persistent connection for each data source and recycle the connection as needed? Connection pooling does exactly that. Connection pooling provides a more efficient way to manage connections and share them across different service requests. Connection pooling provides superior performance and reduces the number of idle connections.

The concept of connection pooling is very simple. On each connection request, the connection pool first determines if there is an idle connection in the pool. If so, the connection pool returns that connection instead of making a new connection to the database. Connections are disconnected from the database and taken out of the pool based on a configurable duration option.

Connection pooling is available in the following ways.

• Microsoft Transaction Server (MTS) provides object pooling.
• ODBC 3.0 has a connection pooling feature that is enabled by default in Active Server Pages (ASP).

There is no formula for setting either the maximum number of connections or the number for each user, although Web-based architecture generally uses connection pooling and a design strategy that anticipates one stateless connection for each active Web page.

As a general guideline, you should create data source connections using the ActiveX Data Objects (ADO) Connection object and use MTS to pool and recycle the connection.

For More Information   For more information on using ActiveX Data Objects (ADO) for creating data connections and accessing data, see Data Access Using ActiveX Data Objects (ADO) in Chapter 5 of this book. For more information on using Microsoft Transaction Server for connection pooling, see Performance Value of Object Pooling with MTS in this chapter. For more information on how to store the connection string in the ASP Session object, search online for "Connection Pooling with Web-Based Applications" in MSDN Library Visual Studio 6.0.