Analysis of SqlConnection pool mechanism

When a physical connection is established, you need to shake hands with the server, parse the connection string, authorize, and check constraints. After the 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.

 

The code is as follows:

Copy code

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 );         }

SqlConnection opens the connection pool by default. To disable it forcibly, add Pooling = False to the connection string.

The calling procedure is as follows:

The code is as follows:	Copy code
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:

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.

      

The code is as follows:

Copy code

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

 

The code is as follows:

Copy code

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

The code is as follows:

Copy code

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.