eaglet

.net 中通過 SqlConnection 連接 sql server，我們會發(fā)現(xiàn)第一次連接時總是很耗時，但后面連接就很快，這個其實和SqlConnection 的連接池機制有關，正確的理解這個連接池機制，有助于我們編寫高效的數(shù)據(jù)庫應用程序。

作者: eaglet

轉載請注明出處

.net 中通過 SqlConnection 連接 sql server，我們會發(fā)現(xiàn)第一次連接時總是很耗時，但后面連接就很快，這個其實和SqlConnection 的連接池機制有關，正確的理解這個連接池機制，有助于我們編寫高效的數(shù)據(jù)庫應用程序。

 

很多人認為 SqlConnection 的連接是不耗時的，理由是循環(huán)執(zhí)行 SqlConnection.Open 得到的平均時間幾乎為0，但每次首次open 時，耗時又往往達到幾個毫秒到幾秒不等，這又是為什么呢？

首先我們看一下 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.

以上摘自 http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.80%29.aspx

也就是說物理連接建立時，需要做和服務器握手，解析連接字符串，授權，約束的檢查等等操作，而物理連接建立后，這些操作就不會去做了。這些操作是需要一定的時間的。所以很多人喜歡用一個靜態(tài)對象存儲 SqlConnection 來始終保持物理連接，但采用靜態(tài)對象時，多線程訪問會帶來一些問題，實際上，我們完全不需要這么做，因為 SqlConnection 默認打開了連接池功能，當程序 執(zhí)行  SqlConnection.Close 后，物理連接并不會被立即釋放，所以這才出現(xiàn)當循環(huán)執(zhí)行 Open操作時，執(zhí)行時間幾乎為0.

下面我們先看一下不打開連接池時，循環(huán)執(zhí)行 SqlConnection.Open 的耗時

        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 默認是打開連接池的，如果要強制關閉，我們需要在連接字符串中加入 Pooling=False

調(diào)用程序如下：

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

下面是測試結果

 

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

從這個測試結果看，關閉連接池后，平均每次連接大概要耗時4個毫秒左右，這個就是建立物理連接的平均耗時。

 

下面再看默認情況下的測試代碼

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

調(diào)用代碼

 

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

測試結果

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

這個測試結果看，第一次耗時是119ms，這是因為我在測試代碼中，首先運行的是這個測試過程，119 ms 是程序第一次啟動時的首次連接耗時，這個耗時可能不光包括連接數(shù)據(jù)庫的時間，還有 ado.net 自己初始化的用時，所以這個用時可以不管。10秒以后在執(zhí)行這個測試過程，首次執(zhí)行的時間變成了0ms，這說明連接池機制發(fā)生了作用，SqlConnection Close 后，物理連接并沒有被關閉，所以10秒后再執(zhí)行，連接幾乎沒有用時間。

但我們發(fā)現(xiàn)一個有趣的現(xiàn)象，10分鐘后，首次連接時間變成了6ms，這個和前面不打開連接池的測試用時幾乎一樣，也就是說10分鐘后，物理連接被關閉了，又重新打開了一個物理連接。這個現(xiàn)象是因為連接池有個超時時間，默認情況下應該在5-10分鐘之間，如果在此期間沒有任何的連接操作，物理連接就會被關閉。那么我們有沒有辦法始終保持物理連接呢？方法是有的。

連接池設置中有一個最小連接池大小，默認為0，我們把它設置為大于0的值就可以保持若干物理連接始終不釋放了。看代碼

 

 

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

其實只要在連接字符串中加入一個 Min Pool Size=n 就可以了。

調(diào)用代碼

                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

我們可以看到當 Min Pool Size = 1  時，除了首次連接用時5ms以外，即便過了10分鐘，用時還是0ms，物理連接沒有被關閉。

 

多線程調(diào)用問題

多線程調(diào)用我也做了測試，這里不貼代碼了，我大概講一下結果。如果是多線程訪問 SqlConnection ，注意是通過 new SqlConnection 方式訪問，

那么這里有兩個問題，如果后一個線程在前一個線程 Close 前調(diào)用了Open操作，那么 Ado.net 不可能復用一個物理連接，它將為第二個線程分配一個新的物理連接。如果后一個線程 Open  時，前一個線程已經(jīng) Close 了，則新的線程使用前一個線程的物理連接。也就是說，如果同時有n個線程連接數(shù)據(jù)庫，最多情況下會創(chuàng)建n條物理連接，最少情況下為1條。如果創(chuàng)建n條物理連接，則用時理論上等于 n * t / cpu , n 為線程數(shù)，t 為每次創(chuàng)建物理連接的用時，前面測試的結果大概是5-10ms左右，cpu 為當前機器的CPU數(shù)量。另外網(wǎng)絡，服務器的負荷也影響這個用時。為了保證在大并發(fā)時，盡量少的創(chuàng)建新的物理連接，我們可以適當把 Min Pool Size 調(diào)大一些，但也不要太大，因為單個機器TCP鏈路的數(shù)量是有限的，詳見我另外一篇文章 Windows 下單機最大TCP連接數(shù)

連接字符串中關于 連接池方面的參數(shù)

見下面鏈接 SqlConnection.ConnectionString Property

 

IIS 回收應用程序池對連接池的影響

在做 ASP.NET 程序時，我們會發(fā)現(xiàn)，如果網(wǎng)站20分鐘不訪問，再次訪問就會比較慢，這是因為IIS默認的 idle timeout 是20分鐘，如果在20分鐘內(nèi)沒有一個訪問，IIS 將回收應用程序池，回收應用程序池的結果就相當于應用程序被重啟，所有原來的全局變量，session, 物理連接都將清空。回收應用程序池后首次訪問，相當于前面我們看到的程序啟動后第一次訪問數(shù)據(jù)庫，連接的建立時間將比較長。所以如果網(wǎng)站在某些時段訪問量很少的話，需要考慮 idle timeout 是否設置合理。