Recipe 8.6 Test the Comparative Benefits of Various Optimization Techniques

8.6.1 Problem

Now that you've tried the optimization techniques in this chapter, you'd like to test some additional optimization ideas. How can you test various VBA optimization techniques in a standardized fashion?

8.6.2 Solution

By using a Windows API call, some simple math, and a wrapper function, you can easily compare the performance of two optimization techniques with relatively high accuracy. This solution shows you how to create a form to compare the performance of two functions. It runs the functions and then displays how long each took to execute.

Open and run frmTestOptimize from 08-06.MDB. The form shown in Figure 8-15 allows you to enter the names of two functions and test their performance relative to each other. The 08-06.MDB database contains two sample functions that show the relative performance of integer division and floating-point division. (This optimization was discussed in the Solution in Recipe 8.4.) To run the test, enter:

FloatDivision( )

into the Function 1 text box, and enter:

IntegerDivision( )

into the Function 2 text box. Press the Test button. The form will run each function, show the time taken by each function, and tell you which function is faster and by how much.

Figure 8-15. The Test Optimizations form

To use frmTestOptimize to test your own functions, follow these steps:

8.6.3 Discussion

There are two key aspects to this technique. First, we used the Windows API GetTickCount function. GetTickCount returns the number of milliseconds elapsed since Windows was started. This number is useful when employed to compare two points in time. You may wonder if you can use the Timer function built into Access instead, or even the Now function; however, both of these return time values that are accurate only to within about one tenth of a second, even though they can show numbers that appear to have greater precision. You will lose a great deal of accuracy with these functions. Because GetTickCount returns time measurements in milliseconds, it is more accurate than VBA's Timer or Now functions.

Second, this optimization test technique makes use of the Eval function, which is one of the least understood yet most powerful functions in Access. You can use Eval to execute a function that is named in a variable or some other expression. If you have programmed in a lower-level language such as C or Pascal, you probably miss Basic's absence of pointers to functions. You can use the Eval function to simulate this by passing a function name as a parameter to Eval. This technique calls Eval for both functions you type into the form.

When you are testing optimization techniques, watch out for a couple of things that can yield false results:

• Both Access and Windows use caching algorithms to reduce disk writes. Any tests that access objects from the database must take this into account. For example, if you are testing an optimization on form load time, your results can be erroneous if you perform the comparison of the two methods one after the other. The first time you load the form, Access caches it in memory if possible; the second time, the form invariably loads faster because Access is retrieving it from memory rather than disk. This can skew your test results. There are several ways to get around the effects of caching; probably the simplest is to repeat all tests, reversing the order the second time you perform the test.

• Windows is a multitasking operating system. Because of this, your test results may be further skewed by the fact that Windows may be performing some other operation in the background while one of your tests is running—for example, a word-processing document may be automatically saved in the background in the middle of your test. The best way to minimize this factor is to ensure that no other Windows programs are running when you perform your tests. It is always a good idea to run the test several times and average the results.