ado.net creating a single connection versus relying on connection pooling, my two cents.
Creating a connection object at the start of the process and passing it versus relying on connection pooling and the “using” construct?
This seems to be a hot topic in the adonet newsgroup, I would like to put in my two cents. This post deals with the managed pooler in the SqlClient and the Oracle Managed provider.
For intranet winform applications that are not multi-threaded there really aren’t many differences. You will get practically the same performance. Creating a single _unpooled_ connection has some advantages since you can control its lifetime. Pooled connections that are idle will be cleaned up after eight minutes and this can cause unnecessary load on the server by either keeping connections around longer than they should be in applications where you know you are not going to be using the connection any more, or by opening too many connections in applications that use the connection and then idle for more than eight minutes.
If you are dealing with a multi threaded application for me there is no question. I would always rely on connection pooling .The chance of shooting yourself in the foot with code that may use the connection in two separate threads at the same time is too great, most of the time this will work fine so you will only catch these bugs in production. They will be hardware dependent bugs of the “everything works on my dev machine but…” variety. I would not risk it. (I will blog more on this at a later time).
If you are dealing with asp.net every fraction of a second that you hold a connection open while not using it is stealing resources from your other threads. You need to rely on connection pooling and you need to open the connection as late as possible and close it as soon as you can.
Some people are concerned about the cost of Opening and Closing the connection repeatedly, to see how this may affect your application try something like the code below:
using System;
using System.Data;
using System.Data.SqlClient;
public class Example
{
public static void Main()
{
SqlConnection sqlconnection1 = new SqlConnection(ConnectionString);
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
sqlcommand1.CommandText = "create table foo123 (myid int)";
try {sqlcommand1.ExecuteNonQuery();}catch(Exception){}
sqlconnection1.Close();
DateTime start = DateTime.Now;
for (int i = 0; i < 1000; i++)
{
Execute1();
}
DateTime stop = DateTime.Now;
Console.WriteLine("Opening new connection every time spent: ="+((TimeSpan) stop.Subtract(start)).Ticks.ToString("N") + "Ticks.");
Console.WriteLine("="+((TimeSpan) stop.Subtract(start)) +" seconds");
SqlConnection sqlconnection2 = new SqlConnection(ConnectionString);
sqlconnection2.Open();
start = DateTime.Now;
for (int i = 0; i < 1000; i++){
Execute2(sqlconnection2);
}
stop = DateTime.Now;
Console.WriteLine("Opening a single connection: ="+((TimeSpan) stop.Subtract(start)).Ticks.ToString("N") + "Ticks.");
Console.WriteLine("="+((TimeSpan) stop.Subtract(start)) +" seconds");
}
static void Execute1()
{
using (SqlConnection sqlconnection1 = new SqlConnection(ConnectionString ))
{
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
sqlcommand1.CommandText = "insert into foo123 values (1)";
Int32 int321 = sqlcommand1.ExecuteNonQuery(); // 1
}
}
static void Execute2(SqlConnection sqlconnection1)
{
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
sqlcommand1.CommandText = "insert into foo123 values (1)";
Int32 int321 = sqlcommand1.ExecuteNonQuery(); // 1
}
}
In this code I am comparing the cost of inserting 1000 rows using a single connection to the cost of relying on the pool and the using statement to insert the same 1000 rows.
In my computer and running against a non local database these where the results:
(these results mean absolutely nothing since they are completely dependent on network/computer/random factors, I am only posting to give a rough idea to those that won’t test this on their own network)
time in seconds. Results of three test runs.
Open and close 1000 times. Vs Single Connection,
00:00:04.1718750 vs 00:00:04.1093750
00:00:04.1406250 vs 00:00:04.1875000
00:00:04.2031250 vs 00:00:04.1718750
Notes about the code: I am making sure that a connection is open and available in the pool before running Execute1, only fair since I am opening a connection before calling Execute2.
Q: So are you saying that opening 1000 connections is almost free?
A: We are not really opening 1000 connections in Execute1, we are relying on the managed pooler.
Q: How does the managed pooler work?
A: It is surprisingly hard to explain, let me give you some pseudo code for this code:
On Connection.Open we check the pool, currently it has 0 connections so we do a roundtrip to the server (expensive!) and retrieve a connection.
You use the connection and call Close
The connection is a precious resource, we can’t just throw it away! When you call close on the connection we save the connection into a managed pool, this is very fast.
Foreach 1000 Calls to Execute1
On Connection Open we check the pool, it currently has one available connection and there is no need to do a server roundtrip.
Retrieving the connection from the pool is very fast.
Execute insert statement
On Connection Dispose we place the connection back in the managed pool (Connection dispose is guaranteed to happen when we exit the "using" statement.
Rambling out, This post is provided “AS IS” and confers no rights. I am not trying to imply that the test results posted are official in any way, please try it out for yourself.