The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Faster Is Better Than Slower,
At Least For Computers

When working with computers, faster is better than slower. Of course, in some venues slower is better: the cooking technique of braising a tough cut of meat to make it tender, for instance. But when it comes to computers and software, we want things to happen fast. 

This month, I will show you a technique that will help you to time processes so, when you have alternative ways of doing something, you can figure out which one is the fastest.

Timing Things the Hard Way

Let's say you had two alternative processes for some potential routine. For example, you want to choose a customer to win a prize based on the number of orders placed in July 2013 and you have two ways of getting this information: a Lookup and a Function call. How do you tell which would be faster?

If you run it once, it will probably be tough to tell which will give you the answer faster. A surefire way is to use a mechanism that allows you to test each method multiple times, and then compare the total time for each routine. Here is how I would do it.

Sub TestTime()
    Dim dtmBegin As Date
    Dim dtmEnd As Date
    Dim intCycles As Integer
    Dim i As Integer
    Dim strState As String
    intCycles = 500
    dtmBegin = Now
    For i = 1 To intCycles
    Next i
    dtmEnd = Now
    Debug.Print "Method 1 Time; " & CalcTime(dtmBegin, dtmEnd)
    dtmBegin = Now
    For i = 1 To intCycles
    Next i
    dtmEnd = Now
    Debug.Print "Method2: " & CalcTime(dtmBegin, dtmEnd)
End Sub
This function sets up some variables and a number of cycles to run for the test (intCycles). It then runs each of the routines a set number of cycles, in my case 500. It establishes a begin time at the start and an end time when it's finished. It then calls a function, CalcTime (shown below), to display the elapsed time. At the end of each process, it prints out the result. This makes it very easy to see how long each one takes. If you find that the times are identical or close, you can increase the number of cycles.
The CalcTime function is as follows:
Function CalcTime(beginTime As Date, endTime As Date) As Variant
  CalcTime = Format(endTime - beginTime, "HH:MM:SS")
End Function
The CalcTime function formats the difference between the times so that it comes out as as hours, minutes, and seconds. There is nothing terribly tricky or unusual about this function except that it returns a variant rather than a date. By returning a variant, it avoids having AM or a PM show up as part of your result.


In this month’s Wizard, I have shown you a technique to compare the speed of alternate methods of accomplishing some computing task. In many cases, the process that you are doing will not occur frequently enough to go to the trouble of setting up two different ways of accomplishing the same thing and choosing better one. However, if you have a large database or a process that runs many times in your application, you will do both yourself and the folks that use your database a favor by making things go faster. 


Tip of the Month: How to Search on the Asterisk (*) Character
Every once in a while, you may find that you need to search on a special character within a query. For instance, I was faced with finding all records that contained the * symbol from a list of scientific instruments. The * symbol represents a wildcard in the SQL language that Access uses for queries. So if you just try searching for the * symbol, you will get all records that have any character whatsoever.

To get around this problem, use the following syntax:
Like '*[*]*'

By using the brackets, Access will interpret the * symbol within them as a literal character rather than as a wildcard, and return the records that contain *. 

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886