Using "saved queries" instead of "DLookup for performance boost.

J

John Phelan

When I use FMS, “Total Access Analyzer†I get a report with a list of,
“Performance Items†that includes a reference to the use of, “DLookupâ€:

“Module Domain Functions in Code

Domain functions such as DLookup, DMin, Max, etc are often slower than an
equivalent saved query, Where possible, consider replacing Domain functions
with saved queries that are optimized for performance.â€

How do you replace the Domain Function, DLookup, below, with a saved query,
sited in the performance report?

Private Function TipImg(sColor, vGuestID) As String
On Error GoTo ErrLine
Dim sTemp As String, sRet As String
sRet = DLookup("Name", "qryGuestName", "GuestID = " & vGuestID)
Click to expand...

Select Case sColor
Case "N": sTemp = "Incomplete Entry"
Case "R": sTemp = "Fixed Reservation"
Case "B": sTemp = "Normal Reservation"
Case "G": sTemp = "Paid Reservation or Occupancy"
Case "Y": sTemp = "Unit is Unavailable"
Case "P": sTemp = "Normal Occupancy"
End Select

TipImg = sRet & vbCrLf & sTemp

ExitLine:
Exit Function
ErrLine:
Resume ExitLine
End Function


- John
 
M

MGFoster

John said:
When I use FMS, “Total Access Analyzer†I get a report with a list of,
“Performance Items†that includes a reference to the use of, “DLookupâ€:

“Module Domain Functions in Code

Domain functions such as DLookup, DMin, Max, etc are often slower than an
equivalent saved query, Where possible, consider replacing Domain functions
with saved queries that are optimized for performance.â€

How do you replace the Domain Function, DLookup, below, with a saved query,
sited in the performance report?

Private Function TipImg(sColor, vGuestID) As String
On Error GoTo ErrLine
Dim sTemp As String, sRet As String




Select Case sColor
Case "N": sTemp = "Incomplete Entry"
Case "R": sTemp = "Fixed Reservation"
Case "B": sTemp = "Normal Reservation"
Case "G": sTemp = "Paid Reservation or Occupancy"
Case "Y": sTemp = "Unit is Unavailable"
Case "P": sTemp = "Normal Occupancy"
End Select

TipImg = sRet & vbCrLf & sTemp

ExitLine:
Exit Function
ErrLine:
Resume ExitLine
End Function
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In this case you'd not substitute a stored query 'cuz the DLookup() is
using a query as its data source. Stored queries are faster than domain
aggregate functions 'cuz they use the table's indexes. A query doesn't
have any indexes so there would not be any performance gain in referring
to the query in another stored query (unless the query used as the data
source in the DLookup returns a very small data set).

You could create a new query that just returns the Name from the same
table as the query qryGuestName uses, then run that query to get the
name. I have a function I call getQueryResult that returns one column
value from a pre-defined query (stored), that may/may-not have
parameters. I use that whenever I want to avoid using a domain
aggregate function. Here it is:

Function getQueryResult(strQuery As String, ParamArray varParams() As
Variant) As Variant
' Purpose:
' Run the indicated query and return the result: 1 value in 1
column.
' In:
' strQuery The indicated query
' varParams() The array of parameters. 2 elements make up 1
parameter object.
' Format: Parameter name, Parameter value
' Out:
' Variant The result of the query - NULL if no result.
' Errors Return the error to the calling routine.
' Created:
' mgf 25feb2003
' Modified:
'

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim i As Integer

Set db = CurrentDb

' Set up the query
Set qd = db.QueryDefs(strQuery)

For i = LBound(varParams) To UBound(varParams) Step 2
qd.Parameters(varParams(i)) = varParams(i + 1)
Next i

' Get the data
Set rs = qd.OpenRecordset()

If Not rs.EOF Then getQueryResult = rs(0)

On Error Resume Next
rs.Close
qd.Close
db.Close

End Function

Usage: Call the query "myQuery" that has 2 parameters.

dim strReturn as string

strReturn = getQueryResult("myQuery","acct_nbr",25538, _
"start_date", #5/3/2004#)

Debug.Print strReturn

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIjEfYechKqOuFEgEQKgpwCff1gnBvdJdEHhMglj1puN6Pzncb0An3MH
QPw6xTEADOc/r8Sa4doSHSJ0
=syca
-----END PGP SIGNATURE-----
 
Top