Databases Reference
In-Depth Information
at the time the connection pool is populated with connections. Because the con-
nections in the pool are actually used by the application, performance improves
significantly. Obtaining a connection becomes one of the fastest operations an
application performs instead of one of the slowest.
Connection pooling for ADO.NET is not part of the .NET Framework. To
use connection pooling, the data provider or your application must implement
it. As of the publishing date of this topic, most commercial ADO.NET data
providers provide connection pooling. Check your own data provider to verify
that it provides this functionality. For all commercial ADO.NET providers that
offer connection pooling, connections are pooled by default.
Although obtaining connections from a pool is efficient, when your applica-
tion opens and closes connections impacts the scalability of your application.
When opened, connections are marked as “in use” by the pool manager. When
closed, connections are marked as “not in use” and are made available for other
users. Open connections just before the user needs them, not sooner, to mini-
mize the time that the physical connection is marked “in use.” Similarly, close
connections as soon as the user no longer needs them so that they are available
for other users.
For ADO.NET, each unique connection string creates a connection pool
(except in the case of reauthentication). Once created, connection pools are not
closed until the data provider is unloaded. Typically, more memory is required to
manage multiple connection pools. However, it's important to understand that
it's the number of connections, not the number of connection pools, that con-
sumes significant memory. In a well-designed connection pooling implementa-
tion, the maintenance of inactive or empty connection pools involves minimal
system overhead.
To minimize the number of connections required in a connection pool to
service users, you can switch a user associated with a connection to another user
if your data provider supports a feature known as reauthentication. Minimizing
the number of connections conserves memory and can improve performance.
See “Using Reauthentication with Connection Pooling,” page 232.
See Chapter 8, “Connection Pooling and Statement Pooling,” for details
about connection pooling.
Some applications are not good candidates for using connection pooling. See
“When Not to Use Connection Pooling,” page 15, for examples.
