Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 11-02-2018, 03:18 PM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,360
Thanks: 115
Thanked 3,110 Times in 2,828 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Another Mythbuster - Speed Tests - Conditional Updates

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:
Quote:
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
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
Attached Images
File Type: png SpeedTests7-AvgResults.PNG (15.4 KB, 147 views)
Attached Files
File Type: zip SpeedComparisonTest_v7_ConditionalUpdate.zip (599.1 KB, 73 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 09-02-2019 at 05:33 AM.
isladogs is offline  
The Following 2 Users Say Thank You to isladogs For This Useful Post:
Galaxiom (11-03-2018), Gasman (11-03-2018)
Closed Thread

Tags
conditional updates , if/case/switch/iif , speed tests

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mythbusters - Speed Comparison Tests - Having vs Where isladogs Code Repository 16 11-04-2018 07:27 AM
Determine OS from VBA - a few tests needed spikepl Modules & VBA 11 06-09-2018 02:14 PM
Tests Uncle Gizmo General 8 05-23-2006 11:30 AM
Continuous Forms & Logical Tests Luddite Lad Forms 3 10-05-2005 06:36 PM
[SOLVED] calculations and tests in data entry forms Orby Forms 2 02-23-2005 11:16 PM




All times are GMT -8. The time now is 05:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World