SQL Server database connection pool in ADO. NET

Connecting to the database server usually consists of several steps that take a long time. A physical channel (such as a socket or named pipe) must be established, and a handshake must be performed with the server for the first time. The connection string information must be analyzed and the server must authenticate the connection, check must be run to register in the current transaction, and so on.
In fact, most applications only use one or several different connection configurations. This means that many identical connections will be opened and closed repeatedly during application execution. To minimize the cost of opening a connection, ADO. NET uses an optimization method called connection pool.

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 the application calls Close for 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. The pool connection is also established based on whether the connection has been registered in the transaction.

Pool connections can significantly 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 the behavior of the connection pool.

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 a different connection string. 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. The connection string must also be completely matched. The keywords provided for the same connection in different order are allocated to a separate pool.

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.

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. Maintenance of inactive or empty pools only requires minimal system overhead.

Note:

When an error occurs, such as failover, the pool is automatically cleared.

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.

Warning:
We strongly recommend that you always close the connection after using it so that the connection is returned to the pool. To Close the Connection, you can use the Close or Dispose method of the Connection object, or open all connections in the C # using statement or in the Visual Basic Using statement. Connections that are not explicitly closed may not be added or returned to the pool.

Remove 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.

Clear pool
ADO. NET 2.0 introduces two new methods for clearing the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pool of a given provider and ClearPool clears the connection pool associated with a specific connection. If the connection is in use during the call, it will be marked accordingly. When the connection is closed, it will be discarded rather than returned to the pool.

Use the connection string keyword to control the connection pool

The following table lists the valid names of connection pool values in ConnectionString. For more information, see SQL Server connection pool (ADO. NET ).

Connection Lifetime

0

When the Connection is returned to the pool, the creation time is compared with the current time. If the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection will be destroyed. This is useful in clustering configuration (used to force load balancing between running servers and servers that are just online ).

The zero (0) value will cause the maximum connection timeout for the pool connection.

Connection Reset

'True'

Determine whether to reset the database connection when extracting database connections from the pool. For SQL Server 7.0, setting it to false can avoid an additional round-trip to the Server when the connection is obtained. Note that the connection status (such as the database context) is not reset at this time ).

As long as the Connection Reset is not set to false, the Connection pool program will not be affected by the ChangeDatabase method. The connection will be reset after exiting the corresponding connection pool, and the server will be moved back to the database at login. No new connection is created or authentication is performed again. If you set Connection Reset to false, the pool may produce connections from different databases.

Enlist

'True'

When this value is true, the pool program automatically registers connections in the current transaction context of the Creation thread. The identifiable values are true, false, yes, and no.

Load Balance Timeout

0

The shortest time (in seconds) before the connection pool is destroyed ).

Max Pool Size

100

The maximum number of connections allowed in the pool.

Min Pool Size

0

The minimum number of connections allowed in the pool.

Pooling

'True'

When this value is true, the system extracts the SQLConnection object from the appropriate pool, or creates the object and adds it to the appropriate pool as needed. The identifiable values are true, false, yes, and no.

Description found on the blog of Deep Blue:

A few days ago, my colleague asked me a question: a CS architecture program directly uses SQL Server as the Server, and each client directly connects to the database for operations (kay note: this architecture is the cs project of S2 ), if the number of opened clients is too large, the number of SQL Server connections will be extremely high, and the database has a performance bottleneck. What should I do in this case? The reason for this is the internal mechanism of ADO. NET. ADO. to improve performance, the connection pool is used, so that each request does not need to create a connection, and then authenticate and execute the SQL statement. Instead, the connection is directly retrieved from the connection pool to execute the SQL statement, after the execution is complete, the connection is not actually closed, but is re-stored in the connection pool. If there are 100 clients, and each client saves 10 connections in the connection pool after a period of time, in this case, even if no operation is performed on the client, there are 1000 connections on SQL Server, so it is not surprising that performance problems do not occur.

Since it is a connection pool problem, I have come up with two solutions to this problem:

