The SqlConnection connection pooling mechanism in. NET _ Practical Skills

A proper understanding of this connection pooling mechanism helps us to write efficient database applications.

Many people think that the SqlConnection connection is not time-consuming, the reason is that the average time of the loop execution Sqlconnection.open is almost 0, but each time the first Open, time-consuming and often reach a few milliseconds to a few seconds, this is why?

First, let's take a look at the authoritative documentation on MSDN.

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must is established, the initial handshake with the server must occur, The connection string information must be parsed, the connection must is authenticated by the server, checks must is run For enlisting in the current transaction.

Above excerpted from http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.80%29.aspx

In other words, when the physical connection is established, it needs to be done with the server handshake, parsing the connection string, authorization, constraint checks, and so on, and the physical connection is established, these operations will not be done. These operations take a certain amount of time.  So a lot of people like to use a static object storage SqlConnection to always keep the physical connection, but when the static object, multi-threaded access will bring some problems, in fact, we do not need to do so, because the SqlConnection by default on the connection pooling function, when the program executes After Sqlconnection.close, the physical connection is not immediately released, so this occurs when the loop performs an open operation, the execution time is almost 0.

Let's take a look at the time consuming to cycle through Sqlconnection.open without opening the connection pool

Copy Code code as follows:

public static void Openwithoutpooling ()
{
String connectionString = "Data source=192.168.10.2;" Initial catalog=news; Integrated security=true; Pooling=false; ";
stopwatch SW = new Stopwatch ();
Sw. Start ();
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
}
Sw. Stop ();
Console.WriteLine ("Without pooling, connection elapsed {0} ms", SW. Elapsedmilliseconds);

Sw. Reset ();
Sw. Start ();
for (int i = 0; i < i++)
{
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
}
}
Sw. Stop ();
Console.WriteLine ("Without pooling, average connection elapsed {0} ms", SW. ELAPSEDMILLISECONDS/100);
}

Copy Code code as follows:

