Ado. net connection pool and connection string Analysis)

From: http://blog.csdn.net/wenyuan_w/archive/2007/09/02/1769432.aspx
With the release of. net. The database reading technology also evolved from ADO to ADO. net. As you know, ADO. NET provides more convenient database read/write capabilities and excellent performance than ADO.

Connection Pool is an important function of ADO. Net to improve performance. However, there are few documents concerning the connection pool mechanism. Therefore, troubleshooting of the connection pool is always a tough problem. For oledb and ODBC, the connection pool is determined by the driver. For Oracle databases, we recommend that you use ODP. Net provided by Oracle for Versions later than 8.0. This article mainly discusses the connection pool of system. Data and sqlclient. - Connection Pool What is it?Every time the program needs to read and write the database. Connection. open () will connect to the database using connectionstring, the database will establish a connection for the program and stay open, then the program can use T-SQL statements to query/update the database. When connection. Close () is executed, the database closes the current connection. Good. Everything looks so methodical. However, if my program needs to open and close the connection from time to time, such as ASP. net or Web Service). For example, when an HTTP request is sent to the server, we need to open the connection and use select * from table to return a datatable/dataset to the client/browser, then close the current connection. So frequent open/close connection operations will undoubtedly become a waste for the entire system. Ado. Net team provides a better solution. Save the previous connection. The previous connection is handed over to the next connection when you need to open the connection. This is the connection pool.

