How can I enable and disable the connection pool using the ado.net connection pool?

For Ado.net What is the role of the connection pool? I believe everyone knows about it. After the connection pool is enabled, how can we enable and disable the connection to better utilize the benefits of the connection pool? MS We recommend that you close the connection whenever possible after it is used. As a result, you must enable or disable the connection or Using (){...} WriteCodeIt is troublesome, and frequent operations on the pool also seem to cause some performance problems. Asp.net In Webform Frequently involved N Database operations, in order to facilitate direct Init Open a connection, and then Dispose Closing in, saving trouble and reducing operations on the pool to improve performance ; Can this improve performance? The answer is yes, but it may disappoint you in some situations. ... . When the number of connections in the pool can cope with user requests, it can indeed reduce the number of operations in the pool to Improve the Performance; otherwise, it is different that the user must wait until the connection is obtained before other operations can be performed, when more users are waiting to get the connection, they can only put the thread in a longer wait time, resulting in a decline in the overall service processing capability.

Perform a simple test as follows:

The test method is to useVs2008Built-in load testing. The number of tested users is200, Set the number of database connection pools20, Measurement duration10Minutes.

Test code1:

Protected VoidPage_load (ObjectSender,EventargsE)

{

Using(System. Data. sqlclient.SqlconnectionSqlconn =NewSystem. Data. sqlclient.Sqlconnection("Data Source =.; initial catalog = northwind; user id = sa; Pwd =; Max pool size = 20"))

{

Sqlconn. open ();

 System. threading.Thread. Sleep (300 );

}

}

Test code2:

Protected VoidPage_load (ObjectSender,EventargsE)

{

Using(System. Data. sqlclient.SqlconnectionSqlconn =NewSystem. Data. sqlclient.Sqlconnection("Data Source =.; initial catalog = northwind; user id = sa; Pwd =; Max pool size = 20"))

{

Sqlconn. open ();

}

System. threading.Thread. Sleep (50 );

Using(System. Data. sqlclient.SqlconnectionSqlconn =NewSystem. Data. sqlclient.Sqlconnection("Data Source =.; initial catalog = northwind; user id = sa; Pwd =; Max pool size = 20"))

{

Sqlconn. open ();

}

System. threading.Thread. Sleep (50 );

Using(System. Data. sqlclient.SqlconnectionSqlconn =NewSystem. Data. sqlclient.Sqlconnection("Data Source =.; initial catalog = northwind; user id = sa; Pwd =; Max pool size = 20"))

{

Sqlconn. open ();

}

System. threading.Thread. Sleep (50 );

Using(System. Data. sqlclient.SqlconnectionSqlconn =NewSystem. Data. sqlclient.Sqlconnection("Data Source =.; initial catalog = northwind; user id = sa; Pwd =; Max pool size = 20"))

{

Sqlconn. open ();

}

System. threading.Thread. Sleep (50 );

Using(System. Data. sqlclient.SqlconnectionSqlconn =NewSystem. Data. sqlclient.Sqlconnection("Data Source =.; initial catalog = northwind; user id = sa; Pwd =; Max pool size = 20"))

{

Sqlconn. open ();

}

System. threading.Thread. Sleep (100 );

}

Test Results1

Test Results2

From the above test results, it is clear thatMSThe proposed method is better in some cases, because as concurrent users increase, the out-of-the-box approach has a better overall performance. Another important thing is that the first code method enables the maximum number of database connections in the pool, while the second code method only uses6Database connections. This further illustrates that using an out-of-the-box disconnection can save database connection resources in some cases.

Although the results are as follows, they do not mean that they are used out-of-the-box and out-of-the-box. When the pool does not allow users to wait outside, it is unnecessary.NIt is best to use a connection when there are no other computing operations that are particularly time-consuming (the duration of this operation is weighed based on your actual situation ), you can find the answer based on the test results. However, I personally recommend usingMSI have always adopted this method.