Detailed description of the SqlConnection connection pool mechanism in. net

Correct understanding of this connection pool mechanism helps us to write efficient database applications.

Many people think that the SqlConnection connection is not time-consuming, because SqlConnection is executed cyclically. the average Open time is almost 0, but the time consumed for the first open usually ranges from several milliseconds to several seconds. Why?

First, let's take a look at what is said in the authoritative document 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 be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

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

That is to say, when a physical connection is established, operations such as shaking hands with the server, parsing the connection string, authorization, and constraints are required. After a physical connection is established, these operations will not be performed. These operations take some time. Therefore, many people prefer to use a static object storage SqlConnection to maintain physical connections. However, when using static objects, multi-threaded access may cause some problems. In fact, we do not need to do this at all, because SqlConnection enables the connection pool function by default, when the program executes SqlConnection. after Close, the physical connection will not be released immediately. Therefore, when the Open operation is executed cyclically, the execution time is almost 0.

Next, let's take a look at the time consumed for loop execution of SqlConnection. Open when the connection pool is not opened.

Copy codeThe Code is 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, first connection elapsed {0} ms", sw. ElapsedMilliseconds );

Sw. Reset ();
Sw. Start ();
For (int I = 0; I <100; I ++)
{
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
}
}
Sw. Stop ();
Console. WriteLine ("Without Pooling, average connection elapsed {0} ms", sw. ElapsedMilliseconds/100 );
}

Copy codeThe Code is as follows:. csharpcode {BACKGROUND-COLOR: # ffffff; FONT-FAMILY: Las LAS, "Courier New", courier, monospace; COLOR: black; FONT-SIZE: small}
. Csharpcode PRE {BACKGROUND-COLOR: # ffffff; FONT-FAMILY: Las LAS, "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: # partition C0}
. 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 opens the connection pool by default. To disable it forcibly, add Pooling = False to the connection string.

The Calling procedure is as follows:

Copy codeThe Code is as follows: Test. SqlConnectionTest. OpenWithoutPooling ();
Console. WriteLine ("Waiting for 10 s ");
System. Threading. Thread. Sleep (10*1000 );
Test. SqlConnectionTest. OpenWithoutPooling ();
Console. WriteLine ("Waiting for 600 s ");
System. Threading. Thread. Sleep (600*1000 );
Test. SqlConnectionTest. OpenWithoutPooling ();

The test results are as follows:

Copy codeThe Code is as follows: Without Pooling, first connection elapsed 13 MS
Without Pooling, average connection elapsed 5 MS
Wating for 10 s
Without Pooling, first connection elapsed 6 MS
Without Pooling, average connection elapsed 4 MS
Wating for 600 s
Without Pooling, first connection elapsed 7 MS
Without Pooling, average connection elapsed 4 MS

According to the test results, after the connection pool is closed, the average time consumed for each connection is about 4 milliseconds, which is the average time consumed for establishing a physical connection.

Next, let's look at the default test code.

Copy codeThe Code is 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, first connection elapsed {0} ms", sw. ElapsedMilliseconds );
Sw. Reset ();
Sw. Start ();
For (int I = 0; I <100; I ++)
{
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
}
}
Sw. Stop ();
Console. WriteLine ("With Pooling, average connection elapsed {0} ms", sw. ElapsedMilliseconds/100 );
}

Call Code

Copy codeThe Code is as follows: Test. SqlConnectionTest. OpenWithPooling ();
Console. WriteLine ("Waiting for 10 s ");
System. Threading. Thread. Sleep (10*1000 );
Test. SqlConnectionTest. OpenWithPooling ();
Console. WriteLine ("Waiting for 600 s ");
System. Threading. Thread. Sleep (600*1000 );
Test. SqlConnectionTest. OpenWithPooling ();

Test Results

With Pooling, first connection elapsed 119 MS
With Pooling, average connection elapsed 0 MS
Waiting for 10 s
With Pooling, first connection elapsed 0 MS
With Pooling, average connection elapsed 0 MS
Waiting for 600 s
MS With Pooling, first connection elapsed 6
With Pooling, average connection elapsed 0 MS

