Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Making Coding Changes to Improve Performance

No matter what you do to optimize the operating system environment and improve your data design, poor code can continue to bog you down. A properly optimized application is optimized in terms of the environment, data design, and code. Just as poor table design can degrade performance, poor coding techniques also can have a dramatic negative effect on performance. Changes to your code include eliminating variants and dead code, using built-in collections, and using specific object types. An important code-related optimization is to deliver your modules precompiled.

The following changes and techniques can aid in the improvement of performance. It is important to recognize that any one change won't make much of a difference. However, an accumulation of all the changes, especially where code is being re-executed in a loop, can make a significant impact on your application's performance.

Eliminate Variants and Use the Smallest Data Type Possible

Variant variables are the slowest for the operating system to process; they carry a lot of overhead because the compiler resolves them at runtime. Remember that this statement declares a variant type of variable:

Dim intCounter

To strong-type this variable as an integer, for example, you must modify your code to look like this:

Dim intCounter As Integer

Not only should you strong-type your variables, but you also should use the smallest data type possible. Remember that data types such as Boolean, Byte, Integer, and Long are the smallest and therefore the fastest to resolve. These are followed by Single, Double, Currency, and (finally) Variant. Of course, if you must store very large numbers with decimal points in a variable, you cannot pick Single. Just keep in mind that it is wise to select the smallest data type appropriate for the use of the variable. Listing 17.1 provides code that illustrates the difference between using a variant and a long integer.

Listing 17.1 Data Type Benchmark Illustrating the Effect of Data Type on Performance

The code, found in the form frmBenchmark in the CHAP17EX.MDB sample database, loops using a variant, and then a long integer. The example displays the amount of time required to execute each loop, along with the percent difference between the two techniques.

Use Specific Object Types

Just as using the General variant data type is inefficient, using generic object variables also is inefficient. This is because the compiler needs to evaluate their type at runtime. The MakeItBold subroutine uses a generic object variable, as shown in Listing 17.2.

Listing 17.2 The MakeItBold Subroutine

NOTE

The code in Listing 17.2 is overly simplified. It contains no error handling. The control passed as Screen.PreviousControl could be any type of control. The type of control received by the MakeItBold routine might not have a FontBold property, in which case an error occurs. It is therefore important for either one or both of these routines to contain proper error handling.

On the other hand, the SpecificBold subroutine uses a specific object variable, as Listing 17.3 shows.

Listing 17.3 The SpecificBold Subroutine

The difference is that the SpecificBold routine expects to receive only text boxes. It does not need to resolve the type of object it receives and therefore is more efficient.

This code is contained in the CHAP17EX.MDB database on the accompanying CD-ROM. You can find the example in frmObjVar.

The best way to truly compare using a specific control versus a generic control is to benchmark the techniques, as shown in Listing 17.4.

Listing 17.4 Object Type Benchmark Compares Using a Specific Control and a Generic Control

The code, found in frmBenchmark, passes a text box to two different routines. The first routine receives any control as a parameter. The second routine receives only text boxes as a parameter. The benchmarks prove that routines that use specific object types take less time and are therefore more efficient.

Use Inline Code

There is a tendency to call out to procedures for everything. This is good from a maintenance standpoint, but not from an efficiency standpoint. Each time VBA calls out to a procedure, it takes additional time to locate and execute the procedure. This is particularly evident when the procedure is called numerous times. The alternative is to use inline code. Executing inline code is more efficient than calling out to procedures, because Access does not need to locate the code. The downside of inline code is that it is more difficult to maintain. You must decide how important maintainability is compared to speed.

Listing 17.5 shows the same code called as a routine and executed inline. The benchmark shows that the inline code executes much more quickly.

Listing 17.5 Inline Code Benchmark

Toggle Booleans Using Not

This code is very inefficient:

If bFlag = True Then
bFlag = False
Else
bFlag = True
End If

You should modify it to look like this:

bFlag = Not bFlag

Besides requiring fewer lines of code, this expression evaluates much more quickly at runtime. Listing 17.6 proves that toggling the Boolean variable is a much more efficient approach to the problem than having to test each condition separately. You can find this code in frmBenchmarks on the CD-ROM accompanying this book.

Listing 17.6 Toggling Boolean Benchmark

Use the Built-In Collections

The built-in collections are there whether or not you use them. By using For Each...Next and a collection of objects, you can write very efficient code, as shown in Listing 17.7.

Listing 17.7 Using For Each...Next

In this example, you use the Forms collection to quickly and efficiently loop through each form, changing the caption on its title bar. The code shown in Listing 17.8 illustrates the use of the Forms collection, as well as an alternative method of accomplishing the same task.

Listing 17.8 For Each...Next Benchmark

Without the For Each..Next loop, you must use a variable to loop through the forms. Notice that the code sets intNumForms equal to the number of forms in the Forms collection minus one. The loop goes from zero to the value stored in intNumForms, changing the caption of the specified form. Although the performance gains realized by using the Forms collection are not dramatic, the Forms collection technique is much simpler to implement.

Use the Len Function

Using the Len function (as shown in Listing 17.9) is more efficient than testing for a zero-length string (as shown in Listing 17.10).

Listing 17.9 Using the Len Function

Listing 17.10 Testing for a Zero-Length String

Listing 17.9 is easier for VBA to evaluate and therefore runs more quickly and efficiently. This is emphasized by the code shown in Listing 17.11 (located in frmBenchmark). The code shows two loops. One uses the Len function and the other does not. The benchmark proves that the routine that uses the Len function executes more quickly.

Listing 17.11 Len Benchmark

Use True and False Instead of 0

This example is similar to the preceding one. It is better to evaluate for True and False (as shown in Listing 17.12) instead of 0 (as shown in Listing 17.13).

Listing 17.12 Evaluating for True and False

Listing 17.13 Evaluating for 0

The code in Listing 17.12 runs more efficiently. The benchmark shown in Listing 17.14 provides an example. The lngSalary variable is evaluated against zero in the top loop. The bottom loop tests lngSalary against True. The second loop runs more quickly.

Listing 17.14 True/False Benchmark

NOTE

Although both the Len function and the use of True/False provided consistently better results in Access 2000 and earlier versions of Access, they appear to provide inconsistent results in Access 2003. This leads me to believe that there might have been changes to how these functions or their alternatives execute in Access 2003. I suggest that you test both the Len function and the use of True/False in your own environments to determine whether they result in improved performance.

Eliminate Unused Dim and Declare Statements

As you modify your subroutines and functions, you often declare a variable and then never use it. Each Dim statement takes up memory, whether or not you are using it. Furthermore, Declare statements, which you use to call external library functions, also take up memory and resources. You should remove these statements if you are not using them.

Eliminate Unused Code

