لطفا منتظر باشید ...
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:
- Import frmTestOptimize from 08-06.MDB into your
database. This form is completely self-contained and requires no
other objects. - Open frmTestOptimize in form view and enter the name of the two
functions you wish to test along with any required parameters. Type
the entries in the Function 1 and Function 2 text boxes exactly as if
you were calling the functions in your VBA code, but omit the
assignment operator and assignment object. For example, for a
function that is called in your VBA code like this:intReturned = MyTestFunction ("MyTable")type the following into the frmTestOptimize text box:MyTestFunction ("MyTable")
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
runningfor 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.