The test results show that the first time consumed is 119 ms, because in the test code, the first time I run this test process, the 119 ms is the first connection time when the program was started for the first time, this time may not only include the time used to connect to the database, but also the time used to initialize ado.net itself, so this time can be ignored. After 10 seconds, during the test, the first execution time is changed to 0 ms, which indicates that the connection pool mechanism works. After SqlConnection is closed, the physical connection is not closed, therefore, the connection will be executed 10 seconds later, and the connection will be useless.

However, we found an interesting phenomenon. After 10 minutes, the first connection time changed to 6 ms, which is almost the same as the test that didn't open the connection pool. That is to say, 10 minutes later, the physical connection is closed, and a physical connection is re-opened. This is because the connection pool has a timeout time. By default, it should be between 5-10 minutes. If no connection operation is performed during this period, the physical connection will be closed. Is there a way to keep the physical connection at all times? There are methods.

There is a minimum connection pool size in the connection pool setting. The default value is 0. We can set it to a value greater than 0 to keep several physical connections from being released. View code

Copy codeThe Code is 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}, first connection elapsed {1} ms", minPoolSize, sw. ElapsedMilliseconds );
Sw. Reset ();
Sw. Start ();
For (int I = 0; I <100; 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, you only need to add a Min Pool Size = n to the connection string.

Call Code

Copy codeThe Code is as follows: Test. SqlConnectionTest. OpenWithPooling (1 );
Console. WriteLine ("Waiting for 10 s ");
System. Threading. Thread. Sleep (10*1000 );
Test. SqlConnectionTest. OpenWithPooling (1 );
Console. WriteLine ("Waiting for 600 s ");
System. Threading. Thread. Sleep (600*1000 );
Test. SqlConnectionTest. OpenWithPooling (1 );

With Pooling Min Pool Size = 1, first connection elapsed 5 MS
With Pooling Min Pool Size = 1, average connection elapsed 0 MS
Waiting for 10 s
With Pooling Min Pool Size = 1, first connection elapsed 0 MS
With Pooling Min Pool Size = 1, average connection elapsed 0 MS
Waiting for 600 s
With Pooling Min Pool Size = 1, first connection elapsed 0 MS
With Pooling Min Pool Size = 1, average connection elapsed 0 MS

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

Multithreading
I also tested multiple-threaded calls. I will not post code here. I will give a rough look at the results. For multi-threaded access to SqlConnection, note that it is accessed through the new SqlConnection method,

There are two problems here. If the last thread calls the Open operation before the previous thread closes, it is impossible for Ado.net to reuse a physical connection, it allocates a new physical connection to the second thread. If the previous thread is closed when the next thread is Open, the new thread uses the physical connection of the previous thread. That is to say, if there are n threads connected to the database at the same time, at most n physical connections will be created, and at least one will be created. If n physical connections are created, the theoretical value is n * t/cpu, n is the number of threads, and t is the number of physical connections created each time. The test result is about 5-10 ms, the cpu is the number of CPUs of the current machine. In addition, the server load also affects the network usage. To avoid creating as few physical connections as possible during high concurrency, we can increase the Min Pool Size appropriately, but not too large, this is because the number of TCP links for a single machine is limited. For more information, see my article "Maximum TCP connections for a single machine in Windows ".

Connection Pool parameters in the connection string

See the following link for SqlConnection. ConnectionString Property

Effect of IIS recycle application pool on Connection Pool
ASP. NET program, we will find that if the website is not accessed for 20 minutes, it will be slow to access again, because the default IIS idle timeout is 20 minutes, if no access is available within 20 minutes, IIS recycles the application pool. The result of revoking the application pool is equivalent to restarting the application. All the original global variables, sessions, all physical connections are cleared. The first access to the application pool after the application pool is reclaimed is equivalent to the first access to the database after the program we saw previously started. The connection will take a long time. Therefore, if the website has a small amount of traffic in some time periods, consider whether the idle timeout settings are reasonable.