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
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
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:
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)
Feedback
Please use the contact form below to let me know whether you found this article useful or if you have any questions/comments.
Do let me know if there are any errors
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 27 Feb 2019
Return to Speed Test List
Page 5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Return to Top
|
|
|