ADO.Net: reuse open SqlConnection or get a new one every time?

Question

Before posting this question, I have read several answers on this site: like this and this. Everyone seem to agree that "c# application pools are optimized for multiple calls to the same database."

However, I still observe a substantial performance penalty if I get a connection from the pool. Here are my benchmarks:

    private const string localConnString = "Data Source=(local);Integrated Security=SSPI;database=MyTest;Pooling=true";


    [Test]
    public void GetNewConnectionEveryTime()
    {
        var stopwatch = new Stopwatch();
        stopwatch.Start();
        for (var i = 0; i < 1000; i++)
        {
            using (var conn = new SqlConnection(localConnString))
            {
                conn.Open();
                const string saveTrancount = "EXEC dbo.TestTran";
                ExecuteSql(conn, saveTrancount);
            }
        }
        stopwatch.Stop();
        Console.WriteLine("Time elapsed: {0}", stopwatch.Elapsed);
        //Time elapsed: 00:00:00.5576016
    }

    [Test]
    public void ReuseOneConnection()
    {
        var stopwatch = new Stopwatch();
        stopwatch.Start();
        using (var conn = new SqlConnection(localConnString))
        {
            conn.Open();
            for (var i = 0; i < 1000; i++)
            {
                const string saveTrancount = "EXEC dbo.TestTran";
                ExecuteSql(conn, saveTrancount);
            }
        }
        stopwatch.Stop();
        Console.WriteLine("Time elapsed: {0}", stopwatch.Elapsed);
        //Time elapsed: 00:00:00.1110324
    }

    private void ExecuteSql(SqlConnection conn, string sql, int timeout = 0)
    {
        var command = conn.CreateCommand();
        command.CommandText = sql;
        command.CommandType = CommandType.Text;
        command.CommandTimeout = timeout;
        command.ExecuteNonQuery();
    }

Apparently getting a connection from the pool takes four times more time (00:00:00.5576016 - 00:00:00.1110324 appr 0.44) than doing actual work (00:00:00.1110324 in second benchmark). What am I missing?

Edit: I am running these benchmarks against a database created on RAM Disk, so all my database operations are very fast.

Answer 1

You benchmarks make sense to me. It would be even worse if you opened and closed the connection for each iteration without connection pooling, but that's a whole different story. However, when you close a pooled connection, you're instead returning it to the pool: under the covers, the SNAC issues sp_reset_connection and puts the connection back into the pool. Usually, it's a very fast operation (unless blocking kicks in), but it has to take some small but non-zero time.
Re-using the same connection without closing and opening for each iteration, avoids this small lag.
However, if you compared the same use of pooled and non-pooled connections it would have made more sense to me. Have you tried that?

Here's an interesting post on sp_reset_connection