VBA Performance Tip: How to Cache Results of Slow Processes

Caching is a powerful technique for boosting the performance of your application.  

A cache is any location where you store data for fast retrieval.  Caches can be found in many places, including hardware (e.g., RAM), the web (e.g., CDNs), software (e.g., hash tables), etc.

Today's article is about a specific kind of caching: memoization.

What is Memoization?

As usual, Wikipedia provides us with a pretty good starting point:

In computing, memoization is an optimization technique used primarily to speed up computer programs by storing the results of expensive function calls and returning the cached result when the same inputs occur again.

A memoized function "remembers" the results corresponding to some set of specific inputs. Subsequent calls with remembered inputs return the remembered result rather than recalculating it, thus eliminating the primary cost of a call with given parameters from all but the first call made to the function with those parameters

Memoization Example

Here's an example of a memoized function in VBA.

This function returns the size of a field definition in a table.  But that's not all it does.  It also stores the retrieved data for the next time it gets called.  The data is stored in a Dictionary, which is a data type especially well-suited for this type of data storage and retrieval.

You can call this function inside a loop with no noticeable performance hit.

' ----------------------------------------------------------------
' Procedure : GetFieldSize
' Date      : 2/17/2023
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/memoization/
' Purpose   : Returns the size of a field in a database table.
' Notes     - Uses memoization for efficient repeated calls.
'           - Uses a Unicode Private Use character as delimiter
'               to avoid the possibility of TblName/FldName collisions
'               (see: https://nolongerset.com/private-use-areas/)
' ----------------------------------------------------------------
Function GetFieldSize(TblName As String, FldName As String) As Long
    'Static variables retain their values in between function calls
    Static Dict As Object  'Scripting.Dictionary
    
    'Initialize the dictionary the first time through
    If Dict Is Nothing Then Set Dict = CreateObject("Scripting.Dictionary")
        
    'Build a unique lookup key based on table and field name
    Const PuaCharCode As Long = &HF8FF
    Dim Delim As String: Delim = ChrW(PuaCharCode)
    
    Dim Key As String
    Key = TblName & Delim & FldName
    
    'Check to see if we already retrieved the field size for this
    '   combination of TblName and FldName
    If Not Dict.Exists(Key) Then
        'This is our first time looking up the size of this field
        Dim FieldSize As Long
        FieldSize = CurrentDb.TableDefs(TblName).Fields(FldName).Size
        
        'Now that we have the size of this particular field, we will
        '   store it for faster future access
        Dict.Add Key, FieldSize
    End If
    
    'If we've gotten this far, then we *know* that the field size is
    '   stored in the dictionary; for simplicity, we ALWAYS look up
    '   the field size from the dictionary, even if we just stored it
    GetFieldSize = Dict.Item(Key)
    
End Function

Proof of Concept

The specific purpose of this function–retrieving the size of a database field–is incidental to the article.

You can use this technique with any situation where:

• A given set of inputs should always produce the same output
• Calculating the output is a relatively slow process
• You expect to call the function repeatedly for the same set of inputs
• The total number of inputs is finite and relatively small

With that in mind, and using the above example function as inspiration, here are the step-by-step instructions for implementing this technique:

1. Declare a dictionary object variable with program or module extent (e.g., a public variable, local static variable, class property, etc.)
2. Check to see if the dictionary object Is Nothing; if it is, initialize it (e.g., Set Dict = CreateObject("Scripting.Dictionary"))
3. Create a unique key based on the inputs to the function
4. Check to see if that key exists in the dictionary
5. If it does not, execute the long-running process and save the result to a local variable
6. Add an item to the dictionary using the key and the calculated value
7. Retrieve the requested value from the dictionary using the key

Self-Healing Object Variables

One of the features of this approach is that the function will work even if the code gets reset (e.g., via Run > Reset in the VBIDE menu or if there is an unhandled error).

As part of a code reset, the dictionary object variable (Dict) will be set to Nothing.  Since we are using the dictionary as our cache, we will lose our performance benefit (at least until the cache repopulates itself).  While the function may run a bit slower after a reset, the code will still return correct results.  We don't have to worry about getting error 91, "Object variable not set."

I've used this technique of having object variables that re-initialize themselves as needed, but I never had a good name for it until I read Daniel Pineault's* article, "Self-Healing Object Variables."

^{* While my first encounter with the term "Self-Healing Object Variables" did indeed come from Daniel's article, it appears Ben Clothier used the term in an article he wrote more than ten years earlier.}

Acknowledgements

• Article excerpt generated with the help of ChatGPT