. csharpcode {background-color: #ffffff; Font-family:consolas, "Courier New", Courier, monospace; Color:black; Font-size:small}
. Csharpcode PRE {background-color: #ffffff; Font-family:consolas, "Courier New", Courier, monospace; Color:black; Font-size:small}
. Csharpcode PRE {margin:0em}
. Csharpcode. rem {COLOR: #008000}
. Csharpcode. kwrd {COLOR: #0000ff}
. csharpcode. str {COLOR: #006080}
. Csharpcode. op {COLOR: #0000c0}
. csharpcode.preproc {COLOR: #cc6633}
. csharpcode. asp {background-color: #ffff00}
. csharpcode. html {COLOR: #800000}
. Csharpcode. attr {COLOR: #ff0000}
. csharpcode. alt {background-color: #f4f4f4; Margin:0em; width:100%}
. Csharpcode. lnum {COLOR: #606060}

SqlConnection the default is to open the connection pool, if you want to force shutdown, we need to add Pooling=false in the connection string

The calling procedure is as follows:

Copy Code code as follows:

Test.SqlConnectionTest.OpenWithoutPooling ();
Console.WriteLine ("Waiting for 10s");
System.Threading.Thread.Sleep (10 * 1000);
Test.SqlConnectionTest.OpenWithoutPooling ();
Console.WriteLine ("Waiting for 600s");
System.Threading.Thread.Sleep (600 * 1000);
Test.SqlConnectionTest.OpenWithoutPooling ();

Here's the test results.

Copy Code code as follows:

Without pooling, connection elapsed MS
Without pooling, average connection elapsed 5 ms
Wating for 10s
Without pooling, connection elapsed 6 ms
Without pooling, average connection elapsed 4 ms
Wating for 600s
Without pooling, connection elapsed 7 ms
Without pooling, average connection elapsed 4 ms

From this test, the average time to establish a physical connection is approximately 4 milliseconds per connection after closing the connection pool.

Next look at the default test code

Copy Code code as follows:

public static void Openwithpooling ()
{
String connectionString = "Data source=192.168.10.2;" Initial catalog=news; integrated security=true; ";
stopwatch SW = new Stopwatch ();
Sw. Start ();
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
}
Sw. Stop ();
Console.WriteLine ("With pooling, connection elapsed {0} ms", SW. Elapsedmilliseconds);
Sw. Reset ();
Sw. Start ();
for (int i = 0; i < i++)
{
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
}
}
Sw. Stop ();
Console.WriteLine ("With pooling, average connection elapsed {0} ms", SW. ELAPSEDMILLISECONDS/100);
}

Calling code

Copy Code code as follows:

Test.SqlConnectionTest.OpenWithPooling ();
Console.WriteLine ("Waiting for 10s");
System.Threading.Thread.Sleep (10 * 1000);
Test.SqlConnectionTest.OpenWithPooling ();
Console.WriteLine ("Waiting for 600s");
System.Threading.Thread.Sleep (600 * 1000);
Test.SqlConnectionTest.OpenWithPooling ();

Test results

With pooling, connection elapsed 119 ms
With pooling, average connection elapsed 0 ms
Waiting for 10s
With pooling, connection elapsed 0 ms
With pooling, average connection elapsed 0 ms
Waiting for 600s
With pooling, connection elapsed 6 ms
With pooling, average connection elapsed 0 ms

The test results showed that the first time it took was 119ms, because I ran the test process first in the test code, 119 MS is the first time the program was first started, which may take time, not just when the database was connected, but also when it was initialized by Ado.net. So this can be used regardless. 10 seconds later in the execution of this test process, the first execution time has changed to 0ms, which indicates that the connection pooling mechanism has taken effect, SqlConnection close, the physical connection has not been closed, so 10 seconds later to execute, the connection is almost no time.

But we found an interesting phenomenon, 10 minutes later, the first connection time to become 6ms, this and the previous test does not open the connection pool almost the same, that is 10 minutes later, the physical connection is closed and a physical connection has been reopened. This behavior occurs because the connection pool has a timeout, which should be in 5-10 minutes by default, and if there is no connection operation during this time, the physical connection is closed. So is there any way we can always keep the physical connection? Method is available.

The connection pool setting has a minimum connection pool size, which defaults to 0, and we set it to a value greater than 0 to keep a few physical connections from being released at all times. Look at the code

Copy Code code as follows:

public static void openwithpooling (int minpoolsize)
{
String connectionString = String. Format ("Data source=192.168.10.2; Initial catalog=news; Integrated security=true; Min Pool size={0} ", minpoolsize);
stopwatch SW = new Stopwatch ();
Sw. Start ();
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
}
Sw. Stop ();
Console.WriteLine ("With Pooling Min Pool size={0}, connection elapsed {1} ms", Minpoolsize, SW. Elapsedmilliseconds);
Sw. Reset ();
Sw. Start ();
for (int i = 0; i < i++)
{
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
}
}
Sw. Stop ();
Console.WriteLine ("With Pooling Min Pool size={0}, average connection elapsed {1} ms", Minpoolsize, SW. ELAPSEDMILLISECONDS/100);
}

In fact, just add a Min Pool size=n to the connection string.

Calling code

Copy Code code as follows:

Test.SqlConnectionTest.OpenWithPooling (1);
Console.WriteLine ("Waiting for 10s");
System.Threading.Thread.Sleep (10 * 1000);
Test.SqlConnectionTest.OpenWithPooling (1);
Console.WriteLine ("Waiting for 600s");
System.Threading.Thread.Sleep (600 * 1000);
Test.SqlConnectionTest.OpenWithPooling (1);

With pooling Min Pool size=1, connection elapsed 5 ms
With pooling Min Pool size=1, average connection elapsed 0 ms
Waiting for 10s
With pooling Min Pool size=1, connection elapsed 0 ms
With pooling Min Pool size=1, average connection elapsed 0 ms
Waiting for 600s
With pooling Min Pool size=1, connection elapsed 0 ms
With pooling Min Pool size=1, average connection elapsed 0 ms

We can see that when Min Pool Size = 1 o'clock, the physical connection is not closed except for the first connection time 5ms, even after 10 minutes, or 0ms.

Multithreading call problem
Multithreading call I also did the test, there is no code here, I would like to talk about the results. If you are multi-threaded access to SqlConnection, note that it is accessed through the new SqlConnection method,

So here are two questions, if the last thread calls the open operation before the last thread close, then ado.net cannot reuse a physical connection, which assigns a new physical connection to the second thread. If the last thread is close when the next thread is Open, the new thread uses the physical connection of the previous thread. That is, if you have n threads connecting to the database at the same time, you will most likely create n physical connections, at least 1. If you create n physical connections, the time is theoretically equal to n * t/cpu, N is the number of threads, and T is for each physical connection to be created, the result of the previous test is about 5-10ms, the CPU number of the current machine. In addition network, the load of the server also affects this when. In order to ensure that in the large concurrency, as little as possible to create a new physical connection, we can appropriately put the Min Pool size, but not too large, because the number of individual machine TCP link is limited, see my other article the maximum number of TCP connections under Windows

Parameters for connection pooling in connection strings

See below link SqlConnection.ConnectionString property

The impact of the IIS recycle application pool on the connection pool
When doing the ASP.net program, we will find that if the site is not accessed for 20 minutes, it is slower to access it because the default idle timeout for IIS is 20 minutes, and if there is no access within 20 minutes, IIS will recycle the application pool. The result of the Recycle application pool is that the application is restarted, all the original global variables, session, and physical connections are emptied. The first access after the application pool is reclaimed, which is equivalent to the first time the program we've seen to access the database, the connection will be established for a long time. Therefore, if the site in a certain period of access to a small amount of time, you need to consider whether the idle timeout set reasonable.