连接池应该比保持连接打开慢吗？

As my previous question : How to stay connected to database until screen close?

正如我之前的问题：如何在屏幕关闭之前保持与数据库的连接？

First, Let me apologize all of you that I don't explain my situation.

首先，让我向所有人道歉，我不解释我的情况。

Well, my situation is update up to hundred records. I create a real work alike with For loop and log their result.

好吧，我的情况是更新到100条记录。我使用For循环创建一个真正的工作并记录他们的结果。

private void button1_Click(object sender, EventArgs e)
{
    int i;
    KeyEventArgs keyEvent = new KeyEventArgs(Keys.Enter); //Create keydown event 

    Performance perf = new Performance(); //Class for measure time and logging

    perf.Start(); //Start stopwatch

    for (i = 1; i <= 100; i++)
    {
        txtLotNo.Text = i.ToString("0000000000") + "$01";   //Generate input ID

        txtLotNo_KeyDown(sender, keyEvent); //Fire keydown event
    }

    perf.Stop();    //Stop stopwatch

    perf.Log(frmInvCtrl.appPath,"Stock In (Stay connected)- " + frmInvCtrl.instance);   //Logging
}

Here is a Performance Class.

这是一个Performance Class。

class Performance
{
    private Stopwatch _sw = new Stopwatch();    //Create stopwatch property

    public double GetWatch
    {
        get
        {
            return this._sw.ElapsedMilliseconds;
        }
    }

    public void Start()
    {
        Stop();

        _sw.Reset();
        _sw.Start();
    }

    public void Stop()
    {
        if (_sw.IsRunning)
        {
            _sw.Stop();
        }
    }

    public void Log(string path,string menu)
    {
        string logName = path + "\\Log_" + System.DateTime.Now.ToString("yyyyMMdd") + ".txt";
        string logDetail = System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + " - [" + menu + "] "
            + "Process 100 record in [" + (((double)_sw.ElapsedMilliseconds / 1000)).ToString() + "] seconds";

        using(StreamWriter writer = new StreamWriter(logName,true))
        {
            writer.WriteLine(logDetail);    //wirtelog
        }
    }
}

And these are log result

这些都是日志结果

2017/02/19 08:16:05 - [Stock In - On Cloud] Process 100 record in [68.352] seconds
2017/02/19 08:17:34 - [Stock In - On Cloud] Process 100 record in [70.184] seconds
2017/02/19 08:20:28 - [Stock In - On Cloud] Process 100 record in [56.66] seconds
2017/02/19 08:21:34 - [Stock In - On Cloud] Process 100 record in [60.605] seconds
2017/02/19 08:22:44 - [Stock In - On Cloud] Process 100 record in [68.27] seconds
2017/02/19 08:24:43 - [Stock In - Network Server] Process 100 record in [46.86] seconds
2017/02/19 08:26:05 - [Stock In - Network Server] Process 100 record in [31.746] seconds
2017/02/19 08:26:48 - [Stock In - Network Server] Process 100 record in [31.859] seconds
2017/02/19 08:27:32 - [Stock In - Network Server] Process 100 record in [31.003] seconds
2017/02/19 08:28:17 - [Stock In - Network Server] Process 100 record in [40.487] seconds
2017/02/19 08:32:42 - [Stock In (Stay connected)- On Cloud] Process 100 record in [18.196] seconds
2017/02/19 08:35:47 - [Stock In (Stay connected)- On Cloud] Process 100 record in [14.721] seconds
2017/02/19 08:36:30 - [Stock In (Stay connected)- On Cloud] Process 100 record in [15.903] seconds
2017/02/19 08:37:31 - [Stock In (Stay connected)- On Cloud] Process 100 record in [15.811] seconds
2017/02/19 08:38:15 - [Stock In (Stay connected)- On Cloud] Process 100 record in [16.4] seconds
2017/02/19 08:43:08 - [Stock In (Stay connected)- Network Server] Process 100 record in [13.09] seconds
2017/02/19 08:43:25 - [Stock In (Stay connected)- Network Server] Process 100 record in [13.03] seconds
2017/02/19 08:43:40 - [Stock In (Stay connected)- Network Server] Process 100 record in [13.051] seconds
2017/02/19 08:43:55 - [Stock In (Stay connected)- Network Server] Process 100 record in [12.992] seconds
2017/02/19 08:44:12 - [Stock In (Stay connected)- Network Server] Process 100 record in [14.953] seconds

I was practiced with connection pooling. But, these results shown stay connect to database are faster in many records situation.

我练习连接池。但是，这些结果显示在许多记录情况下保持连接到数据库的速度更快。

Are there any practice suitable for this case?

有没有适合这种情况的做法？

EDIT : 2017/02/21

编辑：2017/02/21

Here is open connection when open form code:

这是打开表单代码时的开放连接：

private void frm_Load(object sender, EventArgs e) //Open menu
{
    ... //statement

    frmMain.sqlConn1 = new SqlConnection();
    frmMain.sqlConn1.ConnectionString = frmMain.connectionString1;
    frmMain.sqlConn1.Open();

    ... //statement
}

Update code:

更新代码：

