ARTICLE

How connection pooling works in ADO.NET

Posted by Sapna in Articles | ADO.NET in VB.NET on July 14, 2010

Tags: ADO.NET, connection polling, pool maintenance thread., reduce database-related overhead

This article is about how connection pooling makes the process of connecting to ADO.NET data sourcse more efficient.

Tweet
2716
0

Reader Level: Beginner

The basic idea behind connection polling is that a pool of database connections is available to one or more users of a database. When a user attempts to connect to the database, ADO.NET checks to see if an existing connection can be retrieved from a connection pool. If a program requests a connection and one isn't available in the pool, a new connection is created unless the maximum number of the connection for the pool are already in use. In that case, the program must wait for a connection to become available.

Each connection pool contains a limited number of connections. Because of that, if a large number of user attempt to access the database using the same connection string, it's likely that some users will have to wait for a connection.

Importance of connection pooling

In developing an application that will run in a Web-based or multi-tier environment, pooling becomes very important. Making connections to the database can be one of the application's most time-consuming activities. Maintaining connections to the database in the resource state of the Web server can create scalability problems because all users are forced through the same connection object (not to mention that Web servers are almost by definition "stateless"). Opening a new connection on every page of a Web server is bad because it's slow. MDAC pooling provides a way to get the best of both scenarios: a limited number of connections (just enough to match your system's current load) without introducing a scalability bottleneck.

Connection pooling programs let you reduce database-related overhead when it's the sheer number of physical connections dragging performance down. This is particularly important on Windows, where system limitations prevent large number of connections.

How does the pool maintenance thread work?

The behaviour of the pool maintenance thread is determined by the value of four properties of the connection pool:

Aged timeout: The amount of time a connection will be open.
Minimum connections: The minimum number of connections the Connection Manager will keep in a connection factory's free pool.
Reap time: How often the pool maintenance thread will run.
Unused timeout: How long a connection will remain in the free pool before it is closed.