SQL connection performance question

Question

Using C#, visual studio 2008, SQL Server 2000 (and 2008)

I use a DataLayer class for my SQL call in my Web Application:

In the DataLayer are all the SQL calls for the application and all SQL activity happens in that class.

For example:

protected void UpdatePeople(int iPeopleID, string Lastname......)
{
    InitCmd("sUpdatePeople");
    c_objSQLCmd.Parameters.Add(new SqlParameter("@PeopleID", iPeopleID));
    c_objSQLCmd.Parameters.Add(new SqlParameter("@LastName", szLastName));
    CmdExecuteNonQuery();
}

Then, I have helper objects that do the SQL work:

    private void InitCmdSP(string szStoredProc)
    {
        c_objSQLCmd.Parameters.Clear();
        c_objSQLCmd.CommandType = CommandType.StoredProcedure;
        c_objSQLCmd.CommandText = szStoredProc;
    }
    private int CmdExecuteNonQuery()
    {
        int iReturn = 0;
        if (c_objSQLConn.State != ConnectionState.Open)
            c_objSQLConn.Open();
        iReturn = c_objSQLCmd.ExecuteNonQuery();
        c_objSQLConn.Close();
        return iReturn;
    }

I also have helper obects that return DataTable or DataSet and that ExecuteScalar and return int, string, etc. as appropriate.

My question is this, what performance hit am I taking by opening/closing the SQL connection each time?

So far, the appplications have generally less than 600-700 users and not many of them simultaneously, so the perceived performance to the user is probably not a reall issue. I'm just trying to consider best practices.

Thanks, John

On a different note, I'd roll out all the connection logic by using an 'Using statement' - http://www.w3enterprises.com/articles/using.aspx to ensure that the connections are disposed if any exception occurs — WorldIsRound, Mar 26 '11 at 14:17

score 5 · Accepted Answer · edited May 23 '17 at 09:58

5

Not much. Your connections are pooled by default to avoid the real open/close overhead.

You can test for pooling by running SQL Profiler: you'll see sp_reset_connection calls

edited May 23 '17 at 09:58

Community

1
1

answered Mar 26 '11 at 14:26

gbn

422,506
82
585
676

sheikhjabootie · Answer 2 · 2011-03-26T15:21:23.010

2

Closing the connection each time will probably have practically no impact on performance.

The Sql Server ADO.NET provider will automatically use a connection pool for your application (unless you tell it explicitly not to).

But the best practice for using anything that implements IDisposable (including SqlConnection and SqlCommand) is to wrap the item in a using block like so:

using(var connection = new SqlConnection(...))
{
    using(var command = connection.CreateCommand())
    {
        // Do something with the command.
    }
}

This ensures that any unmanaged resources are released at the earliest opportunity and, importantly is resistant to exceptional code paths. The code above is syntactic sugar for:

SqlConnection connection;

try
{
    connection = new SqlConnection(...);

    SqlCommand command;

    try
    {
        command = connection.CreateCommand();

        // Do something with the command.
    }
    finally
    {
        if(command != null)
        {
            command.Dispose();
        }
    }
}
finally
{
    if(connection != null)
    {
        connection.Dispose();
    }
}

So basically, using is awesome.

edited Mar 26 '11 at 15:21

answered Mar 26 '11 at 14:30

sheikhjabootie

7,308
2
35
41

@Xcaliburp thanks for the using suggestion; however, I'm not sure how I would implement that using the helper objects I use. How would I relate the existing command object (c_objSQLCmd) to the "command = connection.CreateCommand()" in your tip? Thanks, John. – John Mar 26 '11 at 15:48
Upon further reflection, I assume I can use an existing object within the using statement. I.E. using (c_objSQLcmd), correct? – John Mar 26 '11 at 16:12
@John - no you can't reuse an object within a using statement. The using statement will call dispose and that can only happen once in the lifetime of an object. I'm not sure what benefits you are getting from having a command object shared across multiple methods. I would create and dispose a new one each time it was needed. Or I'd pass it in as a parameter (dependency injection) - in which case the using would be higher in the call stack. – sheikhjabootie Mar 26 '11 at 23:59
@John - you could keep your existing pattern, but your class should implement idisposable and call dispose on the command there. Then you should use the using statement when calling your class. – sheikhjabootie Mar 27 '11 at 00:10
I do that for a couple reasons, one of which is to simplify the code readability and another is to kep all SQL logic in one location. It doesn't seem to make sense to me to put what is essentially the exact same code in every method that makes a call to execute an SP. So, within each method: I call the method that initializes the command object; append the SqlParameters to the command object; and, call the method to Execute the command object and return whatever type (int, string, datatable, dataset) is required. – John Mar 27 '11 at 00:18
how about using (SqlCommand o = c_objSQLCmd) where c_objsqlcmd is the exisitng object? I don't care if the object is disposed each time, I reinitialize it everytime I use it anyway. – John Mar 27 '11 at 00:40
@John - there is no way to undispose a disposed object. Implementors of IDisposable are expected throw an ObjectDisposedException if an object is used after it is disposed. The reason for implementing the interface is to allow the clean up of unmanaged resources. So if you use the object after disposal, the underlying resource has been released. You have to construct a new object if you want to resurrect it. You could instead do using(var command = CreateCommand("myproc")) where CreateCommand returns a new instance of SqlCommand configured as you need it. – sheikhjabootie Mar 27 '11 at 03:46
@John - I should add that it may be that the SqlCommand and SqlConnection implementations of the ADO.NET interfaces actually do nothing in their dispose methods. As I said in my answer, the connections are kept open and pooled behind the scenes by default. But best practice says you should always dispose of an object that implements IDisposable at the earliest opportunity. Hope that helps. – sheikhjabootie Mar 27 '11 at 03:50

WorldIsRound · Answer 3 · 2011-03-26T14:34:55.680

You can consider with connection pooling by setting the minimum PoolSize based on the number of requests that hit your web application. Additionally you can consider wrapping all the connection logic in a using statement to ensure that all connections are cleaned up. Such as:

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();      
    }

Here is an article to get you started on connection pooling. Since you have an web app, you can also consider the Unit of work pattern

SQL connection performance question

3 Answers3