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

Those numbers are far too close to be a definitive benchmark! — Cory Nelson, May 12 '11 at 16:39
+1 That delta is pretty small. Try running it a couple of thousand times and see what you get. Also, reorder the two tests as some startup time is no doubt affecting your results. — 3Dave, May 12 '11 at 16:41
Not only are the timings almost equal, but you are doing them directly after each other, making it likely that the second round will be quicker, by virtue of the code already being loaded and potential cacheing. Connection pooling is the key, here. — Pontus Gagge, May 12 '11 at 16:42
@n8wrl, @user420667: `DbCommand` also implements `IDisposable` (and so does `SqlCommand`). — vgru, May 12 '11 at 16:44
Hm... I will try reordering and more its. But I think I should change my command to a faster command, since the majority of the time being spent is in the execution of the command. — user420667, May 12 '11 at 16:52

score 34 · Accepted Answer · answered May 12 '11 at 16:39

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.

score 12 · Answer 2 · answered May 12 '11 at 16:39

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.

vgru · Answer 3 · 2011-05-12T16:55:38.883

8

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?

edited May 12 '11 at 16:55

answered May 12 '11 at 16:50

vgru

49,838
16
120
201

This is only for inserts, updates, and deletes. Essentially, only for writing to the database... not for querying it. Is that correct? – user420667 May 12 '11 at 22:06
@user420667: That's right, batching and bulk copying speed up write operations. Sorry, there was no query text in your initial question, I presumed you were doing inserts (partly because I had to optimize large insert operations recently, so I did all sorts of performance tests). – vgru May 13 '11 at 07:51
Heh. Fair enough given the somewhat intentional vagueness of my sqlcommand. Thanks. – user420667 May 13 '11 at 15:04

score 5 · Answer 4 · answered May 12 '11 at 16:40

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.

score 3 · Answer 5 · answered May 12 '11 at 16:41

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.

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

5 Answers5

Linked

Related