Most programmers experiment with various alternatives for accomplishing a task. This often involves creating numerous test subroutines and functions. The problem is that most people do not remove this code when they are done with it. This dead code is loaded with your application and therefore takes up memory and resources. Several third-party tools are available that can help you find both dead code and variable declarations. One tool that many people use is called Total Access Analyzer, by FMS, Inc. (http://www.fmsinc.com).

Use Variables to Refer to Properties, Controls, and Data Access Objects

If you are going to repeatedly refer to an object, you should declare an object and refer to the object variable rather than the actual control, as shown in Listing 17.15.

Listing 17.15 Declaring an Object and Referring to the Object Variable

This is a very scaled-down example, but if numerous properties are being changed, or if this code is being called recursively, an object variable can make the code more efficient, as Listing 17.16 shows.

Listing 17.16 Using an Object Variable to Make Your Code More Efficient

The benchmark shown in Listing 17.17 contains two loops. The first loop sets four properties of the same control, explicitly referencing the control as each property is set. The second loop uses an object variable to accomplish the same task. The difference in performance between the two loops is somewhat dramatic.

Listing 17.17 Object Variable Benchmark

Use With...End With

Another way to optimize the code in the preceding example is to use a With...End With construct, as shown in Listing 17.18.

Listing 17.18 Using With...End With

The code in Listing 17.19 shows two different loops. The first loop explicitly references the text box four different times to set four different properties. The second loop uses a With statement to reference the same control and set the four properties. The code in the second loop executes much more efficiently.

Listing 17.19 Object Variable Resolution Benchmark

Resolve Variables Outside a Loop

Although both the object variable reference and the With statement significantly improve performance, Listings 17.17 and 17.19 can be further improved by resolving the object variable outside the loop whenever possible. Listing 17.20 provides an example.

Listing 17.20 Resolving the Object Variable Outside the Loop

Notice that the object variable is resolved outside the loop. This loop executes significantly faster than the loops in Listings 17.17 and 17.19.

Use the Me Keyword

The preceding example uses Forms!frmHello.txtHello to refer to a control on the current form. It is more efficient to refer to the control as Me.txtHello because VBA searches only in the local name space. Although this makes your code more efficient, the downside is that the Me keyword only works within form, report, and class modules. It won't work within standard code modules. This means that you cannot include the Me keyword in generic functions that are accessed by all your forms.

Use String Functions When VBA Provides a Variant and String Alternative

Several VBA functions come in two forms :
one with a dollar sign ($) and one without. An example is Left(sName) versus Left$(sName). Whenever it is acceptable to return a string, it is more efficient to use the version with the dollar sign, which return strings rather than variants. When a string variable is returned, VBA doesn't need to perform type conversions.

Use Dynamic Arrays

Array elements take up memory, whether or not you use them. It's therefore sometimes preferable to use dynamic arrays. You can increase the size of a dynamic array as necessary. If you want to reclaim the space used by all the elements of the array, you can use the Erase keyword, as in this example:

Erase aNames

If you want to reclaim some of the space being used by the array without destroying data in the elements you want to retain, use Redim Preserve:

Redim Preserve aNames(5)

This statement sizes the array to six elements (it's zero-based). Data within those six elements is retained.

CAUTION

You must be careful when using dynamic arrays with Redim Preserve. When you resize an array using Redim Preserve, the entire array is copied in memory. If you are running in a low-memory environment, this can mean that virtual disk space is used, which slows performanceor worse than that, the application can fail if both physical and virtual memory are exhausted.

Use Constants When They Are Available

Constants improve both readability and performance. A constant's value is resolved after compilation. The value the constant represents is written to code. A normal variable has to be resolved as the code is running because VBA needs to obtain the current value of the variable.

Use Bookmarks

A bookmark provides you with the most rapid access to a record. If you are planning to return to a record, set a variable equal to that record's bookmark, making it very easy to return to that record at any time. Listing 17.21 shows an example that uses a bookmark.

Listing 17.21 Using a Bookmark

You can find this code in basOptimize of CHAP17EX.MDB. The code stores the bookmark in a variable until the Do...Until loop executes. Then the code sets the recordset's bookmark equal to the value contained within the variable.

Set Object Variables Equal to Nothing

Object variables take up memory and associated resources. Their value should be set to Nothing when you are finished using them. For example:

Set oObj = Nothing

This conserves memory and resources.

Use Action Queries Instead of Looping Through Recordsets

Besides being easier to code, executing a stored query is much more efficient than looping through a recordset, performing some action on each record. Listing 17.22 shows an example that loops through a recordset.

Listing 17.22 Looping Through a Recordset

This code, which is located in basOptimize of CHAP17EX.MDB, loops through a recordset, adding 1 to each project's total estimate. Contrast this with the code in Listing 17.23.

Listing 17.23 Executing a Stored Query

This code uses a command object to execute a stored query called qupdLowerEstimate. The query runs much more efficiently than the Do...Until loop shown in Listing 17.22.

NOTE

The most efficient method to update records is with a stored procedure. You can use stored procedures with a client/server database engine such as Microsoft SQL Server. This is covered in detail in

Alison Balter's Mastering Access 2002 Enterprise Development .

Deliver Your Application with the Modules Compiled

Applications run slower when they are not compiled. Forms and reports load slower, and the application requires more memory. If you deliver your application with all the modules compiled, they do not need to be compiled on the user's machine before they run.

To easily recompile all modules, choose Debug, Compile with the Visual Basic Editor (VBE) active. This command opens and compiles all code in the application, including the code behind forms and reports. It then saves the modules in the compiled state, preserving the compiled state of the application.

Retain the Compiled State

Don't bother choosing the Debug, Compile command if you plan to make additional changes to the application. An application becomes decompiled whenever you modify the application's controls, forms, reports, or modules. Even something as simple as changing a single line of code causes the application to lose its compiled state. It's therefore important to choose the Debug, Compile command immediately before you distribute the application.

CAUTION

Renaming a database file causes the code contained in the database to decompile. It's therefore important to always choose the Compile command after renaming a database.

Distribute Your Application as an MDE

The process of creating an MDE file compiles all modules, removes editable source code, and compacts the destination database. All Visual Basic code will run, but cannot be viewed or edited. This improves performance, reduces the size of the database, and protects your intellectual property. Memory use also is improved. The process of saving an application as an MDE and the implications of doing so are covered in Chapter 27, "Database Security Made Easy," and Chapter 32, "Distributing Your Application."

Organize Your Modules

VBA code theoretically can be placed in any module within your application. The problem is that the compiler does not load a module until you call a function within it. After you call a single procedure in a module, the compiler loads the entire module into memory. Furthermore, if a single variable within a module is used, the compiler loads the entire module into memory. As you might imagine, if you design your application without much thought, every module in your application will be loaded.

If you place similar routines all in one module, that module will be loaded, and others will not. This means that if people are using only part of the functionality of your application, they will never be loading other code modules. This conserves memory and therefore optimizes your application.