Creating a Faster DLookup()

Introduction:

Have you ever been given the advice NOT to use domain functions inside queries?  I have, and I have repeated that advice over and over again; why?  The main reason is that this process is inherently slow.

But what if you absolutely need that value in your dataset?  I recently ran into this problem, and no matter what I tried, I could not get a particular field into a dataset and keep the rest of the recordset updateable, without using a DLookup().  This was extremely frustrating for users of the application, because it took close to 30 seconds for the datasheet to finish "calculating" every time the form was loaded or refreshed (this with only about 450 records). 

There are two primary reasons for the slowness associated with including domain functions in queries:
1.  Every call to a domain function requires that a new recordset be created using the table you want to grab data from and using the critieria you provided.  This is the same thing that happens when you use a correlated subquery (sample below) in your queries.

SELECT A.ID, A.Description,
                      (SELECT B.SomeValue FROM B WHERE B.SomeID = A.ID) as SomeValue
                      FROM A

Select all Open in new window

2.  When you use a domain function (DLookup, DMax, DMin, DSum) or a user defined function in your query, the query parser is unable to see inside the function, so it has no means of determining the optimal execution plan.  This is the main reason you don't want to use Domain functions (or UDF's that return data) in queries.

In my case, the query I was using was selecting data from a single table with a criteria on a date field, so I was not too concerned about the effect of #2 (above) and felt I needed to focus on #1.  I did some research and found that another developer (Allen Browne) had developed a version of the DLookup() function, called ELookup(), which is quicker, and provides additional functionality, but still opened a recordset every time it was called, and required that I build a criteria string within the context of my query.  I was not happy with either of those requirements, so I asked a couple of other Access experts for recommendations.  After some discussion, I combined several of their recommendations into a solution which was significantly faster (under 2 seconds).

Creating a User Defined Function (UDF):
To be clear, this solution involves writing your own user defined function, but the speed with which this operates makes it well worth it.  The first step in developing this lookup function was to determine the parameters which would be required to find the value I was looking for, and determine the datatype I wanted to return to the calling process.  In this particular instance, I was looking for a Tax Rate, which was determined by the value of three fields ([State_CD], [Product], [Rate_CD]) in my query (qry_Current_Tax_Rates).  I realized that I would need to pass these three values into the function as arguments and, because I could not guarantee that all of these values would be present in the underlying recordset, I had to declare them as variants.  I also determined that if any of those values was missing, or I could not find a value in my recordset consistent with those three values, I would return a NULL to the calling process, so I declared the return value of the function as a variant as well.

Public Function fnTaxRateLookup(StateCode as Variant, _
                                                      ProductCode as Variant, _
                                                      RateCode as Variant, _
                                                      Optional Reset as Boolean = False, _
                                                      Optional CloseRS as Boolean) as Variant

Select all Open in new window

Defining function variables:
When I started work on this function, I declared a global recordset variable that I could access from anywhere in my application, but as I used the function within my application, I decided that what I really needed was a static variable that would retain its value between function calls.  I also realized that because my source data had a limited number of States, Products, and Rate Codes, that I could speed up the application by not performing the lookup operation if the values passed to the function  were the same as the previously passed values, so my variable declarations morphed into:

Static myRS As DAO.Recordset
                      Static myState As Variant
                      Static myProduct As Variant
                      Static myRateCode As Variant
                      Static myRate As Variant
                                
                      Dim strSQL As String
                      Dim strCriteria As String

Select all Open in new window

Checking for repeat arguments:

As I implemented this function I found that the source recordset (where the functions arguments came from) was frequently sorted by the fields used in this function, I determined I could speed the process up by not performing the lookup if the arguments for the current funcion call were identical to those passed during the previous call.  The code for that segment of the function looks like:

fnTaxRateLookup = Null
                      If IsNull(StateCode) Or IsNull(ProductCode) Or IsNull(RateCode) Then Exit Function
                                
                      If (Nz(myState, "") = StateCode) And (Nz(myProduct, "") = ProductCode) _
                      And (Nz(myRateCode, "") = RateCode) Then
                          fnTaxRateLookup = myRate
                          Exit Function
                      End If

Select all Open in new window

Resolving the recordset issue:

As mentioned in the introduction, part of the reason that using the DLookup() function in this situation is so slow is that it has to open a new recordset based on the parameters you provide, every time you call the function.  To avoid this, I created a static recordset and check to see whether that recordset has been instantiated each time the function is called.

If (myRS Is Nothing) Or Reset Then
                           Set myRS = CurrentDb.OpenRecordset("qry_Current_Tax_Rates", , dbFailOnError)
                      End If

Select all Open in new window

Retrieving the lookup value:

There are two generally accepted methods for finding a record in a DAO recordset which meet a specified criteria.  The recordset.FindFirst method allows you create a criteria string to search for within the recordset.  The syntax I used in my function was:

