Use Caching to Speed Up Editable Forms with Hidden Duplicate Values
In this installment, we reduce our database calls by more than 80% by shifting the bulk of the work to high-performing VBA dictionary structures.
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 FunctionIn 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:
Mike Wolfe
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 FunctionDebug 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