public static long ScanUpdate(string lotNo)
{
    string scanLotNo = "";
    int scanIndex = 0;

    if (!SplitBarcode(lotNo, ref scanLotNo, ref scanIndex))
    {
        //Invalid Barcode data
        return -919;
    }

    //Prepare sql command
    string updStatus = (frmMain.shelfScan) ? "05" : "10";
    string sql = <sql statement>

    try
    {
        using (SqlCommand sqlCmd = new SqlCommand(sql, frmMain.sqlConn1))   //frmMain.sqlConn1 is connection in form_Load()
        {
            if (sqlCmd.ExecuteNonQuery() <= 0)
            {
                //No row affect
                //frmMain.sndPlay.Play();
                return -99;
            }
            else
            {
                //Completed
                return 0;
            }
        }
    }
    catch
    {
        return 99;
    }
    finally
    {

    }
}

And dispose connection when exit

退出时配置连接

private void btnBack_Click(object sender, EventArgs e)
{
    frmMain.sqlConn1.Dispose();
    this.Close();
}

3 个解决方案

#1

From the standpoint of responsiveness of a single thread, keeping the connection open will be faster. The purpose of connection pooling is to reduce the expense of opening new connections by sharing them between threads, while at the same time not consuming an excessive number of connections on the shared SQL Server.

从单个线程的响应性的角度来看，保持连接打开将更快。连接池的目的是通过在线程之间共享来减少打开新连接的开销，同时不会在共享SQL Server上消耗过多的连接。

Every time a connection is released to the connection pool and then reused, the protocol stack will make a call to sp_resetconnection to clean up the state on the server. You can see this by running a profiler trace against the SQL Server.

每次将连接释放到连接池然后重新使用时，协议栈将调用sp_resetconnection来清除服务器上的状态。您可以通过针对SQL Server运行探查器跟踪来查看此信息。

Since every process has its own connection pool for each connection string, you will only benefit from connection pooling if there is contention for the connections within a process.

由于每个进程都为每个连接字符串都有自己的连接池，因此只有在进程内存在争用连接时才会从连接池中受益。

#2

The part everyone is missing here is that its Windows CE, its not uncommon Opening Connections on compact edition can be really slow.

每个人都缺少的部分是它的Windows CE，它在紧凑版上的并不罕见的开放连接可能非常慢。

That said however, those times do look a little exaggerated. See this QA for workarounds: How can I make my SQL Server CE connection open faster?

然而，那些时候看起来有点夸张。请参阅此QA以获取解决方法：如何更快地打开SQL Server CE连接？

#3

There are a number of areas where you can run into problems with the code above.

在许多方面，您可能会遇到上述代码的问题。

• You are creating a single SqlConnection instance.
• 您正在创建单个SqlConnection实例。
• All your event handlers call a static function which references the one SqlConnection instance.
• 所有事件处理程序都调用一个引用一个SqlConnection实例的静态函数。
• Your event handlers don't check if they are running on the main UI thread.
• 您的事件处理程序不会检查它们是否在主UI线程上运行。

The SqlConnection class will automatically pool connections under the covers. When you call the close() function, the connection just goes back into the pool. You can control the pooling behavior with properties in the connection string. By keeping the same connection object for all your functions, you are forcing them to be serialized.

SqlConnection类将自动池连接。当您调用close（）函数时，连接只会返回池中。您可以使用连接字符串中的属性控制池行为。通过为所有函数保留相同的连接对象，您将强制它们被序列化。

Here is a reference to some connection string properties. Take a look at Connection Lifetime.

以下是对某些连接字符串属性的引用。看看Connection Lifetime。

I would remove the SqlConnection instance from the form_load() function and write ScanUpdate as follows:

我将从form_load（）函数中删除SqlConnection实例并按如下方式编写ScanUpdate：

public static long ScanUpdate(string lotNo)
{
    string scanLotNo = "";
    int scanIndex = 0;

    if (!SplitBarcode(lotNo, ref scanLotNo, ref scanIndex))
    {
        //Invalid Barcode data
        return -919;
    }

    //Prepare sql command
    string updStatus = (frmMain.shelfScan) ? "05" : "10";
    string sql = <sql statement>

    try
    {
        using (SqlConnection conn = new SqlConection(frmMain.connectionString1)) {
            SqlCommand sqlCmd = new SqlCommand(sql, conn);
            if (sqlCmd.ExecuteNonQuery() <= 0)
            {
                //No row affect
                //frmMain.sndPlay.Play();
                return -99;
            }
            else
            {
                //Completed
                return 0;
            }
            conn.Close();
        }            
    }
    catch
    {
        return 99;
    }
}

For your *_Click event handler functions, make sure to check to check if the event needs to be re-invoked:

对于* _Click事件处理函数，请务必检查以检查是否需要重新调用该事件：

private void button1_Click(object sender, EventArgs e)
{
   if (this.InvokeRequired)
   {
      this.Invoke(new EventArgsDelegate(button1_Click), new object[] { sender, ea });
   }

   // Do some stuff
}

See this answer for more detail on event handling.

有关事件处理的更多详细信息，请参阅此答案