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.
