Should I worry about the time needed to run DLookup on a session log table? (1 Viewer)

[JMongi]

One of my login functions checks for any outstanding sessions still running on another computer. I just realized as time "keeps on slipping, slipping, slipping...into the future..." that my log file will get longer and longer and thus my Dlookup might start taking too long to run.

tblLoginSessions
LoginID
UserName
LoginEvent
LogoutEvent
ComputerName
Notes

Public Function SessionCheck() As Boolean
On Error GoTo ErrHandler
glProcName = "modUserControl Function SessionCheck"
'This function checks the session log for any open sessions from the user and handles any already open sessions.
'The default is to prevent the user from logging in so that the code must work correctly for login.

Dim strCriteria As String, strSQL As String

strCriteria = "UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null"
SessionCheck = False

Select Case True
    Case DCount("LoginID", "tblLoginSessions", strCriteria) = 0
        'No unclosed sessions
        SessionCheck = True
        
    Case DLookup("ComputerName", "tblLoginSessions", strCriteria) = modUserControl.ComputerName
        'User is logged in on the same computer
        'Close previous session
        strSQL = "UPDATE tblLoginSessions " & _
                " SET LogoutEvent =" & Now() & ", Notes = 'Abnormal Close' " & _
                " WHERE UserName='" & modUserControl.DomainUsername & "' AND LogoutEvent Is Null AND ComputerName='" & modUserControl.ComputerName & "';"
        Debug.Print strSQL
        'CurrentDb.Execute strSQL
        SessionCheck = True
    
    Case DLookup("ComputerName", "tblLoginSessions", strCriteria) <> modUserControl.ComputerName
        'User is already logged in on another computer
        FormattedMsgBox "User " & modUserControl.DomainUsername & " is already logged in at workstation " & _
            DLookup("ComputerName", "tblLoginSessions", strCriteria) & "      " & "@User " & modUserControl.DomainUsername & _
            " MUST logout from that computer before logging in again.            @", vbCritical, "Already Logged In"
        
    Case Else
        'Something unaccounted for has happened
        'Message about it
End Select

ExitHandler:
'Any additional code needed on error
Exit Function

ErrHandler:
Call ErrProcessor(glProcName)
GoTo ExitHandler

End Function

Should I be concerned? If so, what can I do about it?

 

[CJ_London]

providing the fields you are referencing in the dlookup criteria are indexed, should not be a problem. The issues with domain functions is when they are used in queries because they have to be executed for each row.

 

[theDBguy]

I don't think there's really anything to worry about. But if you're thinking of reducing the number of DLookups to gain a little speed, then you might consider eliminating some of what I am thinking may be unnecessary steps in your login checks. For instance, something similar to this pseudocode:

Dim strWorkstation As String
strWorkstation = Nz(DLookup("ComputerName", "tblLoginSessions", strCriteria), "")
If strWorkstation = "" OR strWorkstation = modUserControl.ComputerName Then
    'Close (ALL) previous session(s)
    strSQL="UPDATE tblLoginSessions " & _
        " SET ...
        " WHERE LogOutEvent Is Null "
Else
    'User is already logged in on another computer
    FormattedMsgBox ...
End If

Just a thought...

 

[JMongi]

providing the fields you are referencing in the dlookup criteria are indexed

Thanks for the info. Do all fields used in a DLookup, Dcount, etc function need to be indexed?

 

[MarkK]

One thing that jumps out for me is you might have to run four domain aggregate functions against the same row in the same table, each one of which will have to open it's own recordset to get the value you want. In a case like this I would just open one recordset with SQL like...

SELECT * 
FROM tblLoginSession 
WHERE UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null

If I was worried about speed, I would take that step first, and potentially reduce my disk read time by 75%.

 

[MarkK]

Index the fields in your criteria.

 

[JMongi]

@theDBugy - If I am remembering correctly, one of the benefits of Select Case True is that the rest of the cases are not resolved when a case is found true.

But, I could be wrong.

 

[theDBguy]

@theDBugy - If I am remembering correctly, one of the benefits of Select Case True is that the rest of the cases are not resolved when a case is found true.

But, I could be wrong.

I think you are remembering correctly, but take a look at your third Case statement, for example. You are executing two DLookups looking for the same information. Just saying...

 

[JMongi]

It definitely could use some optimization. I'll look at it again.

The main question is about Dlookup, Dcount, etc on an ever expanding table of records.

 

[theDBguy]

The main question is about Dlookup, Dcount, etc on an ever expanding table of records.

Understood, and I did try to answer that question. As I said, in my humble opinion, you probably won't notice much difference in the speed of your login process for many years to come. But, that's just one person's point of view. Cheers!

 

[JMongi]

@theDBguy - Thanks! didn't mean to sound snarky at all. I did have a tangential question...in your code suggestion you used Nz to encapsulate the dlookup. Reason?

 

