Analysis of ASP.net database connection pool-practical skills

Source: Internet
Author: User
Tags connection pooling
1, for a asp.net database connection pool, you can configure a link pool as a link string using a set of name-value pairs. For example, you can configure whether the pool is valid (default is valid), the maximum and minimum capacity of the pool, and the time that the queued request for the link is blocked. The following sample 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 obtain a connection from the connection pool and use the connection without having to re-establish a connection for each connection request. Once a new connection is created and placed in a connection pool, the application can reuse the connection without having to implement the entire database connection creation process.
When an application requests a connection, the connection pool assigns a connection to the application rather than re-establish a connection, and 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 a set of active connections for each given connection configuration. As long as the user calls Open on the connection, the pool process checks to see if there is a connection available in the pool. If a pool connection is available, the connection is returned to the caller instead of opening a new connection. When the application calls close on the connection, the pool process returns the connection to the active connection pool set instead of actually shutting down the connection. Once the connection is returned to the pool, it can be reused in the next Open call.
A pool connection can be established only if the same connection is configured. Ado.net preserves multiple pools at the same time, each configured a pool. Connections are divided into pools by connection strings and by Windows identities (when using integrated security).
Pool connectivity can greatly improve the performance and scalability of your application. By default, connection pooling is enabled in Ado.net. Unless explicitly disabled, the pool process optimizes the connection when it is opened and closed in an application. You can also provide several connection string modifiers to control the behavior of the connection pool
Creation and allocation of pools
When the connection is first opened, a connection pool is created based on the exact matching algorithm, which associates the pool with the connection string in the connection. Each connection pool is associated with a different connection string. When you open a new connection, a new pool is created if the connection string does not exactly match the existing pool. Set up a pool connection by process, by application domain, by connection string, and by Windows identity when using integrated security.
Three new SqlConnection objects were created in the following C # example, but only two connection pools are required for management. Note that the first and second connection strings are different depending on the value assigned to the Initial Catalog.
Copy Code code 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 A.
}

If minpoolsize is not specified or specified as zero in the connection string, the connection in the pool is closed after a period of inactivity. However, if the specified minpoolsize is greater than 0, the connection pool will not be corrupted until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools requires minimal system overhead
Add a connection
The connection pool is created for each unique connection string. When 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 (the default is 100). The connection is released back into the pool when it is turned off or disconnected.
When a SqlConnection object is requested, the object is fetched from the pool if there is a connection available. The connection is available, must not be used, has a matching transaction context or is not associated with any transaction context, and has a valid link to the server.
The connection pool process satisfies these connection requests by reallocating the connection when the connection is released back into the pool. If the maximum pool size has been reached and no available connections exist, the request will be queued. The pool process then attempts to re-establish any connection until the timeout is reached (the default is 15 seconds). Throws an exception if the pool process cannot satisfy the request before the connection times out.
To remove a connection
The connection pooling process periodically scans the connection pool, finds unused connections that have not been closed through close or Dispose, and then re-establish the found connection. If your application does not explicitly close or disconnect its connections, it may take a long time for the connection pool process to re-establish the connection, so it is a good idea to make sure that close and Dispose are called explicitly in the connection.
If the connection is idle for a long time, or if the pool process detects that a connection to the server has been disconnected, the connection pool process removes the connection from the pool. Note that a disconnected connection cannot be detected until you attempt to communicate with the server. If a connection is found to be 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 server that has disappeared, it is possible to remove the connection from the pool even if the connection pool manager does not detect the disconnected connection and marks it as invalid. This is because the system overhead of checking whether the connection is still valid will result in another round-trip to the server, thereby offsetting the advantage of the pool process. When this occurs, the first attempt to use the connection detects whether the connection was disconnected and throws an exception.

Asp. NET database connection pooling Benefits
The main advantage of using connection pooling is performance. The time it takes to create a new database connection depends primarily on the speed of the network and the distance between the application and the database server (the network), and this process is often a time-consuming process. With the database connection pool, the database connection request can be satisfied directly through the connection pool without having to reconnect the request and authenticate to the database server, thus saving time.


Asp. NET database connection Pool disadvantage
There may be a number of unused connections in the database connection pool that have been linked to the database (which means waste of resources).
Tips and Hints
1. Create a connection pool when you need a database connection instead of building it in advance. Once you have finished using the connection, close it immediately, and don't wait for the garbage collector to handle it.
2. Ensure that all user-defined transactions are closed before you close the database connection.
3. Do not close all connections in the database, at least one of the connections in the connection pool is available. If memory and other resources are issues that you must consider first, you can turn off all connections and create a connection pool when the next request arrives
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.