SQL Server 2005 Analysis Services’s ADOMD.NET Connection Pooling, or Lack Thereof

Swimming Pool Fun, CC-BY-NC-ND Dianne / dclimb510 First of all, if you’ve come here looking for how to activate connection pooling when using SSAS 2005 via ADOMD.NET, you’re in for a little surprise – there is none by design.

Loading a new connection can take up a long time, since with every new session to the database, all of the metadata and security context has to get loaded too. I’ve decided to perform some mental gymnastics and try and implement a simple connection pooling mechanism. Here is the (very) basic version of the mechanism, which I may continue to post changes to as they accumulate:

public static class AdomdConnectionPool
{
private static Dictionary<string, Dictionary<string, ArrayList>> pool = 
new Dictionary<string, Dictionary<string, ArrayList>>(); /// <summary> /// Gets a connection from the pool or creates one if one does not exist. /// </summary> public static PooledConnection GetConnection(string connectionString) { // Pooling (See the Poll method) ValidateListExistance(connectionString); return GetConnectionFromPool(connectionString); } private static PooledConnection GetConnectionFromPool(string connectionString) { KeyValuePair<string, ArrayList> session =
new KeyValuePair<string, ArrayList>(null, null); lock (pool[connectionString]) { if (pool[connectionString].Count > 0) { // Available session exists. Use it. session = pool[connectionString].First(); pool[connectionString].Remove(session.Key); } } // No available connections exist. Create a new one. if (session.Key == null) return CreateNewConnection(connectionString); // A session exists AdomdConnection pooledConnection = new AdomdConnection(connectionString); pooledConnection.SessionID = session.Key; try { pooledConnection.Open(); // Register the session with the pool. PooledConnection poolItem = new PooledConnection(pooledConnection, session.Value); poolItem.Disposed += new Action<PooledConnection, EventArgs>(poolItem_Disposed); return poolItem; } catch (Exception ex) { // Connection probably expired. Try again. return GetConnectionFromPool(connectionString); } } private static void ValidateListExistance(string connectionString) { lock (pool) { if (!pool.ContainsKey(connectionString)) pool.Add(connectionString, new Dictionary<string, ArrayList>()); } } private static PooledConnection CreateNewConnection(string connectionString) { // Create a new connection and register it with the pool. PooledConnection poolItem = new PooledConnection(
new AdomdConnection(connectionString),
new ArrayList()); poolItem.Disposed += new Action<PooledConnection, EventArgs>(poolItem_Disposed); poolItem.Connection.Open(); return poolItem; } private static void poolItem_Disposed(PooledConnection sender, EventArgs e) { Dictionary<string, ArrayList> connections = pool[sender.Connection.ConnectionString]; try { // Close the connection, but keep the session alive. sender.Connection.Close(false); lock (connections) { // Reclaim the connection to the pool. connections.Add(sender.Connection.SessionID, sender.ExtraData); } } catch { // Can't close connection? Don't let it back in the pool. // We don't really care why, though. If necessary in the future, log. } } }

Each new connection has a SessionID property, which is unique and is given to each connection that is opened without an existing value in the SessionID property. If there is a value in the property before the connection opens, the connection connects to that session once Open is called. It may have been wiser to use the same SessionID for all connections, but since timeouts may happen on old connections (see the end of the poolItem_Disposed method), I decided to use many, fresh connections. I admit I have no idea whether it’s a best practice, but I have yet to see evidence otherwise.

Each pooled connection has two properties we need to use: Connection and ExtraData:

public class PooledConnection : IDisposable
{
/// <summary>
/// Creates a new instance of <see cref="PooledConnection" />.
/// </summary>
internal PooledConnection(AdomdConnection connection, ArrayList extraData)
{
this.Connection = connection;
this.ExtraData = extraData;
}
public AdomdConnection Connection { get; private set; }
public ArrayList ExtraData { get; private set; }
void IDisposable.Dispose()
{
if (this.Disposed != null)
this.Disposed(this, EventArgs.Empty);
}
internal event Action<PooledConnection, EventArgs> Disposed;
}

The ExtraData property is there to store connection-specific data, such as a list of session-scoped members already created at runtime on this session, etc.

Using the mechanism is very simple and resembles using a simple ADO.NET connection:

using (PooledConnection pooledConnection = AdomdConnectionPool.GetConnection(connectionString))
{
// Execute the query
AdomdDataAdapter adapter = new AdomdDataAdapter(query, pooledConnection.Connection);
}

Please note that you can not use this sort of connection pooling when you’re using the integrated role-based security, unless you save your sessions according to roles. This has not and will not be implemented in my connection pooling mechanism and if you need it, you’ll have to write one for yourself.

I would love to hear comments about the whole thing. Remember that it’s pretty basic, but I would love to make it more interesting and intricate and most of all – useful.

Advertisements

One thought on “SQL Server 2005 Analysis Services’s ADOMD.NET Connection Pooling, or Lack Thereof

  1. Hi Omer,
    What has your experience been with your connection pool class so far? Is it working well for you?
    I am using a connection facade class in which I cache the SessionID in the user’s ASP.NET Session and then reuse it. If I can’t create a new connection with the same session I catch the thrown exception, create a new connection without the old SessionID, and then cache the new SessionID.
    It has worked well so far, but I am also caching the connection itself for the life-time of the HttpRequest. Multiple parts on a page may use the same connection in our app. As I look to make our pages multi-threaded this probably won’t work any longer. I’m curious about using a connection pool class like your combined with SessionID management.
    Thoughts?
    Thanks,
    Terry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s