Should connection pooling be this much slower than keeping a connection open?

Question

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.

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.

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

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

Answer 1

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

• You are creating a single SqlConnection instance.
• All your event handlers call a static function which references the one SqlConnection instance.
• Your event handlers don't check if they are running on the main UI thread.

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.

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

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

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:

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.

Answer 2

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.

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.

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.

Answer 3

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

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?