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.
posted by Ivan Mitev @ 17:53
3 comments
