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
Method1
Next i
dtmEnd = Now
Debug.Print "Method 1 Time; " & CalcTime(dtmBegin, dtmEnd)
dtmBegin = Now
For i = 1 To intCycles
Method2
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.
Conclusion
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.
|