[theDBguy]

@theDBguy - Thanks! didn't mean to sound snarky at all. I did have a tangential question...in your code suggestion you used Nz to encapsulate the dlookup. Reason?

DLookup() can return a Null value, which you can't assign to a String variable.

 

[theDBguy]

DLookup() can return a Null value, which you can't assign to a String variable.

By the way, DLookup() can only return the value(s) from the first matching record. So, if a user was consistently having problems logging out, you may be only closing one of those previous sessions. Just a thought...

 

[JMongi]

Ah, makes sense. A little snipping and pruning and modifying and we get the following code (still using Select Case True instead of an OR in an If/Then).

Edit: To address your other concern. IF this code is working correctly, there should only ever be one hanging session. But, even if there is, I plan on having a mainteance code to run from an admin console to check for these outliers.

Public Function SessionCheck() As Boolean
On Error GoTo ErrHandler
glProcName = "modUserControl Function SessionCheck"
'This function checks the session log for any open sessions from the user and handles any already open sessions.
'The default is to prevent the user from logging in so that the code must work correctly for login.

Dim strCriteria As String, strSQL As String, strLastComputer
SessionCheck = False

strCriteria = "UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null"
strLastComputer = Nz(DLookup("ComputerName", "tblLoginSessions", strCriteria), "")

Select Case True
    Case strLastComputer = ""
        'No unclosed sessions
        SessionCheck = True
        
    Case strLastComputer = modUserControl.ComputerName
        'User is logged in on the same computer
        strSQL = "UPDATE tblLoginSessions " & _
                " SET LogoutEvent =" & Now() & ", Notes = 'Abnormal Close' " & _
                " WHERE UserName='" & modUserControl.DomainUsername & "' AND LogoutEvent Is Null AND ComputerName='" & modUserControl.ComputerName & "';"
        Debug.Print strSQL
        'CurrentDb.Execute strSQL
        SessionCheck = True
    
    Case strLastComputer <> modUserControl.ComputerName
        'User is already logged in on another computer
        FormattedMsgBox "User " & modUserControl.DomainUsername & " is already logged in at workstation " & _
            strLastComputer & "      " & "@User " & modUserControl.DomainUsername & _
            " MUST logout from that computer before logging in again.            @", vbCritical, "Already Logged In"
        
    Case Else
        'Something unaccounted for has happened
        'Message about it
End Select

ExitHandler:
'Since this function is a key protection to unauthorized access, any error should prevent access.
SessionCheck = False
Exit Function

ErrHandler:
Call ErrProcessor(glProcName)
GoTo ExitHandler

End Function

 

[theDBguy]

Ah, makes sense. A little snipping and pruning and modifying and we get the following code (still using Select Case True instead of an OR in an If/Then).

Edit: To address your other concern. IF this code is working correctly, there should only ever be one hanging session. But, even if there is, I plan on having a mainteance code to run from an admin console to check for these outliers.

Public Function SessionCheck() As Boolean
On Error GoTo ErrHandler
glProcName = "modUserControl Function SessionCheck"
'This function checks the session log for any open sessions from the user and handles any already open sessions.
'The default is to prevent the user from logging in so that the code must work correctly for login.

Dim strCriteria As String, strSQL As String, strLastComputer
SessionCheck = False

strCriteria = "UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null"
strLastComputer = Nz(DLookup("ComputerName", "tblLoginSessions", strCriteria), "")

Select Case True
    Case strLastComputer = ""
        'No unclosed sessions
        SessionCheck = True
       
    Case strLastComputer = modUserControl.ComputerName
        'User is logged in on the same computer
        strSQL = "UPDATE tblLoginSessions " & _
                " SET LogoutEvent =" & Now() & ", Notes = 'Abnormal Close' " & _
                " WHERE UserName='" & modUserControl.DomainUsername & "' AND LogoutEvent Is Null AND ComputerName='" & modUserControl.ComputerName & "';"
        Debug.Print strSQL
        'CurrentDb.Execute strSQL
        SessionCheck = True
   
    Case strLastComputer <> modUserControl.ComputerName
        'User is already logged in on another computer
        FormattedMsgBox "User " & modUserControl.DomainUsername & " is already logged in at workstation " & _
            strLastComputer & "      " & "@User " & modUserControl.DomainUsername & _
            " MUST logout from that computer before logging in again.            @", vbCritical, "Already Logged In"
       
    Case Else
        'Something unaccounted for has happened
        'Message about it
End Select

ExitHandler:
'Since this function is a key protection to unauthorized access, any error should prevent access.
SessionCheck = False
Exit Function

ErrHandler:
Call ErrProcessor(glProcName)
GoTo ExitHandler

End Function

I like it. Good luck with your project.

 

[JMongi]

Thanks! Getting my coding sea legs back under me again.