Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

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

 

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.

Speed Comparison Tests                                                  Page 5 (of 8)

Screenshots

Click any image to view a larger version ...

1 2 3 Return To Top Page 5 of 8 Return to Access Articles

 

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

For these tests, I used the same dataset of approx. 30000 records as in 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

SpeedTests7-Form SpeedTests7-Results SpeedTests7-AvgResults

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)

The code used in each test is as follows:

 

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

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:

5 6

5.    Conditional Updates                                          Updated 27/02/2019

7 8

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

* Required