-Connection PoolHow does it work?First, when a program executes connection. when open () is enabled, ado.net needs to determine whether the connection pool is supported (pooling is set to true by default). If it is set to false, ado.net creates a connection with the database (to avoid confusion, all connections in the database use the "connection" Description) and then returns it to the program. If it is set to true, ado.net will create a connection pool based on connectstring, and then fill in the connection pool with the connection (all connections in. net programs use the "connection" description ). The number of connections filled is determined by the min pool size (0 by default) attribute. For example, if it is set to 5, ado.net opens five connections with the SQL database at a time, then stores four connections in the connection pool, and one connection is returned to the program. When the program runs connection. Close. If pooling is true, ado.net places the current connection in the connection pool and maintains the connection with the database. At the same time, the connection lifetime (default value: 0) attribute will be determined. 0 represents an infinite number. If the connection exists for a longer time than the connection lifetime, ado.net will close the connection and disconnect the database, instead of saving it to the connection pool again. (This setting is mainly used in the Cluster's SQL database to achieve load balancing ). If pooling is set to false, the connection to the database is directly disconnected. Then, when connection. open () is executed, ADO. NET will judge whether the new connectionstring is consistent with the connectionstring of the connection stored in the connection pool. (ADO. net converts connectionstring to a binary stream. Therefore, the new connectionstring must be exactly the same as the connectionstring of the connection stored in the connection pool, even if a space is added, or the order of some properties in the connection string will make ADO. net considers this as a new connection and creates a new connection. Therefore, if you use the userid and password authentication method, modifying the password will also lead to a connection. If you use SQL Integration authentication, you need to save the two connections using the same one ). Then, ado.net needs to determine whether there is a usable connection in the current connection pool (not occupied by other programs). If not, ado.net needs to judge the Max pool size set by connectionstring (100 by default). If all connections in the connection pool do not reach the Max pool size, ado.net will connect to the database again and create a connection, then return the connection to the program. If maxpoolsize is reached, ado.net will not create any new connection again, but will wait for the connection occupied by other programs in the connection pool to be released. The waiting time is affected by sqlconnection. connectiontimeout (the default value is 15 seconds). That is to say, if the time exceeds 15 seconds, sqlconnection will throw a timeout error (sometimes if sqlconnection is used. the open () method throws a timeout error. One possible cause is that the previous connnection is not closed in time, and the number of connection pools reaches maxpoolsize .) If there is an available connection, the connection retrieved from the connection pool is not directly returned to the program. ado.net also needs to check the connectionreset attribute of connectionstring (true by default) whether to reset the connection at most. This is because the connection returned from the program may have been modified, for example, sqlconnection. changedatabase method modifies the current connection. The returned connection may not be connected to the initial Catalog Database specified by the current connection string. Therefore, you need to reset the current connection once. However, all additional checks increase the overhead of the ado.net connection pool on the system.-Connection PoolHow to Set it?The only way to modify the connection pool is to set the connection string. Pooling (true) when true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. this attribute indicates whether to use the connection pool. The default value is true. If it is set to false, the connection pool is not used. Connection lifetime (0)
When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by connection lifetime. this is useful in clustered deployments to force load balancing between a running server and a server just brought online. A value of zero (0) will cause pooled connections to have the max Imum time-out. This attribute indicates the validity period of a connection. If a connection returns to the connectionpool, the connection will not be placed in the connection again if it exceeds the connection life time. When the next request is sent, ADO. Net creates a new connection. This attribute is mainly used in the SQL database of the cluster for load balancing. Enlist (true) when true, the pooler automatically enlists the connection in the current transaction context of the Creation thread if a transaction context exists. max pool size (100) the maximum number of connections allowed in the pool. min pool size (0) The minimum number of connections maintained in the pool. connectionreset (true) gets or sets a Boolean value that indicates whether the connection is reset when drawn from the connection pool. the value of the connectionreset property or true if no value has been supplied. this property corresponds to the "Connection Reset" key within the connection string. when the connection is retrieved from the connection pool, we can use the sqlconnectionstringbuilder class to modify sqlconnectionstring in the ado.net 2.0 world to ensure that the new connection will not be mentioned because of the previous one. 

- Connection. Dispose () vs connection. Close ()You may often see many documents on the network and the msdn site. We recommend that you use using (sqlconnection Cn = new sqlconnection () {} to create a connection, because when {} is exceeded ,. net framwork will automatically execute connection. the dispose () method ensures that the connection is closed in time. 1) so timely call. is dispose () so important? If an object exceeds the living space. net will be automatically cleared by GC (Garbage Collector? This problem is actually caused by GC, which is used in. net. He does not work as hard as we do. GC will clean unused objects only when the external environment is extremely bad (when there is not enough content to be allocated ). Therefore, connection may not be killed by GC even if it exceeds the lifecycle of the variable. No connection is returned to the connection pool. Therefore, this causes the next connection to have a connection pool with no available connection and re-open a new connection, thus wasting unnecessary performance. Therefore, the ado.net team repeatedly stressed the need to close the current connection in a timely manner. One of the best methods is that the using {} block system will automatically call the connection. dispose method when exiting {}, and dispose will automatically execute the close method to release the current connection. 2) What did dispose do?

Protected override void dispose (bool disposing)
...{
If (disposing)
...{
This. _ userconnectionoptions = NULL;
This. _ poolgroup = NULL;
This. Close ();
}
This. disposeme (disposing );
Base. Dispose (disposing );
}

 

In fact, the connection. dispose method calls a close method, so the two are equivalent. That is to say, if you promptly execute the connection. Close () method, there is no need to wrap the connection in a using. 3) if using is required, if the program structure causes me to fail to use using () {} to wrap my connection, for example, my connection is returned by the same help class, what should I do? This is a common problem. In such an environment, we cannot wrap the entire connection in a connection. There are two solutions to this problem. One is to modify your code structure. Input a connectionstring to return the connection. Another method is to check your code repeatedly and check whether the connection is closed in time. Because the effect of close is the same as that of dispose. However, if you do not use using () {}, the task of closing the connection in time will be handed over to us rather than being checked by. NET Framework. - So much, when do we need to use the connection pool? What about it?Generally, this is determined by your project requirements. If your project is asp.net/webservice, we recommend that you use connection pool because this function can help you reduce the huge system overhead caused by frequent connection creation. If your system is a C/S model structure, we do not recommend that you use the connection pool. This is generally because in a model such as C/S, every user uses his/her own username and password to connect to the background database. Different connection strings are used, and the database connection is usually not frequently opened or closed, in fact, in the C/S model, you can keep a connection closed without closing, which improves your system performance, the additional check of the connection pool will not cause the consumption of system resources, and you do not have to worry about the persistent connection occupying the connection for a long time, as a result, other connections cannot be obtained from the connection pool in time. (Because you do not need to use the connection pool at all ). Hope this helps.

Another point is:
Connection lifetime
0
When a connection returns a pool, its time is released if it exists more than the connection lifetime compared to the creation time. This is useful for balancing the servers that are added to the cluster. A value of 0 can guarantee the maximum connection time limit.

Connection Reset
'True'
Determines whether the database connection is reset when the database is moved from the pool.

Enlist
'True'
If it is true, pooler automatically lists the operation context of the currently created thread, if the operation context exists.

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'
If the value is true, the connection is retrieved from the corresponding pool. If you need to create or add the connection to the corresponding pool.