Use Caching to Speed Up Editable Forms with Hidden Duplicate Values

This article is Part 6 of my "Hiding Duplicate Values" Mini-Series.

To follow along with the screenshots and examples, download the sample data from here: Sample Data for the "Hiding Duplicate Values" Series.


Starting Point

This article is a direct follow-up to Part 5, so be sure to read that first for context.

As a brief refresher, we wrote the following custom functions to help control the visibility of our continuous form's group header values:

Private Function IsTopInvoiceItem(InvoiceID As Long, _
                                  InvItemID As Long) As Boolean
    Dim SQL As String
    SQL = "SELECT InvItemID " & _
          "FROM Part4 " & _
          "WHERE InvoiceID=" & InvoiceID
          
    Dim TopInvItemID As Long
    TopInvItemID = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)!InvItemID
    
    IsTopInvoiceItem = (InvItemID = TopInvItemID)
End Function

Private Function IsTopCustomerItem(CustomerID As Long, _
                                   InvItemID As Long) As Boolean
    Dim SQL As String
    SQL = "SELECT InvItemID " & _
          "FROM Part4 " & _
          "WHERE CustomerID=" & CustomerID
          
    Dim TopInvItemID As Long
    TopInvItemID = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)!InvItemID
    
    IsTopCustomerItem = (InvItemID = TopInvItemID)
End Function

In the Next Steps section of Part 5, I wrote the following:

Careful readers may have realized that the above two functions will be called a total of 52 times for the 13 records in our sample form.

Our recordset-based functions likely perform better than the earlier DCount() solution, but opening and closing that many recordsets is still wasteful.  Now imagine doing that for a form with hundreds of records.  Connected to an Azure SQL cloud backend.

In the next article, we'll explore a technique we can use to drastically reduce the performance impact of this approach.

Let's explore that performance-enhancing technique.

High-Level Overview: Caching

"Caching" is one of those words that sounds fancier than it is.

All it really means is that we're saving the results of our calculations so that we don't have to repeat them unnecessarily.

Many programming languages and web frameworks have entire modules dedicated to caching, but there's nothing like that in VBA.  That means we'll have to roll our own solution, but as you'll see, it's not that hard.

Implementation Approach

We will use two dictionary object variables to store the results of our recordset look-ups.

I covered this technique in an earlier article:

VBA Performance Tip: How to Cache Results of Slow Processes
Store expensive function call results with memoization. Learn to implement this technique with our step-by-step instructions and sample code.

One of the nice things about this approach is that it is self-healing.  If the code gets reset and our dictionaries get emptied, they will automatically refill as the functions are called.

The Code

'For more info on the typThis class, see:
'   https://nolongerset.com/this/
Private Type typThis
    'Requires reference to "Microsoft Scripting Runtime"
    ' see: https://nolongerset.com/references-safe-list/
    TopInvoiceItems As New Dictionary
    TopCustomerItems As New Dictionary
End Type
Private this As typThis


Private Function IsTopInvoiceItem(InvoiceID As Long, _
                                  InvItemID As Long) As Boolean
    
    'Check to see if we've already looked up
    '   the top InvItemID for this Invoice
    If Not this.TopInvoiceItems.Exists(InvoiceID) Then
        Debug.Print "Looking up Invoice "; InvoiceID
        
        'If not, look it up...
        Dim SQL As String
        SQL = "SELECT InvItemID " & _
              "FROM Part4 " & _
              "WHERE InvoiceID=" & InvoiceID
        Dim TopInvItemID As Long
        TopInvItemID = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)!InvItemID
        
        '...and save it in the TopInvoiceItems dictionary for later use
        this.TopInvoiceItems.Add Key:=InvoiceID, Item:=TopInvItemID
        
    End If
    
    'At this point, we *know* that the dictionary
    '   has an entry for the passed InvoiceID
    Dim SavedTopInvItemID As Long
    SavedTopInvItemID = this.TopInvoiceItems.Item(InvoiceID)
    
    'Compare the passed InvItemID to the one in the dictionary cache
    IsTopInvoiceItem = (InvItemID = SavedTopInvItemID)
    
    Debug.Print "Invoice: ", InvoiceID, InvItemID, SavedTopInvItemID, IsTopInvoiceItem
