Analysis of asp.net database connection pool

1. For the ASP. NET database connection pool, you can use a group of name-value pairs to configure the connection pool as a link string. For example, you can configure whether the pool is valid (which is valid by default). The maximum and minimum capacity of the pool is used for the time when the queued requests that open the link are blocked. The following example string configures the maximum and minimum capacity of the pool.
"Server = (local); Integrated Security = SSPI; Database = Northwind;
Max Pool Size = 75; Min Pool Size = 5"
Summary of ASP. NET database connection pool
The connection pool allows applications to obtain a connection from the connection pool and use the connection, without re-establishing a connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse the connection without the entire database connection creation process.
When an application requests a connection, the connection pool allocates a connection for the application instead of creating a new connection. After the application uses the connection, the connection is returned to the connection pool instead of being released directly.
2,
The connection pool reduces the number of times new connections need to be opened. The pool Process maintains the ownership of the physical connection. Manage connections by retaining a set of active connections for each given connection configuration. As long as the user calls Open on the connection, the pool process checks whether there are available connections in the pool. If a pool connection is available, the connection is returned to the caller instead of opening a new connection. When an application calls Close on the connection, the pool process returns the connection to the active connection pool instead of closing the connection. After the connection is returned to the pool, it can be reused in the next Open call.
You can establish a pool connection only when the same connection is configured. ADO. NET retains multiple pools at the same time, and each pool is configured with one. Connections are divided into multiple pools by connection strings and Windows Identifiers (when using integrated security.
Pool connections can greatly improve application performance and scalability. By default, the connection pool is enabled in ADO. NET. Unless explicitly disabled, the pool process will optimize the connection when the connection is opened or closed in the application. You can also provide several connection string modifiers to control connection pool behavior.
Pool creation and allocation
When a connection is opened for the first time, a connection pool is created based on the exact match algorithm, which associates the pool with the connection string in the connection. Each connection pool is associated with different connection strings. When a new connection is opened, if the connection string does not exactly match the existing pool, a new pool is created. Create a pool connection by process, by application domain, by connection string, and by Windows when using integrated security.
In the following C # example, three new SqlConnection objects are created, but only two connection pools are required for management. Note that the first and second connection strings are different based on the value assigned for the Initial Catalog.
Copy codeThe Code is as follows:
Using (SqlConnection connection = new SqlConnection (
"Integrated Security = SSPI; Initial Catalog = Northwind "))
{
Connection. Open ();
// Pool A is created.
}
Using (SqlConnection connection = new SqlConnection (
"Integrated Security = SSPI; Initial Catalog = pubs "))
{
Connection. Open ();
// Pool B is created because the connection strings differ.
}
Using (SqlConnection connection = new SqlConnection (
"Integrated Security = SSPI; Initial Catalog = Northwind "))
{
Connection. Open ();
// The connection string matches pool.
}

If MinPoolSize is not specified in the connection string or is specified as zero, the connection in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool will not be damaged until the AppDomain is detached and the process ends. Non-active or empty pool maintenance requires minimal system overhead
Add connection
The connection pool is created for each unique connection string. After a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirements. The connection is added to the pool as needed, but cannot exceed the specified maximum pool size (default value: 100 ). The connection is released back to the pool when it is closed or disconnected.
If an available connection exists when you request a SqlConnection object, the object is obtained from the pool. The connection must be available, not used, have a matched transaction context or not associated with any transaction context, and have a valid link with the server.
The connection pool process re-allocates connections when the connection is released back to the pool to meet these connection requests. If the maximum pool size is reached and no available connections exist, the request will be queued. Then, the pool process tries to re-establish any connection until the timeout time is reached (the default value is 15 seconds ). If the pool process cannot meet the request before the connection times out, an exception is thrown.
Remove Connection
The connection pool process periodically scans the connection pool to find unused connections that are not closed through Close or Dispose, and reestablishes the established connection. If the application does not explicitly Close or disconnect it, it may take a long time for the connection pool process to re-establish the connection. Therefore, it is best to ensure that Close and Dispose are explicitly called in the connection.
If the connection is idle for a long time or the pool process detects that the connection to the server is disconnected, the connection pool process removes the connection from the pool. Note that the disconnected connection can be detected only after you try to communicate with the server. If a connection is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only after they are closed or re-established.
If a connection exists with the server that has been removed, the connection pool manager may still remove the connection from the pool even if it does not detect a disconnected connection and marks it as invalid. This is because the system overhead of checking whether the connection is still valid will cause another round-trip with the server, thus offsetting the advantages of the pool process. In this case, the first attempt to use the connection will detect whether the connection has been disconnected and cause an exception.

Advantages of ASP. NET database connection pool
The main advantage of using a connection pool is performance. The time required to create a new database connection mainly depends on the speed of the network and the distance between the application and the database server (network). This process is usually a very time-consuming process. After the database connection pool is used, database connection requests can be directly met through the connection pool without re-connecting and authenticating the request to the database server. This saves time.


Disadvantages of ASP. NET database connection pool
There may be multiple unused connections in the database connection pool that have been connected to the database (this means a waste of resources ).
Tips
1. Create a connection pool only when you need a database connection, instead of creating a connection pool in advance. Once you use the connection, immediately close it. Do not wait until the Garbage Collector processes it.
2. Make sure that all user-defined transactions are closed before closing the database connection.
3. Do not close all connections in the database. at least ensure that one connection in the connection pool is available. If the memory and other resources are the first thing you must consider, you can close all connections and create a connection pool when the next request arrives.