永利皇宫登录网址欢迎您!

永利皇宫登录网址 > 数据库知识 > 的连接池机制解析

的连接池机制解析

时间:2020-04-08 13:05

提供一篇关于sqlconnection连接池详细,有需要的朋友参考一下。

物理连接建立时,需要做和服务器握手,解析连接字符串,授权,约束的检查等等操作,而物理连接建立后,这些操作就不会去做了。这些操作是需要一定的时间的。所以很多人喜欢用一个静态对象存储 SqlConnection 来始终保持物理连接,但采用静态对象时,多线程访问会带来一些问题,实际上,我们完全不需要这么做,因为 SqlConnection 默认打开了连接池功能,当程序 执行 SqlConnection.Close 后,物理连接并不会被立即释放,所以这才出现当循环执行 Open操作时,执行时间几乎为0.

下面我们先看一下不打开连接池时,循环执行 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

调用程序如下:

代码如下复制代码 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 msWithout Pooling, average connection elapsed 5 msWating for 10sWithout Pooling, first connection elapsed 6 msWithout Pooling, average connection elapsed 4 msWating for 600sWithout Pooling, first connection elapsed 7 msWithout 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); }

调用代码

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 msWith Pooling, average connection elapsed 0 msWaiting for 10sWith Pooling, first connection elapsed 0 msWith Pooling, average connection elapsed 0 msWaiting for 600sWith Pooling, first connection elapsed 6 msWith Pooling, average connection elapsed 0 ms

这个测试结果看,第一次耗时是119ms,这是因为我在测试代码中,首先运行的是这个测试过程,119 ms 是程序第一次启动时的首次连接耗时,这个耗时可能不光包括连接数据库的时间,还有 ado.net 自己初始化的用时,所以这个用时可以不管。10秒以后在执行这个测试过程,首次执行的时间变成了0ms,这说明连接池机制发生了作用,SqlConnection Close 后,物理连接并没有被关闭,所以10秒后再执行,连接几乎没有用时间。

但我们发现一个有趣的现象,10分钟后,首次连接时间变成了6ms,这个和前面不打开连接池的测试用时几乎一样,也就是说10分钟后,物理连接被关闭了,又重新打开了一个物理连接。这个现象是因为连接池有个超时时间,默认情况下应该在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 就可以了。

调用代码

代码如下复制代码

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 msWith Pooling Min Pool Size=1, average connection elapsed 0 msWaiting for 10sWith Pooling Min Pool Size=1, first connection elapsed 0 msWith Pooling Min Pool Size=1, average connection elapsed 0 msWaiting for 600sWith Pooling Min Pool Size=1, first connection elapsed 0 msWith Pooling Min Pool Size=1, average connection elapsed 0 ms

我们可以看到当 Min Pool Size = 1 时,除了首次连接用时5ms以外,即便过了10分钟,用时还是0ms,物理连接没有被关闭。

上一篇:学习心得 下一篇:sql server Right Outer Join用法永利皇宫登录网址