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.
Comments
- Anonymous
October 09, 2004
Taking this to the nth degree (100,000 connections opened), you end with with a savings of 61% by using an open connection.
Before the Connection passing developers start to cheer, however, the savings, per connection instantiation is:
0.0000532952 seconds per connection
If you pass the connection object, and open and close on each use, the savings reduces to 12% or only 0.0000154375 per instantiation.
NOTE: The tests here are actually weighted for the Connection object. Delving deeper into the mechanics would yield which slice of time was actually instantiation only, but this test is more real world, as you see total impact.
To put this in real time. Over 100,000 connections, with an insert, you can save about 40 seconds worth of time if you simply open a connection for your application and continue to use it. In trade, you end up with numerous potential error conditions, along the lack of ability to use the using() keyword in C# (as it will Dispose(), thus killing your connection).
Thanks for the sample, Angel. - Anonymous
October 09, 2004
Done with 1000 Iterations:
Opening new connection every time spent: =8,434,152.00Ticks.
=00:00:00.8434152 seconds
Opening a single connection: =3,904,700.00Ticks.
=00:00:00.3904700 seconds
Done with 100000 Iterations:
Opening new connection every time spent: =545,485,320.00Ticks.
=00:00:54.5485320 seconds
Opening a single connection: =358,033,320.00Ticks.
=00:00:35.8033320 seconds
Gregory, I see your point and tend to agree - one the other hand the longer stuff takes to process the more likelihood that something outside of the program could interfere and cause problems. However that's just a subjective judgement on my part - no hard numbers there.