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