End Function

Private Function IsTopCustomerItem(CustomerID As Long, _
                                   InvItemID As Long) As Boolean
    
    'Check to see if we've already looked up
    '   the top InvItemID for this Customer
    If Not this.TopCustomerItems.Exists(CustomerID) Then
        Debug.Print "Looking up Customer "; CustomerID
        
        'If not, look it up...
        Dim SQL As String
        SQL = "SELECT InvItemID " & _
              "FROM Part4 " & _
              "WHERE CustomerID=" & CustomerID
        Dim TopInvItemID As Long
        TopInvItemID = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)!InvItemID
    
        '...and save it in the TopCustomerItems dictionary for later use
        this.TopCustomerItems.Add Key:=CustomerID, Item:=TopInvItemID
        
    End If
    
    'At this point, we *know* that the dictionary
    '   has an entry for the passed CustomerID
    Dim SavedTopInvItemID As Long
    SavedTopInvItemID = this.TopCustomerItems.Item(CustomerID)
    
    'Compare the passed InvItemID to the one in the dictionary cache
    IsTopCustomerItem = (InvItemID = SavedTopInvItemID)
    
    Debug.Print "Customer: ", CustomerID, InvItemID, SavedTopInvItemID, IsTopCustomerItem
End Function

Debug Output

I added Debug.Print statements to the above code so that you could see how the caching works in practice.

As a reminder, there are 13 records in our sample data.  We have four group header controls in each row.  That results in 52 calls (13×4) to our custom functions.  

Without caching, we were opening a recordset 52 times.

With caching, we are opening a recordset 9 times (once for each unique CustomerID [4] and InvoiceID [5]).

You can imagine how big a performance boost this could give you, especially when working with a relatively slow connection to your backend, such as a SQL Azure cloud database.

Here's what the Immediate Window looks like after we open our form:

Looking up Customer  4 
Customer:      4             5008          5008         True
Customer:      4             5008          5008         True
Customer:      4             5008          5008         True
Looking up Invoice  1004 
Invoice:       1004          5008          5008         True
Invoice:       1004          5008          5008         True
Customer:      4             5007          5008         False
Customer:      4             5007          5008         False
Invoice:       1004          5007          5008         False
Invoice:       1004          5007          5008         False
Customer:      4             5009          5008         False
Customer:      4             5009          5008         False
Invoice:       1004          5009          5008         False
Invoice:       1004          5009          5008         False
Customer:      4             5011          5008         False
Customer:      4             5011          5008         False
Looking up Invoice  1005 
Invoice:       1005          5011          5011         True
Invoice:       1005          5011          5011         True
Customer:      4             5013          5008         False
Customer:      4             5013          5008         False
Invoice:       1005          5013          5011         False
Invoice:       1005          5013          5011         False
Customer:      4             5010          5008         False
Customer:      4             5010          5008         False
Invoice:       1005          5010          5011         False
Invoice:       1005          5010          5011         False
Customer:      4             5012          5008         False
Customer:      4             5012          5008         False
Invoice:       1005          5012          5011         False
Invoice:       1005          5012          5011         False
Looking up Customer  3 
Customer:      3             5006          5006         True
Customer:      3             5006          5006         True
Looking up Invoice  1003 
Invoice:       1003          5006          5006         True
Invoice:       1003          5006          5006         True
Customer:      3             5005          5006         False
Customer:      3             5005          5006         False
Invoice:       1003          5005          5006         False
Invoice:       1003          5005          5006         False
Looking up Customer  2 
Customer:      2             5003          5003         True
Customer:      2             5003          5003         True
Looking up Invoice  1002 
Invoice:       1002          5003          5003         True
Invoice:       1002          5003          5003         True
Customer:      2             5004          5003         False
Customer:      2             5004          5003         False
Invoice:       1002          5004          5003         False
Invoice:       1002          5004          5003         False
Looking up Customer  1 
Customer:      1             5002          5002         True
Customer:      1             5002          5002         True
Looking up Invoice  1001 
Invoice:       1001          5002          5002         True
Invoice:       1001          5002          5002         True
Customer:      1             5001          5002         False
Customer:      1             5001          5002         False
Invoice:       1001          5001          5002         False
Invoice:       1001          5001          5002         False