Last Updated 10 May 2023 Difficulty level : Moderate
This is 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
Complete List of Speed Comparison Test Articles
1. Handling Nulls: Trim / Len / Nz
Three different approaches to checking for 'nothing' in a control
2. CurrentDB vs DBEngine(0)(0) vs ThisDb vs CurDb
Comparison of four methods used to reference the current database
3. DoEvents vs DBIdle.RefreshCache vs Sleep
Comparison of three methods used to pause code. The purpose is to give the processor time to complete the previous task before continuing
4. HAVING vs WHERE
Compares the efficiency of using a WHERE clause or a HAVING clause in aggregate queries.
5. Conditional Updates
This compares 5 different ways of doing a conditional update where there are multiple conditions: If/ElseIf/End If ; Select Case ; Nested IIf ; Switch ; Lookup table
6. Query vs SQL vs QueryDef
These tests compare the execution times using SQL statements, saved queries and query definitions and discusses the advantages of each approach
7. Check Record Exists
These tests compare the time required to check the existence of a specified record in a large dataset using 7 different methods.
8. Optimise Queries
This compares the effect of different ways of improving query performance
9. SELECT DISTINCT vs GROUP BY
This compares the efficiency of two methods of grouping data
10. Regex Or Not
This compares the efficiency of two methods of filtering data
11. $ Or No $
This compares the efficiency of two types of function for processing data e.g. Left$ vs Left
12. Finding Unmatched Records
This compares three methods of finding differences in records: The unmatched query created with the wizard compared to two methods using subqueries
13. WHERE OR vs WHERE IN
This article compares the time required to filter a dataset with several filter criteria in the WHERE clause.
Two sets of tests are done - using OR and using IN to filter the data. It also compares the effect of indexing with unexpected results.
14. Loop vs Recursion
This article compares the time required to count the number of gifts in the 12 Days of Christmas song by two different methods.
The tests are then repeated for a 'lifetime' of days.
15. Grouped Aggregate Queries
This article compares the time required to run five different types of grouped aggregate query.
The effect of indexing is also discussed in detail.
Also see these related articles:
a) Timer Comparison Tests
This article compares the accurary and consistency in the times measured using six different methods including those used in the speed comparison tests
b) Show Plan - Run Faster
This article explains how the little documented Jet ShowPlan feature can be used to assist with optimising queries & VBA SQL statements
c) Synchronise Data
This article discusses various ways of synchronising data with external tables. The times are compared as well as the increase in file sizes associated with each method
Feedback Return To Top
Please use the contact form below to let me know whether you found these articles useful or if you have any questions/comments.
Do let me know if you have any suggestions for future articles in this series
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 10 May 2023
Return to Access Articles All Test Pages 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Return to Top