1. Close the ADO. NET connection pool. Each time you execute SQL statements, a new connection is created and executed, and then closed. This will slow down the data query (the connection is established every time and the authentication is performed every time, of course, it will be slow). However, this is slow in milliseconds and generally cannot be felt, however, if an operation involves hundreds of SQL statements, the details may be slowed down. The modification method is very simple. You do not need to modify the code. Simply add Pooling = False to the database link string.

2. Modify the architecture. In addition to performance problems, this CS architecture also has other security problems. You can change the method of directly connecting to the database to connect to the service, which can use Remoting and Web services. Of course, you can use WCF now. In this way, only the service program connects to the database, and the client only connects to the service program, so that there will be no bottlenecks caused by the connection pool. However, the amount of code modified in this way is very large, and it is still very painful to change the code.

The following is an article about the ADO. NET connection pool found on the Internet. It feels good.

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.

How to Implement connection pool

Make sure that each connection uses the same connection string (the same as the connection pool); only the connection pool with the same connection string will work. If the connection strings are different, the application will not use the connection pool but create a new connection.

Advantages

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

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 memory and other resources are your first concern, you can close all connections and create a connection pool when the next request arrives.

Connection Pool FAQ

1. When to create a connection pool?

When the first connection request arrives, a connection pool is created. The connection pool is determined by the connection character creation of the database connection. Each connection pool is related to a different connection string. When a new connection request arrives, if the connection string is the same as the string used by the connection pool, a connection is retrieved from the connection pool. If not, a new connection pool is created.

2. When will the connection pool be closed?

Close the connection pool when all connections in the connection pool are closed.

3. When all connections in the connection pool are used up and new connection requests arrive, what will happen?

When the connection pool reaches its maximum number of connections, new connection requests are placed in the connection queue. When a connection is released to the connection pool, the connection pool allocates the newly released connection to the connection requests queued in the queue. You can call close and dispose to return the connection to the connection pool.

4. How should I allow the connection pool?

For. NET applications, the connection pool is allowed by default. (This means that you don't have to do anything for it) Of course, if you can add Pooling = true to the connection string of the SQLConnection object; make sure that your application allows the use of the connection pool.

5. How should I disable the connection pool?

By default, ADO. NET allows a database connection pool. If you want to disable the connection pool, you can use the following method:

1) when using the SQLConnection object, add the following content to the connection string: Pooling = False;

2) When using the OLEDBConnection object, add the following content to the connection string: ole db Services =-4;

Through the above two articles, I hope you can understand what the database connection pool is, when it applies, and when it does not apply. I have made a small example of Performance Testing. You can see:

First run:

After multiple runs:

The code for the test button is as follows:

1: string connStringUsePool = "server = .; database = pubs; uid = sa; pwd = 123456; pooling = true; connection lifetime = 0; min pool size = 1; max pool size = 50 "; 2: string connStringUnUsePool = "server = .; database = pubs; uid = sa; pwd = 123456; pooling = false "; 3: 4: private void button#click (object sender, EventArgs e) 5: {6: 7: 8: int count = 50; 9:
10: DateTime start = DateTime. now; 11: for (int I = 0; I <count; I ++) 12: {13: using (SqlConnection conn = new SqlConnection (connStringUsePool) 14: {15: conn. open (); 16: conn. close (); 17:} 18:} 19: DateTime end = DateTime. now; 20: TimeSpan ts = end-start; 21: label1.Text = "use connection pool" + ts. milliseconds. toString (); 22: 23: start = DateTime. now; 24: for (int I = 0; I <count; I ++) 25: {26: using (SqlConnection conn = new SqlConnection (connStringUnUsePool) 27: {28: conn. open (); 29: conn. close (); 30 :}31 :}32: end = DateTime. now; 33: ts = end-start; 34: label2.Text = "no connection pool" + ts. milliseconds. toString (); 35 :}

Original address: http://developer.51cto.com/art/200906/131898.htm here to thank the author!