Dim strQuote as string
                      
                      strQuote = chr$(34)
                      strCriteria = "[State_CD] = " & strQuote & StateCode & strQuote _
                              & " AND [Product] = " & strQuote & ProductCode & strQuote _
                              & " AND [Rate_CD] = " & strQuote & RateCode & strQuote
                      With myRS
                          .FindFirst strCriteria
                          IF .NoMatch Then
                              fnTaxRateLookup = NULL
                          ELSE
                              fnTaxRateLookup = !TaxRate
                          END IF
                      End With

Select all Open in new window

The recordset.SEEK method is significantly faster than the FindFirst method because it takes advantage of an index.  However, this method requires that:

1. the recordset be opened as a table data type (dbOpenTable)
2. the fields being searched are contained within a named index

Although you cannot use this method with a linked table, you can set a reference to that table in its source database, as opposed to referencing the linked table in the currentdb.  To do so, the syntax used to set the reference to the recordset would be:

If (myRS Is Nothing) Or Reset Then
                           Dim db as DAO.Database
                           Set db = DBEngine.OpenDatabase("SourceDatabaseName")
                           Set myRS = Db.OpenRecordset("SourceTableName", dbOpenTable, dbFailOnError)
                      End If

Select all Open in new window

The query I was using in my function did not meet these requirements, but if it had, the syntax for using the SEEK method would have been:

With myRS
                          .Index = "StateProductRate"   '<= this is the name of the index
                          .Seek "=", StateCode, ProductCode, RateCode
                          IF .NoMatch then 
                              fnTaxRateLookup = NULL
                          ELSE
                              fnTaxRateLookup = !Tax_Rate
                          END IF
                      END With

Select all Open in new window

Note:  For more information on fast searchs visit Marcus Fischer's article on Fast Table Lookup Functions.

As I implemented this solution, I realized that I also needed a way to close the recordset, since it was not being closed at the end of each call.  To do so, I added an additional, optional argument to the function declaration and added a couple of additional lines of code to the function:

If CloseRS Then
                          If (myRS Is Nothing) = False Then
                              myRS.Close
                              Set myRS = Nothing
                          End If
                          If Reset = False Then Exit Function
                      End If

Select all Open in new window

In the Close event of the form that uses this function, I simply call the function and pass it NULL values for all the arguments but CloseRS.

fnTaxRateLookup NULL, NULL, NULL,, True

Select all Open in new window

The final code for my "Faster Dlookup" function is shown below.  So far, I've found that this works well in both queries as well as in the Control Source property of  textbox on a form.

Public Function fnTaxRateLookup(StateCode As Variant, _
                                                ProductCode As Variant, _
                                                RateCode As Variant, _
                                                Optional Reset As Boolean = False, _
                                                Optional CloseRS As Boolean = False) As Variant
                      
                           Static myRS As DAO.Recordset
                           Static myState As Variant
                           Static myProduct As Variant
                           Static myRateCode As Variant
                           Static myRate As Variant
                               
                           Dim strSQL As String
                           Dim strCriteria As String
                                
                           On Error GoTo ProcError
                      
                           'Explicitly set the default return value of the function
                           fnTaxRateLookup = Null
                                
                           'If user passed the CloseRS = True, then close the recordset
                           If CloseRS Then
                               If (myRS Is Nothing) = False Then
                                   myRS.Close
                                   Set myRS = Nothing
                               End If
                               If Reset = False Then Exit Function
                           End If
                           
                           'If any of the required arguments is NULL, exit function     
                           If IsNull(StateCode) Or IsNull(ProductCode) _
                           Or IsNull(RateCode) Then Exit Function
                      
                           'If all of the required arguments are the same as during the 
                           'previous function call return the previous selected rate
                           If (Nz(myState, "") = StateCode) _
                           And (Nz(myProduct, "") = ProductCode) _
                           And (Nz(myRateCode, "") = RateCode) Then
                               fnTaxRateLookup = myRate
                               Exit Function
                           End If
                                
                           'Open the recordset if not already open or Reset = true
                           If (myRS Is Nothing) Or Reset Then
                               Set myRS = CurrentDb.OpenRecordset("qry_Current_Tax_Rates", , dbFailOnError)
                           End If
                                
                           strCriteria = "[State_CD] = " & Quotes(StateCode) & " AND " _
                                       & "[Product] = " & Quotes(ProductCode) & " AND " _
                                       & "[Tax_Rate_Code] = " & Quotes(RateCode)
                           With myRS
                               .FindFirst strCriteria
                               myRate = IIf(.NoMatch, Null, !Tax_Rate)
                           End With
                      
                           'set the value of the static variables
                           myState = StateCode
                           myProduct = ProductCode
                           myRateCode = RateCode
                           fnTaxRateLookup = myRate
                                
                      ProcExit:
                           Exit Function
                      
                      ProcError:
                           Debug.Print "fnTaxRateLookup", Err.Number, Err.Description
                           MsgBox Err.Number & vbCrLf & Err.Description, , "fnTaxRateLookup"
                                    
                      End Function

Select all Open in new window