Another Mythbuster - Speed Tests - Conditional Updates (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 11:14
Joined
Jan 14, 2017
Messages
18,186
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:
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/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

  • SpeedComparisonTest_v7_ConditionalUpdate.zip
    599.1 KB · Views: 538
  • SpeedTests7-AvgResults.PNG
    SpeedTests7-AvgResults.PNG
    15.4 KB · Views: 645
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom