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

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?

  • SQL Server CSV per row
  • SQL Server - Function call in WHERE condition
  • Restore Failed using C# Code
  • SQL Server search for “ὡ” (U+1F61) matches all?
  • Must declare the scalar variable “@query” dynamic tsql to stored procedure
  • SQL Server Stored Procedure that returns updated records
  • 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
    

  • Synchronous controller with Task.Factory.StartNew versus Asynchronous controller in MVC 4 versus Service Broker Activation
  • Programmatically getting SQL Cluster Virtual Name
  • Simulating MySql's password() encryption using .NET or MS SQL
  • What's a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR?
  • How do I save a stream to a file in C#?
  • Object reference not set to an instance of object at run time C# SQL Server?
  • 5 Solutions collect form web for “Is it better to execute many sql commands with one connection, or reconnect every time?”

    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.

    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.

    Since .NET reuses connections (“connection pooling”), there is not much overhead in creating a new instance of DbConnection several times in a row. ADO.NET will just reuse the connection under the hood. That’s why it’s good you are disposing the SqlConnection object each time, telling .NET that it can return it to the pool.

    You can, however, increase performance of multiple inserts by using ADO.NET batching. In that case you can easily have several thousands of inserts per second. If performance is critical, you can even consider using SQLBulkCopy.

    Also, your first pair of results is quite strange: 30s for 100 inserts?

    In general, .NET’s connection pooling should make it ‘not matter’ as it does a great job of recycling connections for you. But my practice is to use a single connection for a bunch of transactions I know will be taking place together. I think your timings are an indication of the connection pool doing its job and just plain variations in runs.

    SqlClient will pool your connections. In your first case with one open, it will do the job of opening the connection. Every other run will use the pooled connection. If you reverse your order and do “many connections” first, I would expect you to see the opposite result.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.