isladogs
MVP / VIP
- Local time
- Today, 19:24
- Joined
- Jan 14, 2017
- Messages
- 18,547
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
d) Switch
e) Lookup table
In that thread I wrote:
My predictions about comparative speed were based on personal experience and articles such as this as Stackoverflow: https://stackoverflow.com/questions/767821/is-else-if-faster-than-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
c) Switch 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
The 5 tests done were:
a) If/ElseIf/End If
b) Select Case
c) Nested IIf
d) Switch
e) Lookup table
In that thread I wrote:
My predictions about comparative speed were based on personal experience and articles such as this as Stackoverflow: https://stackoverflow.com/questions/767821/is-else-if-faster-than-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
c) Switch 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
Attachments
Last edited:
