Click any image to view a larger version



Conditional Updates


Last Updated 27 Feb 2019                                                Difficulty level :   Moderate


This is the fifth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding.

Example databases are provided in each case so the same tests can be done on your own workstations

This article was written in response to a recent thread Help with multiple IIF statements at Access World Forums which included discussion of the different ways of doing a conditional update where there are multiple conditions.

In this example, 5 different methods have been compared for speed/efficiency:
a)   If/ElseIf/End If
b)   Select Case
c)   Nested IIf
d)   Switch
e)   Lookup table

SpeedTests7Form
Before running these tests, I wrote the following comments in the forum thread:
In terms of speed I think multiple If and Select Case are very similar but Switch should be faster.

This was based on both personal experience and articles such as this at Stackoverflow: Is Else If faster than Switch Case?

In the same forum thread I also wrote:
Whilst I agree that using a look up table is often the correct solution, I agree with using Case statements for readability in VBA in preference to multiple Ifs. Multiple nested IIf statements (as in the original post) are a nightmare to error check or to edit when changes/additions are required.

For these tests, I used the same dataset of approx. 30000 records as in the first part of the Having vs Where speed test.

In each test 10 different conditions were checked and each test looped through all the records 10 times. I also repeated each test 5 times and calculated averages



The code used in each test is as follows:

1.   If...Else If ...End If

T=1
Set rst = CurrentDb.OpenRecordset("qryPatientDOB", dbOpenDynaset)

With rst
    For Q = 1 To LC     'loop count
       .MoveLast
       .MoveFirst
       Do Until .EOF
           .Edit
           N = !R1LngDOB     'last digit of DOB converted to long integer

           If N = 0 Then
                !Output = "A" & T
           ElseIf N = 1 Then
               !Output = "B" & T
           ElseIf N = 2 Then
                !Output = "C" & T
           ElseIf N = 3 Then
                !Output = "D" & T
           ElseIf N = 4 Then
                !Output = "E" & T
            ElseIf N = 5 Then
               !Output = "F" & T
           ElseIf N = 6 Then
               !Output = "G" & T
           ElseIf N = 7 Then
               !Output = "H" & T
           ElseIf N = 8 Then
               !Output = "I" & T
           ElseIf N = 9 Then
                !Output = "J" & T
           Else
                    'shouldn't be possible
                !Output = Null
           End If
        .Update
       .MoveNext
       Loop
    Next Q
End With



2.   Select Case

T=2
Set rst = CurrentDb.OpenRecordset("qryPatientDOB", dbOpenDynaset)

With rst
    For Q = 1 To LC     'loop count
       .MoveLast
       .MoveFirst
       Do Until .EOF
           .Edit
           N = !R1LngDOB     'last digit of DOB converted to long integer

           Select Case N
           Case 0
                !Output = "A" & T
           Case 1
               !Output = "B" & T
           Case 2
                !Output = "C" & T
           Case 3
                !Output = "D" & T
           Case 4
                !Output = "E" & T
            Case 5
               !Output = "F" & T
           Case 6
               !Output = "G" & T
           Case 7
               !Output = "H" & T
           Case 8
               !Output = "I" & T
           Case 9
                !Output = "J" & T
           Case Else
                'shouldn't be possible
                !Output = Null
           End Select
        .Update
       .MoveNext
       Loop
    Next Q
End With



3.   Nested IIf

T=3
Set rst = CurrentDb.OpenRecordset("qryPatientDOB", dbOpenDynaset)

With rst
    For Q = 1 To LC     'loop count
       .MoveLast
       .MoveFirst
       Do Until .EOF
           .Edit
           N = !R1LngDOB  'last digit of DOB converted to long integer

           !Output = IIf(N = 0, "A" & T, IIf(N = 1, "B" & T, IIf(N = 2, "C" & T, IIf(N = 3, "D", _
                   IIf(N = 4, "E" & T,  IIf(N = 5, "F" & T, IIf(N = 6, "G" & T, _
                   IIf(N = 7, "H" & T, IIf(N = 8, "I" & T, IIf(N = 9, "J" & T, ""))))))))))

        .Update
       .MoveNext
       Loop
    Next Q
End With

4.   Switch

T=4
Set rst = CurrentDb.OpenRecordset("qryPatientDOB", dbOpenDynaset)

With rst
    For Q = 1 To LC     'loop count
       .MoveLast
       .MoveFirst
       Do Until .EOF
           .Edit
           N = !R1LngDOB     'last digit of DOB converted to long integer
           
           !Output = Switch(N = 0, "A" & T, N = 1, "B" & T, N = 2, "C" & T, N = 3, "D" & T, _
                    N = 4, "E" & T, N = 5, "F" & T, N = 6, "G" & T, N = 7, "H" & T, _
                    N = 8, "I" & T, N = 9, "J" & T)

        .Update
       .MoveNext
       Loop
   Next Q
End With



5.   Lookup table

T=5

For Q = 1 To LC     'loop count
      'update table
       db.Execute "UPDATE tblLookup INNER JOIN qryPatientDOB" & _
           " ON tblLookup.NumberValue = qryPatientDOB.R1LngDOB" & _
           " SET qryPatientDOB.[Output] = [tblLookup].[Output] & " & T & ";"
Next Q



The test results were:

SpeedTests7Results
SpeedTests7AvgResults
In this case, my predictions were largely correct:

The lookup table method was the clear winner as expected
Select Case was slightly faster than If…ElseIf…End If but the difference was minimal
Switch was slower than both of these (which surprised me)
Nested IIf statements were as expected the slowest method of all

Although done for update SQL statements, I would anticipate the results would be similar for append queries as well.



Conclusions

If there are only a couple of choices, I think it makes little difference which method is used.

However whether there are many choices, I would reiterate the advice given by myself & others in the forum thread:

a)   Use a lookup table if possible – its not only fastest but easiest to edit if circumstances change
b)   For readability, use Select Case in preference to If … ElseIf…End If
c)   Switch is also useful and the code may be very concise
d)   Avoid nested IIf statements which can be very complex to edit or error check


Click to download:     Speed Tests - Conditional Updates v7.2       Approx 2.6 MB (zipped)



Colin Riddington           Mendip Data Systems                 Last Updated 27 Feb 2019



Return to Access Articles Page Return to Top Page 5 of 11 1 2 3 4 5 6 7 8 9 10 11