Is it better to execute many sql commands with one connection, or reconnect every time?

Question

Here's my test code, which seems to suggest that it's better to connect multiple times instead of connecting just once.

Am I doing something wrong?

int numIts = 100; Stopwatch sw = new Stopwatch(); sw.Start(); using (SqlConnection connection = new SqlConnection(connectionParameters)) {                connection.Open();     for(int i = 0; i < numIts; i++)     {         SqlCommand command = new SqlCommand(sqlCommandName, connection);                 command.CommandType = CommandType.StoredProcedure;                 command.Parameters.AddWithValue(par1Name, par1Val);                 command.Parameters.AddWithValue(par2Name, par2Val);         using(SqlDataReader reader = command.ExecuteReader())         {         }     } } sw.Stop(); TimeSpan durationOfOneConnectionManyCommands = sw.Elapsed; Console.WriteLine(durationOfOneConnectionManyCommands);  sw.Reset();  sw.Start(); for(int i = 0; i < numIts; i++) {     using (SqlConnection connection = new SqlConnection(connectionParameters))     {                    connection.Open();         SqlCommand command = new SqlCommand(sqlCommandName, connection);                 command.CommandType = CommandType.StoredProcedure;                 command.Parameters.AddWithValue(par1Name, par1Val);                 command.Parameters.AddWithValue(par2Name, par2Val);         using(SqlDataReader reader = command.ExecuteReader())         {         }     }                                } sw.Stop(); TimeSpan durationOfManyConnections = sw.Elapsed; Console.WriteLine(durationOfManyConnections); 

Output:

//output: //00:00:24.3898218   // only one connection established //00:00:23.4585797   // many connections established. // //output after varying parameters (expected much shorter): //00:00:03.8995448 //00:00:03.4539567 

Update:

OK, so those who said it would be faster w/ one connection have it. (although the difference is marginal, if any.) Here's the revised code and output:

public void TimingTest() {     numIts = 1000;     commandTxt = "select " + colNames + " from " + tableName;      OneConnection();     ManyConnections();     OneConnection(); } private void ManyConnections() {     Stopwatch sw = new Stopwatch();     sw.Start();     for (int i = 0; i < numIts; i++)     {         using (SqlConnection connection = new SqlConnection(connectionParameters))         {             connection.Open();             using (SqlCommand command = connection.CreateCommand())             {                 command.CommandText = commandTxt;                  using (SqlDataReader reader = command.ExecuteReader())                 {                 }             }         }     }     sw.Stop();     TimeSpan durationOfManyConnections = sw.Elapsed;     Console.WriteLine("many connections: " + durationOfManyConnections); } private void OneConnection() {     Stopwatch sw = new Stopwatch();     sw.Start();     using (SqlConnection connection = new SqlConnection(connectionParameters))     {         connection.Open();         for (int i = 0; i < numIts; i++)         {             using (SqlCommand command = connection.CreateCommand())             {                 command.CommandText = commandTxt;                 using (SqlDataReader reader = command.ExecuteReader())                 {                 }             }         }     }     sw.Stop();     TimeSpan durationOfOneConnectionManyCommands = sw.Elapsed;     Console.WriteLine("one connection: " + durationOfOneConnectionManyCommands); } 

Output:

one connection: 00:00:08.0410024 many connections: 00:00:08.7278090 one connection: 00:00:08.6368853  one connection: 00:00:10.7965324 many connections: 00:00:10.8674326 one connection: 00:00:08.6346272 

Update:

the difference is more striking if I use SQLConnection.ClearAllPools() after each function:

Output:

one connection: 00:00:09.8544728 many connections: 00:00:11.4967753 one connection: 00:00:09.7775865 

Answer 1

By default, SqlConnection will use connection pooling. Therefore your code does most likely not actually open many connections in either case.

You can control if SqlConnection will use pooling by enabling or disabling the pool in the connectionstring, depending on what DB your connection string is for, the syntax will vary.

See here for some info if you use MSSQLServer. Try setting Pooling=false in the connection string and see if it makes a difference.

Answer 2

Definitively, it's better to have one connection. Maybe you are running your benchmark with small amount of data. Try increasing the number to 1,000 or 10,000.

Another point is that, depending on your app configuration, you might think you are running with multiple connections but .NET is pooling connections for you, so you are basically running with the same connections.