Is it a bad idea to keep a Database connection open

Richard Berry100

I am using a windows forms application to write data to a SQL 2008 database over a wireless network. (Scanning serial numbers) While stepping through the code in debug mode, the Connection.Open seems to take relatively long. Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed? Will the connection stay open, or will SQL server drop it if it is unused for some time?

Lost User

Richard.Berry100 wrote:

Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?

Yes. Opening/closing a connection takes very little time. There's a connection-pool that manages the connections. Another advantage of closing your connection is that you'll be able to handle more client-connections. If your server can handle 25 clients simultaneous, how much would it be able to handle if they only connect when required?

Richard.Berry100 wrote:

Will the connection stay open, or will SQL server drop it if it is unused for some time?

That'd be easy to test, wouldn't it? Write a console-app and tell us if it indeed stays open as it should; there's only a timeout on creating a connection and on executing statements, not on the lifetime of the connection. That doesn't mean that the server will guarantee an open connection. A single Windows-update could force a reboot and close the connection. Or the cleaning-lady, when she unplugs the server and plugs in her vacuum-cleaner :) --edit Here's the link I was looking for; SQL Server Connection Pooling (ADO.NET)[^]

To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling. [...] It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

PIEBALDconsult

Yes. I instantiate and dispose the connection only once, but I open and close it for each operation.

GuyThiebaut

Define your connection at the start, then for each operation open then close the connection once the operation is complete.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

jschell

Richard.Berry100 wrote:

Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?

Normal MS stuff uses a connection pool. The first time you create the connection it is opened. After that it gets it from the pool. It is already open and it stays open. It never closes while the application runs. That is the default behavior of the connection pool. Your suggestion is doing nothing more than priming the pump to make it appear later that your application is more responsive. So 1. There is nothing wrong with doing that. 2. It is unlikely to have any impact on the database server nor the architecture 3. It is a perfectly reasonable thing to do.

Richard.Berry100 wrote:

or will SQL server drop it if it is unused for some time?

No SQL Server will not close idle connections. That itself is actually a problem because it means that if a client connection crashes (client fails or network fails) that the connection just remains there forever. One either must set up a process to clean them or at least look for them every once in a while.

PIEBALDconsult

It is still preferable to use Open to get the connection from the pool and Close to return it.

Richard Berry100

Hi Guys - thanks to all for the responses. I think the link Eddy posted puts a lot into perspective, in that closing the connection does not really kill it, it leaves it in the pool, and unless there is something in the connection that changes, like accessing a different database, or using a different User ID the same connection from that pool is used again. I tried inserting 10000 records (on server on local machine) in three different ways. The difference was only about 100ms

private void cmdInsert_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
sw.Start();
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr))
{

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
            
	for (int i = 0; i < 10000; i++)
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }

        sw.Stop();
        this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                  
    }

//7958 miliseconds

private void cmdInsert_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
sw.Start();
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr))
{

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
            cn.Open();
	for (int i = 0; i < 10000; i++)
            {
                
                cmd.ExecuteNonQuery();
                
            }
            cn.Close();
        }

        sw.Stop();
        this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                  
    }

//6894 miliseconds

private void cmdInsert_Click(object sender, EventArgs e)
{

Richard Berry100

Thanks Eddy, good link!

jschell

PIEBALDconsult wrote:

It is still preferable to use Open to get the connection from the pool and Close to return it.

I would say it is the only way.

Richard Berry100

Yip, agreed, see the reply I posted to my own question with speed tests on various approaches, and reference to Eddy's link - Thanks!