Don't Use CommandBehavior.CloseConnection when Using a using Statement

^{Current .Net Version: 2.0.50727}

Mike's C#, VS.NET and SQL Blog

Don't Use CommandBehavior.CloseConnection when Using a using Statement
Author:	Michael G. Last Updated:3/17/2006 1:31:47 PM
Category(ies):	SQL Server , ADO.NET , .Net Framework
Description:	Your application's performance will suffer if you automatically close a connection via the CommandBehavior.CloseConnection enumerated value and a using statement in the same scope.
	Add & View Comments About this Article

Tested with Microsoft Visual Studio Version 8.0.50727.42; Microsoft .NET Framework Version 2.0.50727

It's customary for me to use using statements with disposable objects such as connection and file system objects. Doing so ensures that the objects will close and dispose of any valuable resources. I noticed that when you use a using statement on a connection object which is used by a command object to return a reader and the ExecuteReader method is passed the CommandBehavior.CloseConnection enumerated value, the result is a drop in performance by about 84%. This is, by using a CommandBehavior.CloseConnection strategy to close a connection whenever a reader closes along with a using statement to do the same, you are causing the CLR to have to clean up the same resources two different times. This is what causes performance to suffer. Using this method results in your application requiring approximately 84% more time to cleanup than if you just used one method or the other.

Using the CommandBehavior.CloseConnection and using Statement Strategy: DataTable tbl = new DataTable(); DateTime dt = DateTime.Now; using (SqlConnection sqlConn = new SqlConnection("Data Source=GSNSQL001; Initial Catalog=TLSOracle;Integrated Security=SSPI;")) { using (SqlCommand sqlCmd = new SqlCommand("SELECT * FROM TBLAUTHOR", sqlConn)) { sqlConn.Open(); using (SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)) { tbl.Load(reader); } } } Average total milliseconds to complete: 166ms

Using the using Statement ONLY Strategy:

DataTable tbl = new DataTable(); DateTime dt = DateTime.Now; using (SqlConnection sqlConn = new SqlConnection("Data Source=GSNSQL001; Initial Catalog=TLSOracle;Integrated Security=SSPI;")) { using (SqlCommand sqlCmd = new SqlCommand("SELECT * FROM TBLAUTHOR", sqlConn)) { sqlConn.Open(); using (SqlDataReader reader = sqlCmd.ExecuteReader()) { tbl.Load(reader); } } } Average total milliseconds to complete: 90ms

Comments

Click Here to Add your Comment

By jinnyb @ 3/22/2006 3:16:15 AM

Hey thats great article ,but I would like to know how did u calculated the amount of time required for both the code snippets to execute.

By BruceBlodgett @ 3/28/2006 10:22:38 AM

What would the performance and behavior implications be of specifying CommandBehavior.CloseConnection but leaving out the 3rd using clause?

By Michael G. @ 3/28/2006 2:04:33 PM

None because the using statement for the SqlDataReader instance doesn't close the underlying connection - it only 1) closes the reader and 2) disposes of its resources.

Even if you omit the using statement for the SqlDataReader instance, you still have redundancy because what's occurring is an attempt to close the connection twice: once by the SqlDataReader via the CommandBehavior enmurated value and another by the using statement for the SqlConnection instance.

Therefore, the following code will still cause a performance hit:

using (SqlConnection sqlConn = new SqlConnection("Data Source=GSNSQL001; Initial Catalog=TLSOracle;Integrated Security=SSPI;"))
{
    using (SqlCommand sqlCmd = new SqlCommand("SELECT * FROM TBLAUTHOR", sqlConn))
    {
        sqlConn.Open();
        SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection); // closes the connection here
        tbl.Load(reader);
        reader.Close();
    }
} // closes the connection here too!! Oops, already closed! Double work!