Which is faster, DLookup or Recordset?

[DevastatioN]

Hello,

Just a quick question because I am unsure of the technical specifications of each. Which is faster to lookup quick information, a DLookup or a Recordset. I have security built into the form to check the username, and show options appropriately.

I originally made the code using recordset and planned to migrate it to DLookup after the prototype was complete, but am wondering which is faster now. Here is the example code:

Recordset:

'Set the form based on user rights and privileges
    Dim username As String
    Dim leftTop As Integer
    
    'This is the height on the form of the top buttons
    leftTop = 496
    
    'Lookup the current user logged in
    username = DLookup("[LoggedIn]", "[tblCurrentUser]")
    
    'If the user is supposed to have the Add/Edit Items button, show the button and place at leftTop
    If DLookup("[AddEditFileButton]", "[tblUsers]", "[UserID]='" & username & "'") = -1 Then
        Me.btnAddEditItems.Visible = True
        'Set leftTop for next button in list
        leftTop = leftTop + 480
    Else
        Me.btnAddEditItems.Visible = False
    End If
    
    'Set the other buttons accordingly
    Me.btnViewItems.Top = leftTop
    leftTop = leftTop + 480
    Me.btnOverdueItems.Top = leftTop
    leftTop = leftTop + 480
    Me.btnArchivedItems.Top = leftTop
    
    'Reset the top location for the next column
    leftTop = 496
    
    'If the user is supposed to have the Add/Edit Folders button, show the button and place at leftTop
    If DLookup("[AddEditFolderButton]", "[tblUsers]", "[UserID]='" & username & "'") = -1 Then
        Me.btnAddEditFolders.Visible = True
        'Set leftTop for next button in list
        leftTop = leftTop + 480
    Else
        Me.btnAddEditFolders.Visible = False
    End If
    
    'Set the other buttons accordingly
    Me.btnViewFolders.Top = leftTop
    
    'Reset the top location for the next column
    leftTop = 496
    
    'Set the change password button to top of third column
    Me.btnChangePass.Top = leftTop
    leftTop = leftTop + 480
    
    'If the user is supposed to have the Users button, show the button and place at leftTop
    If DLookup("[AddEditUsers]", "[tblUsers]", "[UserID]='" & username & "'") = -1 Then
        Me.btnUsers.Visible = True
        Me.btnUsers.Top = leftTop
        leftTop = leftTop + 480
    Else
        Me.btnUsers.Visible = False
    End If
    
    'If the user is supposed to have the Archive button, show the button and place at leftTop
    If DLookup("[ArchiveButton]", "[tblUsers]", "[UserID]='" & username & "'") = -1 Then
        Me.btnArchive.Visible = True
        Me.btnArchive.Top = leftTop
        leftTop = leftTop + 480
    Else
        Me.btnArchive.Visible = False
    End If

DLookup:

'Set the form based on user rights and privileges
    Dim username As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim leftTop As Integer
    
    'This is the height on the form of the top buttons
    leftTop = 496
    
    'Lookup the current user logged in
    username = DLookup("[LoggedIn]", "[tblCurrentUser]")
    
    'Get the recordset of the username currently logged in
    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblUsers WHERE ([UserID] = '" & username & "')"
    
    Set rs = db.OpenRecordset(strSQL)
    
    'If the user is supposed to have the Add/Edit Items button, show the button and place at leftTop
    If rs("AddEditFileButton").Value = -1 Then
        Me.btnAddEditItems.Visible = True
        'Set leftTop for next button in list
        leftTop = leftTop + 480
    Else
        Me.btnAddEditItems.Visible = False
    End If
    
    'Set the other buttons accordingly
    Me.btnViewItems.Top = leftTop
    leftTop = leftTop + 480
    Me.btnOverdueItems.Top = leftTop
    leftTop = leftTop + 480
    Me.btnArchivedItems.Top = leftTop
    
    'Reset the top location for the next column
    leftTop = 496
    
    'If the user is supposed to have the Add/Edit Folders button, show the button and place at leftTop
    If rs("AddEditFolderButton").Value = -1 Then
        Me.btnAddEditFolders.Visible = True
        'Set leftTop for next button in list
        leftTop = leftTop + 480
    Else
        Me.btnAddEditFolders.Visible = False
    End If
    
    'Set the other buttons accordingly
    Me.btnViewFolders.Top = leftTop
    
    'Reset the top location for the next column
    leftTop = 496
    
    'Set the change password button to top of third column
    Me.btnChangePass.Top = leftTop
    leftTop = leftTop + 480
    
    'If the user is supposed to have the Users button, show the button and place at leftTop
    If rs("AddEditUsers").Value = -1 Then
        Me.btnUsers.Visible = True
        Me.btnUsers.Top = leftTop
        leftTop = leftTop + 480
    Else
        Me.btnUsers.Visible = False
    End If
    
    'If the user is supposed to have the Archive button, show the button and place at leftTop
    If rs("ArchiveButton").Value = -1 Then
        Me.btnArchive.Visible = True
        Me.btnArchive.Top = leftTop
        leftTop = leftTop + 480
    Else
        Me.btnArchive.Visible = False
    End If

    'Release recordset and database links
    Set rs = Nothing
    Set db = Nothing

 

[gemma-the-husky]

i would have thought that for a single read, dlookup MUST be more optimised than opening a recordset

 

[DevastatioN]

So even though I am calliing the DLookup many times, it should still be faster than keeping the recordset open and doing all of the tests? Currently for this form there are four specific tests, but in the future this could grow.

 

[gemma-the-husky]

we discussed this some time back

i think it was thedocman who had great insight into this

i think if you need different records, then 4 dlookups
if its different fields from the same record then it might be different

 

[DevastatioN]

Thanks very much for your help and insight.

 

[LPurvis]

For a single read, single record and single field a recordset can still beat a Domain function. It depends upon the location of the table.
The Domains are excellently optimised for local table access.
If you want a count of all records in a table then
DCount("*", "tblName")
is just about as fast as you can get
i.e. it will beat
CurrentDb.OpenRecordset("SELECT COUNT(*) FROM tblName")(0)
or equally
CurrentProject.Connection.Execute("SELECT COUNT(*) FROM tblName")(0)

[aside]Of course - while we're aiming at full tables in this example, then that optimisation is taken further by the less perfect (but fastest of all on local tables)
CurrentDb.TableDefs("tblName").RecordCount
[/aside]

Once you move to linked tables then you're likely better off with a recordset for maximum performance - but for a single hit it rarely matters really.
There are pre-made custom function replacements (TLookup and ELookup etc) using recordsets instead if you're so inclined.

Multiple calls then it's really a no-brainer. Recordset every time.

(FWIW multiple fields within the same table in one hit can still use the DLookup and concat the field values together in the single call and split them back out. Still not as efficient and pretty pointless given that there's distinct recordset fields to use instead).

Cheers.