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