Understanding the database connection pool in. net

Summary:

 

 

The connection pool can improve database access performance to a certain extent. This article discusses what is a connection pool, how it improves database access performance, and how. NetCreate a connection pool and add or remove connections.

Introduction

 

 

Connecting to a database is an applicationProgramBut they are crucial. The connection pool is a container for opened and reusable database connections. The connection pool is released from memory only when all database connections are closed. The most basic advantage of using a connection pool is to improve the performance and scalability of applications. The main drawback is that one or more database connections will remain open even if they are not currently in use.Ado. netOfData ProvidersThe connection pool will be used by default. If you do not want to use the connection pool, you must specify"Polling = false". The Connection Pool provides you with idle and reusable database connections, instead of opening a new database connection every time you request database data. When the database connection is closed or released, it is returned to the connection pool to remain idle until new connection requests arrive. If we use the connection pool effectively, opening and closing the database will not be very resource-consuming. This article discusses the related content of the connection pool and how to effectively use the connection pool to improve application efficiency and scalability.

How the connection pool works

 

 

The connection pool contains opened reusable database connections. There can be multiple connection pools in the same application domain at the same time, but the connection pool cannot be shared across application domains. Note: A connection pool is created using a unique connection string. The connection pool is created based on the connection string for the first request for database connection. When another different connection string requests a database connection, another connection pool is created. Therefore, a connection string corresponds to a connection pool instead of a database. See the followingCodeAs shown in

Code 1

 
//Create a connection pool
 
 
 

Sqlconnection= NewSqlconnection ();
 
 
 

 
Sqlconnection. connectionstring= 
 
 
 

"Server = localhost; database = test; user id = joydip; Password = joydip; trusted_connection = false";
 
 
 

 
Sqlconnection. open ();
 

Code 2

 
//Because the connection strings are different, create another connection pool
 
 
 

Sqlconnection Conn= NewSqlconnection ();
 
 
 

 
Sqlconnection. connectionstring= 
 
 
 

"Server = localhost; database = test; user id = test; Password = test; trusted_connection = false";
 
 
 

 
Sqlconnection. open ();
 

Code 3

//Because the connection string and code1Same, no longer create a connection pool.
 
 
 

 
Sqlconnection Conn= NewSqlconnection ();
 
 
 

Sqlconnection. connectionstring= 
 
 
 

 
"Server = localhost; database = test; user id = joydip; Password = joydip; trusted_connection = false";
 
 
 

 
Sqlconnection. open ();
 

When a new database connection request arrives, the connection pool responds without creating a new database connection. That is to say, the database connection can be reused without creating a new connection. This improves the efficiency and scalability of applications. When you close an Open Database Connection in an application, the connection is returned to the connection pool, waiting for the connection to be reconnected until the connection times out. Wait for connection requests with the same database connection information within this time period. If no connection request is sent within this time period, the database connection will be closed and the connection instance will be removed from the connection pool.

When a new connection pool is created, the database connection is added to the pool, and the connection between the connection pool and the pool can be used immediately. The connection pool will fill in the connections with the minimum number of connections specified in the connection string. The connection pool will be removed if the connection is not active for a long time or exceeds the specified lifetime.

The connection pool is maintained by the connection pool manager. When subsequent connection requests arrive, the connection pool manager searches for available idle connections in the connection pool and submits the requests to the application if they exist. The following describes how the Connection Manager works when a new connection request arrives.

·If an unused connection is available, the connection is returned.

·If all connections in the pool are used up, create a new connection and add it to the pool.

· If the connection in the pool reaches the maximum number of connections, the request enters the waiting queue until idle connections are available.

You can control the connection pool by passing parameters in the connection string. Basic parameters include:

·Connect timeout

·Min pool size

·Max Pool siz

·Pooling

To effectively use the connection pool, remember to close the connection immediately after the database operation is complete so that the connection can be returned to the connection pool.

Improve Connection Pool performance

 

 

We should open the connection at the latest time and release the connection at the earliest time, that is, release immediately after use. The database connection should be opened only when the data is actually requested, instead of requesting the connection before use. This reduces the number of available connections in the pool, which is harmful to connection pool operations and application performance. The database connection should be released immediately after use, which can facilitate better use of the Connection Pool, because the connection can be returned to the pool for reuse. The following code enables and disables connections in an application

Code 4

 
Sqlconnection= NewSqlconnection (connectionstring );
 
 
 

 
Try
 
 
 

 
{
 
 
 

Sqlconnection. open ();
 
 
 

 
 // Some code
 
 
 

 
}
 
 
 

 
Finally
 
 
 

{
 
 
 

 
Sqlconnection. Close ();
 
 
 

 
}
 

Code4Available"Using"The following code further simplifies keywords:

Code 5

Using(Sqlconnection= NewSqlconnection (connectionstring ))
 
 
 

 
{
 
 
 

 
Sqlconnection. open ();
 
 
 

 // Some code
 
 
 

}

Note: The above code5In"Using"Keywords will be implicitly generatedTry-finallyBlock

The following lists some important points for better use of the connection pool.

·Enable the connection only when necessary, and close the connection immediately after the operation is completed.

·Close user-defined transactions before closing the connection.

· Make sure that at least one open connection exists in the connection pool.

 

·Avoid using the connection pool when using integrated Authentication

The connection pool can be monitored through the following channels:

·UseSp_whoOrSp_who2Stored Procedure

·UseSQL ServerOfProfiler

 

·Use performance counters of performance monitors

References

 

 

Tuning Up ADO. Net Connection pooling in ASP. NET Applications

Connection pooling for the. NET Framework data provider for SQL Server

The. NET connection pool lifeguard

Ado. Net Connection Pooling explained

Conclusion

 

 

The connection pool is the container of the database connection object, as long as there is an active or open connection, it maintains the active state. When a connection string is used to request a database connection, a new connection pool is allocated. By using the same connection string in the application, we can improve the performance and scalability of the application. However, improper use of the connection pool may bring negative effects to our applications.Msdn"Connection is a powerful tool to improve application performance, but improper use of the connection pool is not beneficial but harmful ". This article discusses the related content of the connection pool and how to effectively use the connection pool to improve the efficiency and scalability of applications.