VBA Connection Pooling


I was messing around with SQL Server Profiler recently and thought to myself “this would be a good way to illustrate VBA connection pooling to people”. Many books recommend using connection pooling when building apps that involve more than a trivial amount of SQL database work. Most of those books describe connection pooling and how to perform it, but none backup their assertions with actual proof. Of course, they are correct in what they are saying and connection pooling is a very useful paradigm to implement. This blog post, however, goes one step further and shows you what is happening ‘on the server side’.

The screenshot below you’ll see I’ve got 3 apps open: SQL Server Profiler (top), MS Access VBA IDE (middle) and Task Manager (bottom).

ConectPool1

You’ll see I’ve already started a trace on Profiler and also stepped into the VBA procedure ‘TestConnection’.  I have instantiated and instance of the ADODB.Connection class and set its ConnectionString property to point to the Northwind database on my (local) instance of SQL Server running on the same machine.  I’m using ODBC (“{SQL Server}”) but you can use OLE DB.  It doesn’t make a difference in this demo.  You’ll see I’ve also set the ‘App’ property in the connection string.  This is useful as it allows me to easily spot it in Profiler.

You can deduce from the above that nothing has happened on SQL server from creating the ADODB.Connection object and setting its ConnectionString property.  However, when we ‘Open’ the connection:

ConectPool2

You can see in Profiler that a connection to the SQL Server has been established.  You can see the ‘ApplicationName’ and you’ll see the ‘ClientProcessID’ corresponds to the PID shown in Task Manager.

Now see what happens when we ‘Close’ the connection:

ConectPool3

Nothing.  Profiler is still showing the connection exists.  So, as the books say, ‘closing’ a connection in VBA does not bring down the connection with SQL Server.  Now see what happens when we remove the reference ‘con’ to the instance of ADODB.Connection that we have created, ie. when we ‘set con = nothing’:

ConectPool4

You can see in Profiler that the connection to SQL Server has been severed.  So, the rules are:

1.  Connections are ‘established’ with Connection.Open; and

2.  Connections are ‘severed’ with Set Object = Nothing.

In actual fact, 2. is not strictly correct, for setting an object’s reference to ‘Nothing’ does not guarantee it will be destroyed.  If the object has more than one reference to it, only when the reference count reaches zero is the object’s space in RAM de-allocated.  Every object created by VBA implements a reference counter and when the reference count for that object reaches zero, the memory space occupied by that object is handed back for re-use.  This COM-based ‘reference counting’ can give rise to memory leaks when you have two objects that reference each other.  For example, consider the case where you create a reference (‘RefA’) to an instance of type ‘Parent’ and Parent then creates a reference (‘RefC’) to an instance of type ‘Child’ and Child creates a reference (‘RefP’) back to its Parent.  At this point, Parent has two references to itself (RefA and RefP) and Child has only one reference to itself (RefC).  Note that the only way to get to Child is through its Parent.  If we then destroy the reference RefA (ie. Set RefA = Nothing) then both Parent and Child are still in memory (as they both have a reference count of 1) but we no longer have any way of accessing them (the reference/pointer ‘RefA’ no longer exists).  Consequently, with non-zero reference counts the objects’ memory space is not handed back for re-use with the effect that the memory has ‘leaked’ out of the system.  The only way to get the memory back is to terminate the entire application (Access, Excel, etc.).  The way to prevent the leak happening in the first place would be to ensure Child is destroyed (has zero references to it) before Parent’s reference count reaches zero.  In practice, this is achieved in the ‘Class_Terminate()’ procedure of Parent by ‘Set RefC = Nothing’, ie. RefC is destroyed (the only reference to Child) and, therefore, its reference ‘RefP’ to Parent is destroyed – before RefA is destroyed (which then bring the reference count for Parent down to zero).  So there you have it, a little side-track on the importance of reference counting in VBA.

When an object goes ‘out of scope’ any references that were in scope are destroyed.  If there are no other references to that object then the object is destroyed (its memory space is released).  We can see this in the combination of the next two screenshots:

ConectPool5

ConectPool6

In this example, I have removed the explicit ‘Set con = Nothing’ and re-run the code pausing at the very last line (‘End Sub’).  You can see in the first screenshot that a connection has been established but not severed at this point.  In the second screenshot, where the code is run completely, you can see in Profiler that the connection has been severed.  This happened because there were no other references to the ADODB.Connection object other than ‘con’ and this reference went out of scope when the code completed.  Consequently, the reference count reached zero and the object was destroyed, thus severing the connection to SQL Server.

Before discussing Connection Pooling directly, I want to point out something that is often stated but rarely shown: the ‘cost’ of establishing a connection.

ConectPool7

In the above screenshot you’ll see I have added a couple of lines to write out time information to the Immediate Window.  I have run this code and, as shown in Profiler, a connection was established and then severed.  You can see in the Immediate Window that it took approx. 3 seconds to establish the connection.  YMMV but it shows that applications which involved a lot of SQL can have their performance negatively impacted dramatically if connections are repeatedly being established.  The better approach is to use Connection Pooling, to which I now turn.

ConectPool8
In the above screenshot you’ll see I’ve made ‘con’ a module-level reference.  What you can’t see is that I’ve already run the code.  You can see in Profiler the ‘Audit Login’ but there is no corresponding ‘Audit Logout’ despite the code having completed.  The connection is still there.  Consequently, any procedures that can ‘see’ the reference ‘con’ can make use of the connection by simply ‘Open’-ing it, running any code and then ‘Close’-ing it again, thereby making it available for other procedures.  As VBA does not offer multi-threading, we only need one connection to be in ‘the pool’ and this is what we’ve got.  Strictly speaking, there is no connection ‘pooling’ in VBA as there is no ‘pool’ of connections to choose from – there is only one.  In practice, this is just semantics as the effect is the same – a fast way to access an external database.

So there you have it: VBA Connection Pooling with a side-bar on the importance of reference counting.  Finally, just to complete things:

ConectPool9

Note what I’ve done in the Immediate Window and the effect captured in Profiler.

Categories:VBA

1 comment

  1. Reblogged this on Christopher J. McClellan and commented:
    Interesting read about Connection Pooling in VBA and SQL Server. I knew circular references are bad juju, but I didn’t know about the “App” property of a connection, or that `conn.Close` doesn’t actually sever the connection. The connection isn’t really severed until the `ADODB.Connection` is garbage collected. Highly recommended reading here.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: