Ivan Mitev In The Software Trenches

Technology weblog on .NET development and other things that make the world go round

January 28, 2005

Where is the OLE DB connection pooling?

Well, I am facing a serious problem. I have changed the behaviour of data access layer to open a new connection whenever there is a need to talk to the DB. Before the change there was one static global connection that remained opened in the lifetime of the application. And now I experience significant performance casualties.

I have read in numerous resources that I can count on the automatic connection pooling of my OLE DB .NET Framework Data Provider. I am using Microsoft.Jet.OLEDB.4.0 and a MS Access. So let's quote the documentation:

Pooling connections can significantly enhance the performance and scalability of your application. The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling.

All right, so my first guess is that the connection pooling doesn't work for some reason and after the last active connection is released the pool (if there is a pool created at all) is purged. I know that I can force to release the pool by calling OleDbConnection.Release, but I want the opposite effect. To test my hypothesis I made a simple class and executed its methods TestWithActiveConnection() and TestMultipleConnection(), using the TestDriven.Net VS.NET AddIn. So let me show you the code and then the results.



/// <summary>
/// Test the performance of the Ole DB connection pooling for Microsoft.Jet.OLEDB
/// with and without an active connection in the lifetime
/// </summary>
public class ConnectionPoolTest
{
public OleDbConnection activeConnection = null;
public string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb";
public int IterationCount = 5;

public void TestWithActiveConnection()
{
DateTime start = DateTime.Now;

//Open a connection with the same connection string, during the test lifetime
activeConnection = new OleDbConnection(ConnectionString);
activeConnection.Open();

DateTime end = DateTime.Now;
TimeSpan span = end - start;
Trace.WriteLine( "The connection that is to be active during the test was opened for: " + span.TotalSeconds.ToString() );

TestMultipleConnection();

activeConnection.Close();
}

public void TestMultipleConnection()
{
//the first call to TestConnection()takes more time
//because the JIT compiler has to prepare the function
//so it will be excluded in the total time statistics
TestConnection();

DateTime start = DateTime.Now;
for(int i = 0; i < IterationCount; i ++)
{
TestConnection();
}

TimeSpan span = DateTime.Now - start;
Trace.WriteLine( IterationCount.ToString() + " connections opened and then closed for a total of: " + span.TotalSeconds.ToString() );
}

public void TestConnection()
{
DateTime start = DateTime.Now;

OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();
conn.Close();

TimeSpan span = DateTime.Now - start;
Trace.WriteLine( "Single connection opening and closing took: " + span.TotalSeconds.ToString() );
}
}



I ran the test multiple times in a different order. But the results are following the same pattern and it can be illustrated by this sample output:

1) Test execution output using an active connection

Active during tests connection was opened for: 0.1201704
Single connection opening and closing took: 0.0600852
Single connection opening and closing took: 0.0300426
Single connection opening and closing took: 0.0200284
Single connection opening and closing took: 0.0300426
Single connection opening and closing took: 0.0300426
Single connection opening and closing took: 0.0200284
5 connections opened and closed for a total of: 0.1802556

2) Test execution output without active connection

Single connection opening and closing took: 0.1802556
Single connection opening and closing took: 0.0700994
Single connection opening and closing took: 0.0600852
Single connection opening and closing took: 0.0600852
Single connection opening and closing took: 0.0600852
Single connection opening and closing took: 0.0600852
5 connections opened and closed for a total of: 0.350497

So what does this mean? It is apparent that that the presence of an actively opened connection made the test with multiple connection closing and opening finish a lot faster (2-3 times). The only possible explanation for me is that the connection pool is released each time there are no active connections. I have to make further investigations and read something like Pooling in the Microsoft Data Access Components. Or maybe hold a single opened connection just for the sake of keeping the pool alive. Pfu, this would be ugly, but still it is a good enough workaround! If anyone has a better idea, please share it.

3 Comments:

Hi Ivan,

I found your above post very helpful. Thanks a lot for sharing the information!

Do you happen to know if the connection pool is used by more than one thread, or is it created per each thread? My guess is that the pool is shared between threads since a pool is created per connection string and each thread creating a connection with that connection string will probably get that connection from the pool.

What are your thoughts of this?

thanks,
Yosi

By Anonymous Anonymous, at 29 November, 2006 10:06  

"Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."

Excerpt from: http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx

By Blogger Ivan Mitev, at 29 November, 2006 10:35  

Hi Ivan,

The link you provided clearly says 'The ADO Connection object implicitly uses IDataInitialize. However, this means your application needs to keep at least one instance of a Connection object instantiated for each unique user—at all times. Otherwise, the pool will be destroyed when the last Connection object for that string is closed.'.

[is696]

By Anonymous Anonymous, at 19 January, 2010 03:04  

Post a Comment

<< Home