A brief analysis of asp.net database connection pool
- 2020-05-10 17:56:00
- OfStack
1, for ASP.NET database connection pools, you can configure the link pool as a link string using a set of name-value pairs. For example, you can configure whether the pool is valid (the default is valid), the maximum and minimum capacity of the pool, and how long it takes for queued requests to open a link to be 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"
ASP.NET database connection pool summary
Connection pooling allows an application to take one connection from the connection pool and use it without having to reestablish one connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse the connection without having to perform the entire database connection creation process.
When an application requests a connection, the connection pool allocates one connection to the application instead of reestablishing one; When the application finishes using the connection, the connection is returned to the connection pool instead of being released directly.
2,
Connection pooling reduces the number of times a new connection needs to be opened. The pool process retains ownership of the physical connection. Manage connections by keeping 1 set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pool process checks to see if any connections are available in the pool. If a pool connection is available, it is returned to the caller instead of opening a new connection. When the application calls Close on this connection, the pooling process returns the connection to the active connection pool set, rather than actually closing the connection. Once the connection is returned to the pool, it can be reused in the next Open call.
Only the same configured connection can be pooled. ADO.NET maintains multiple pools at the same time, one for each configuration. Connections are split into multiple pools by connection strings and Windows identifiers (when using integrated security).
Pooling connections can greatly improve application performance and scalability. By default, connection pooling is enabled in ADO.NET. Unless explicitly disabled, the pooling process optimizes the connection when it is opened and closed in the application. You can also provide several connection string modifiers to control the behavior of the connection pool
Pool creation and allocation
When the connection is first opened, a connection pool is created based on an exact match algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a different connection string. When a new connection is opened, a new pool is created if the connection string does not exactly match the existing pool. Pool connections by process, by application domain, by connection string, and (when using integrated security) by Windows identity.
In the C# example below, three new SqlConnection objects are created, but only two connection pools are required for administration. Note that the first and second connection strings are different depending on the value assigned to Initial Catalog.
If MinPoolSize is not specified or specified as zero in the connection string, the connection in the pool will be closed after 1 period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool will not be broken until AppDomain is uninstalled and the process has finished. Maintenance of inactive or empty pools requires minimal overhead
Adding a connection
Connection pools are created for each 1-only connection string. When a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirement. Connections are added to the pool as needed, but cannot exceed the specified maximum pool size (100 by default). The connection is released back into the pool when it is closed or disconnected.
When an SqlConnection object is requested, if a connection is available, the object is retrieved from the pool. To be available, the connection must be unused, have a matching transaction context or not associated with any transaction context, and have a valid link to the server.
The connection pooling process satisfies these connection requests by reassigning connections when they are released back into the pool. If the maximum pool size has been reached and no connection is available, the request will be queued. The pool process then attempts to reestablish any connections until the timeout time (15 seconds by default) is reached. If the pool process cannot satisfy the request before the connection times out, an exception is thrown.
Remove the connection
The connection pooling process periodically scans the connection pool for unused connections that have not been closed by Close or Dispose, and re-establishes the found connection. If an application does not explicitly close or disconnect its connection, it can take a long time for the connection pooling process to re-establish the connection, so it is best to make sure that Close and Dispose are explicitly called in the connection.
If the connection is idle for a long time, or if the pool process detects that the connection to the server has been disconnected, the connection pool process removes the connection from the pool. Note that a broken connection can only be detected after an attempt to communicate with the server has been made. 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 there is a connection to a vanished server, it is possible to take the connection out of the pool even if the connection pool manager does not detect a broken connection and marks it as invalid. This is because the overhead of checking that the connection is still valid will cause another round trip to the server, negating the advantage of the pooling process. When this happens, the first attempt to use the connection will detect if the connection has been disconnected and throw an exception.
ASP.NET database connection pool advantages
The primary benefit of using connection pooling is performance. The time it takes to create a new database connection depends on the speed of the network and the distance between the application and the database server, and this is usually a time-consuming process. With database connection pooling, database connection requests can be met directly through the connection pool without the need to reconnect and authenticate to the database server for the request, thus saving time.
ASP.NET database connection pool disadvantage
There may be multiple unused connection 1's in the database connection pool directly connected to the database (which means a waste of resources).
Tips and tricks
1. Create a connection pool when you need a database connection, not in advance. Close the connection as soon as you finish using it. Don't wait for the garbage collector to process it.
2. Ensure that all user-defined transactions are closed before closing the database connection.
3. Do not close all connections in the database; at least one connection in the connection pool is available. If memory and other resources are your first priority, close all connections and create a connection pool when the next request arrives
"Server=(local); Integrated Security=SSPI; Database=Northwind;
Max Pool Size=75; Min Pool Size=5"
ASP.NET database connection pool summary
Connection pooling allows an application to take one connection from the connection pool and use it without having to reestablish one connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse the connection without having to perform the entire database connection creation process.
When an application requests a connection, the connection pool allocates one connection to the application instead of reestablishing one; When the application finishes using the connection, the connection is returned to the connection pool instead of being released directly.
2,
Connection pooling reduces the number of times a new connection needs to be opened. The pool process retains ownership of the physical connection. Manage connections by keeping 1 set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pool process checks to see if any connections are available in the pool. If a pool connection is available, it is returned to the caller instead of opening a new connection. When the application calls Close on this connection, the pooling process returns the connection to the active connection pool set, rather than actually closing the connection. Once the connection is returned to the pool, it can be reused in the next Open call.
Only the same configured connection can be pooled. ADO.NET maintains multiple pools at the same time, one for each configuration. Connections are split into multiple pools by connection strings and Windows identifiers (when using integrated security).
Pooling connections can greatly improve application performance and scalability. By default, connection pooling is enabled in ADO.NET. Unless explicitly disabled, the pooling process optimizes the connection when it is opened and closed in the application. You can also provide several connection string modifiers to control the behavior of the connection pool
Pool creation and allocation
When the connection is first opened, a connection pool is created based on an exact match algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a different connection string. When a new connection is opened, a new pool is created if the connection string does not exactly match the existing pool. Pool connections by process, by application domain, by connection string, and (when using integrated security) by Windows identity.
In the C# example below, three new SqlConnection objects are created, but only two connection pools are required for administration. Note that the first and second connection strings are different depending on the value assigned to Initial Catalog.
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 A.
}
If MinPoolSize is not specified or specified as zero in the connection string, the connection in the pool will be closed after 1 period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool will not be broken until AppDomain is uninstalled and the process has finished. Maintenance of inactive or empty pools requires minimal overhead
Adding a connection
Connection pools are created for each 1-only connection string. When a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirement. Connections are added to the pool as needed, but cannot exceed the specified maximum pool size (100 by default). The connection is released back into the pool when it is closed or disconnected.
When an SqlConnection object is requested, if a connection is available, the object is retrieved from the pool. To be available, the connection must be unused, have a matching transaction context or not associated with any transaction context, and have a valid link to the server.
The connection pooling process satisfies these connection requests by reassigning connections when they are released back into the pool. If the maximum pool size has been reached and no connection is available, the request will be queued. The pool process then attempts to reestablish any connections until the timeout time (15 seconds by default) is reached. If the pool process cannot satisfy the request before the connection times out, an exception is thrown.
Remove the connection
The connection pooling process periodically scans the connection pool for unused connections that have not been closed by Close or Dispose, and re-establishes the found connection. If an application does not explicitly close or disconnect its connection, it can take a long time for the connection pooling process to re-establish the connection, so it is best to make sure that Close and Dispose are explicitly called in the connection.
If the connection is idle for a long time, or if the pool process detects that the connection to the server has been disconnected, the connection pool process removes the connection from the pool. Note that a broken connection can only be detected after an attempt to communicate with the server has been made. 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 there is a connection to a vanished server, it is possible to take the connection out of the pool even if the connection pool manager does not detect a broken connection and marks it as invalid. This is because the overhead of checking that the connection is still valid will cause another round trip to the server, negating the advantage of the pooling process. When this happens, the first attempt to use the connection will detect if the connection has been disconnected and throw an exception.
ASP.NET database connection pool advantages
The primary benefit of using connection pooling is performance. The time it takes to create a new database connection depends on the speed of the network and the distance between the application and the database server, and this is usually a time-consuming process. With database connection pooling, database connection requests can be met directly through the connection pool without the need to reconnect and authenticate to the database server for the request, thus saving time.
ASP.NET database connection pool disadvantage
There may be multiple unused connection 1's in the database connection pool directly connected to the database (which means a waste of resources).
Tips and tricks
1. Create a connection pool when you need a database connection, not in advance. Close the connection as soon as you finish using it. Don't wait for the garbage collector to process it.
2. Ensure that all user-defined transactions are closed before closing the database connection.
3. Do not close all connections in the database; at least one connection in the connection pool is available. If memory and other resources are your first priority, close all connections and create a connection pool when the next request arrives