Determine how the WebBrowser browser handles page load completion

Many people think that the connection of SqlConnection is not time-consuming, because the average time for loop execution of ES2en.Open is almost zero, but the time for the first open is often several milliseconds to several seconds. Why?

First let's look at 1 what does the authoritative documentation on MSDN say

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.

From above http: / / msdn. microsoft. com/en - us/library / 8 xx3tyca % 28 VS. The 80% 29 aspx

This means that when a physical connection is established, you need to do things like shake hands with the server, parse connection strings, authorize, check constraints, and so on, which will not be done once the physical connection is established. These operations take a fixed amount of time. So many people like to use a static object storage SqlConnection to keep physical connection, but the static object, multithreaded access will lead to more problems, in fact, we don't need to do so, because SqlConnection open the function of the connection pool, by default. When the program execution SqlConnection Close, physical connection will not be freed immediately, so it didn't appear when the loop execution Open operation, the execution time is almost zero.

Let's first look at the time it takes to loop SqlConnection.Open without opening the connection pool

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

By default, SqlConnection turns the connection pool on. To force it off, we need to add Pooling=False to the connection string

The calling procedure is 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 are the results

Without Pooling, first connection elapsed 13 ms
Without Pooling, average connection elapsed 5 ms
Wating for 10s
Without Pooling, first connection elapsed 6 ms
Without Pooling, average connection elapsed 4 ms
Wating for 600s
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, each connection takes about 4 milliseconds on average, which is the average time it takes to establish a physical connection.

Now let's look at the test code by default

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

The calling code

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

The test results

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

The test results to see, first time is 119 ms, this is because I in your test code, first run the test process, 119 ms was the first connection time-consuming procedure first startup, the time-consuming may not only include the time to connect to the database, and ado. net own initialization, so this time can no matter. After 10 seconds during the test, the initial execution time becomes 0ms, which indicates that the connection pooling mechanism has been activated. After SqlConnection Close, the physical connection is not closed, so it is almost useless to execute the connection after 10 seconds.

However, we find an interesting phenomenon that after 10 minutes, the first connection time becomes 6ms, which is almost the same as the previous test with no connection pool open. In other words, after 10 minutes, the physical connection is closed and one physical connection is reopened. This is because the connection pool has a timeout period, which by default should be between 5 and 10 minutes, during which time the physical connection will be closed. So is there any way we can keep the physical connection all the time? There are ways.

There is a minimum connection pool size in the connection pool setting, which defaults to 0, and we set it to a value greater than 0 to keep several physical connections from being freed. Look at the 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); 
 }

Just add 1 Min Pool Size=n to the connection string.

The calling code

                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, first connection elapsed 5 ms
With Pooling Min Pool Size=1, average connection elapsed 0 ms
Waiting for 10s
With Pooling Min Pool Size=1, first connection elapsed 0 ms
With Pooling Min Pool Size=1, average connection elapsed 0 ms
Waiting for 600s
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 5ms, even after 10 minutes, the time is still 0ms, and the physical connection is not closed.

Multithreaded call problem
I've done tests on multithreaded calls, so I'm not going to post code here, but I'm going to talk about the results. If it is multithreaded access to SqlConnection, note that it is via new SqlConnection,

So there are two problems here. If the latter thread calls the Open operation before the first thread Close, then Ado.net cannot reuse a physical connection, and it will allocate a new physical connection to the second thread. If the last thread Open is already Close, the new thread USES the physical connection of the first thread. That is, if there are n threads simultaneously connecting to the database, at most n physical connections will be created, and at least one. If n physical connections are created, the time is theoretically equal to n * t/cpu, with n being the number of threads and t being the number of times each physical connection is created. The results of the previous tests are around 5-10ES168en, and cpu is the number of CPU on the current machine. In addition, network, server load also affects this time. In order to ensure that as few new physical connections are created as possible in large concurrency, we can appropriately increase THE number of Min Pool Size by 1, but not too much, because the number of TCP links on a single machine is limited. See my other article for details

Parameters in a connection string about connection pooling

See link SqlConnection.ConnectionString Property below

The impact of the IIS recycle application pool on the connection pool
Doing ASP. NET program, we will find that if you don't visit websites for 20 minutes, access will be slow again, this is because the default idle IIS timeout is 20 minutes, if there is no one access in 20 minutes, IIS will recycle the application pool, the result of the recycling application pool is equivalent to the application is restarted, all the original global variables, session, physical connection will be empty. The first access after recycling the application pool is equivalent to the first access to the database after the application is started, and the connection takes longer to establish. So if the site has very little traffic during certain periods, consider whether idle timeout is properly set.