SqlConnection和泳池，是保持一个打开的连接KUNG-FOO或FOO吧？ [英] SqlConnection and the Pool, is keeping an open connection kung-foo or foo-bar?

问题描述

我原以为我很聪明。但是，鉴于最近的发现，我不那么肯定了。在页面生命周期，有可能被任意数量的数据库交互。一些背靠背，其他小号$ P $垫出来。所以我发明了一个对象，它保持在HttpContext.Items字典中的SQL连接活动的一个实​​例。每个数据库请求，那么使用此连接，并在HTTP请求已经结束，我妥善处置的连接。我们正在寻找在几百毫秒的连接将开放，并与一些沉重的HTTP缓存，耗尽可用连接是不是一个问题。

关键是prevent额外的往返，由于新连接的建立。但是，当我偶然发现了连接池的知识，我认为这pretty的preserving关闭SqlConnection的用处无效。还是没有呢？

时的场景A​​中的相同场景B，性能明智？其中建议？有没有方案B提供任何性能提升，甚至可能是阻碍，因为一些地方的连接可能不妥善处理边界情况呢？原谅伪岬的例子，我不想扰乱他们BARF。

A

 使用（VAR连接=新的SqlConnection（的connectionString））
{
   使用（VAR命令=新的SqlCommand（...，连接））
   {
      ...做数据库的东西...
   }
}

...遍历栈...

使用（VAR连接=新的SqlConnection（的connectionString））
{
   使用（VAR命令=新的SqlCommand（...，连接））
   {
      ...做数据库的东西...
   }
}
 

B

  VAR connectionKeeper =新ConnectionKeeper（）;

   //添加上下文的项目，因此它可以在任何地方使用
   Context.Items.Add（连接，connectionKeeper）;

   ...遍历栈...

   使用（VAR命令=新的SqlCommand（...，connectionKeeper.Connection））
   {
      ...做数据库的东西
   }

   ...遍历栈...

   使用（VAR命令=新的SqlCommand（...，connectionKeeper.Connection））
   {
      ...做数据库的东西
   }

   ...遍历栈...

   //请求的结束
   sqlKeeper.Dispose（）;
 

解决方案

使用您的code从部分答：请让连接池做的工作。避免将静态的SqlConnection 各地不惜一切代价。连接池是专为这一点。

下面是供您参考的MSDN文章。

SQL Server的连接池（ADO.NET）

I had thought I was clever. But in light of recent discoveries, I'm not so sure any more. During the page life cycle, there could by any number of database interactions. Some back to back, others spread out. So I invented an object that keeps an instance of an SQL connection alive in the HttpContext.Items dictionary. Every db request then uses this connection and when the http request has ended, I properly dispose of the connection. We're looking at a couple hundred milliseconds the connection would be open, and with some heavy http caching, running out of available connections isn't a concern.

The point was to prevent additional round trips due to the establishing of new connections. But when I stumbled upon the knowledge of connection pooling, I think it pretty invalidates the usefulness of preserving the SqlConnection. Or does it?

Is scenario A the same as Scenario B, performance wise? Which would you recommend? Does scenario B provide no performance gains, and even possibly hinders it because of some edge cases where the connection might not disposed of properly? Forgive the pseudo-ness in the examples, I don't want to clutter them with barf.

A

using (var connection = new SqlConnection(connectionString))
{
   using (var command = new SqlCommand("...", connection))
   {
      ... doing database stuff ...
   }
}

... traversing the stack ...

using (var connection = new SqlConnection(connectionString))
{
   using (var command = new SqlCommand("...", connection))
   {
      ... doing database stuff ...
   }
}

B

   var connectionKeeper = new ConnectionKeeper();

   // Add to the context items so it can be used anywhere
   Context.Items.Add("Connection", connectionKeeper);

   ... traversing the stack ...

   using (var command = new SqlCommand("...", connectionKeeper.Connection))
   {
      ... doing database stuff
   }

   ... traversing the stack ...

   using (var command = new SqlCommand("...", connectionKeeper.Connection))
   {
      ... doing database stuff
   }

   ... traversing the stack ...

   // The end of the request
   sqlKeeper.Dispose();

解决方案

Use your code from section A. Please let the connection pool do it's job. Avoid keeping a static SqlConnection around at all costs. The connection pool was designed for this.

Here's an MSDN article for your reference.

SQL Server Connection Pooling (ADO.NET)

这篇关于SqlConnection和泳池，是保持一个打开的连接KUNG-FOO或FOO吧？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！