Following on from this thread, Help with multiple IIF statements
, I’ve done another speed test to compare the different ways of doing a conditional update where there are multiple conditions
The 5 tests done were:
a) If/ElseIf/End If
b) Select Case
c) Nested IIf
e) Lookup table
In that thread I wrote:
In terms of speed I think multiple If and Select Case are very similar but Switch should be faster.
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 are a nightmare to error check or to edit when changes/additions are required.
My predictions about comparative speed were based on personal experience and articles such as this as Stackoverflow: https://stackoverflow.com/questions/...an-switch-case
I used the same dataset of approx. 30000 records as in my last speed test Having vs Where
In this case, there were 10 different conditions to be tested (numbers 0 to 9) and each test looped through all records 10 times.
I also repeated each test 5 times and calculated averages
My predictions were largely correct
1. The lookup table was the clear winner as expected
2. Select Case was slightly faster than If…ElseIf…End If but the difference was minimal
3. Switch was slower than both of these (which surprised me as the code is more 'streamlined')
4. Nested Iif statements were as expected the slowest
Average results were:
If there are only a couple of choices, I think it makes little difference which method is used.
However whether there are multiple choices, I would reiterate the advice given by myself & others in that 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
is also useful and the code may be very concise
d) Avoid nested IIf
statements which can be very complex to edit and error check
Although done for update SQL statements, I would anticipate the results would be similar for append queries as well
Hope this is useful to others