Visual Basic Expert SolutionsChapter 21Optimizing VB CodeBy Steve Potts |
For your programs to be successful, they must execute quickly enough to be acceptable to the user. As Visual Basic programs become larger and more sophisticated, the amount of memory that they use and the speed with which they execute becomes extremely important. The goal of this chapter is to highlight features and practices in Visual Basic that make wise use of resources, and therefore perform well.
Programs that are unnecessarily large generally perform poorly. This happens because all of the code and data can't fit in memory, so Windows must use the hard disk to store part of the program during execution. This means that whenever your program needs a part of the application that is on the disk, Windows must perform an I/O operation and load in this part from disk into memory. The larger the program, the bigger this problem gets.
If your program is really large compared to the available memory on a customer's machine, it will spend most of its time accessing the disk, and very little time running your program. When this happens, the computer is said to be thrashing. If you hear your hard drive running after every command, your program is causing your computer to thrash. The antidote to this poison is to shrink your program to a size that fits better in the memory available in the minimum hardware that you intend to support.
Another reason for poor performance is the unwise use of Visual Basic features on your form and in your code. Like every complex application, Visual Basic allows the developer alternative ways of accomplishing the same objective. Both approaches look identical to the user, but they cause Visual Basic to behave much differently. Labels often look like Text boxes and Images look like Picture boxes. Being wise when making these choices is a key to achieving good performance.
This chapter addresses the topics of program size and speed. It presents rules of thumb for using certain techniques. Generally, the key to good performance is a strict adherence to the best programming practices at all stages of development. By following this strategy, you will be assured that the finished application will perform well on the widest possible range of computers, and therefore be marketable to the most potential customers.
The major topics of this chapter are as follows:
Programs that perform poorly sell poorly and frustrate the users. This results in excessive hardware expenditures and reduced productivity.
To improve performance, we must be able to measure it. In the programming field, this means measuring how much memory is being used by a certain feature or practice, and how much time something takes to execute.
Many of the practices that we will discuss in this chapter will involve simple reductions in these two metrics. Others will involve trade-offs between size and speed. These trade-offs require good measurements so that you can make wise choices.
The first metric that we will need is a measurement of the speed of a program. This is done using a subroutine based on the use of the Time function. This subroutine is shown in Listing 21.1.
Listing 21.1 TIMEDIFF.BAS Creating a Time-Measuring Subroutine
This listing accepts two Time variables of type Date as input, the earlier one first. It calculates and displays the number of seconds between the two times. The DatePart() function parses the input and returns the seconds and minutes components of the argument based on the "s" (seconds) or "n" (minutes) value in the first parameter.
Listing 21.2 is an example that shows how to use this subroutine.
Listing 21.2 HOWLONG.BAS Using the TimeDiff() Subroutine
This simple program used the Time built-in function to query the system clock. The first query is stored in the variable Time1. After a long While loop executes, a second time measure is taken and stored in Time2. Then the program calls the TimeDiff subroutine, which displays the elapsed time. The output of this program is a message box that displays the following phrase:
All of the measurements in this chapter were made on a homemade 486DX2/66 computer with 8 MB of RAM. The times that you measure on your own computer may vary.
Another technique for measuring performance is the use of an external stopwatch. This works well when you are measuring the efficiency of an operation that takes several minutes or hours.
The second metric that we will need in our programs is program size. One way that we can measure this is by examining the file size after an executable is created using file manager. It is not difficult to see the difference between these two lines:
Although execution speed depends on the locality of code as much as raw size, a decrease in the size of the executable file is normally beneficial.
Another metric that we need is memory usage on running programs. Again, we can use a subroutine to measure that for us. We need a program that displays the current amount of available memory, but that doesn't consume more than a little memory itself. We can do this by invoking the Clipboard viewer that comes with Windows, and then sending the Alt+H and A keystrokes to it (this is the About selection on the Help menu). Listing 21.3 shows the routine that does this.
Listing 21.3 A Memory Display Program
The shell command loads the MSInfo application in memory, as shown in figure 21.1.
Fig. 21.1 The Msinfo32 applet can be used to display the available memory.
We now have the following four options for measuring programs:
Because Visual Basic performs so much work for you through built-in objects, it's sometimes difficult to understand the performance impact of certain features and practices. The only way to determine the best approach in a given situation is to run tests. In this section, we will set up a number of tests on alternate ways of using objects and properties in order to discover some rules of thumb.
Multiform applications are composed of a number of forms that are displayed to the user and removed from view when appropriate, according to the logic of the program. There are basically two ways to make a form invisible to the user:
Because these two approaches are functionally equivalent, we need to determine which one to use in a given situation. It should be obvious that the Unload method conserves memory, and that the Hide method will execute faster. The question is how much more memory is used by the Hide method, and how much slower is the Unload method. We can run a test to find out the answers to these questions.
A proper test will be one that performs a series of Show and Hide combinations on a set of fairly complicated forms like the one shown in figure 21.2.
This form is a sort of jumble of bitmaps, metafiles, and controls that were added to make it large enough to measure. This form was duplicated four times in order to create an application that had five fairly large forms to display. Next, the code in Listing 21.4 was added to Form1.
Listing 21.4 SHOWHID1.BAS Hiding Forms
After creating an executable file from this project, it ran with an 11-second time lapse between Time1 and Time2.
Next, we need to change the program to use the Unload command instead of the Hide command and run it again. The code to do this is provided in Listing 21.5.
Listing 21.5 SHOWHID2.BAS Unloading Forms
Because Form1 contains this code, it will be left in memory the entire time. The Unload statements completely remove the forms from memory and recover (in theory) all of the memory that was allocated for it. The execution time for this program was 18 seconds.
By comparing the two times, we learn that using Unload instead of Hide required about seven additional seconds but consumed 127 KB less memory.
The conclusion that we can draw from this experiment is that there are advantages to each strategy. Hiding forms allows your application to execute quickly, but requires more memory. Unloading a form after it has been used significantly reduces the program's memory requirements, but is slower.
Note: Use the Hide method whenever execution speed is the highest priority. Use the Unload subroutine whenever memory conservation is the higher priority.
A hybrid approach is also possible. This approach would unload the less common forms and hide those that tend to be used more often.
Often, it is just as important to give the user visual feedback as it is to give her rapid response. The use of an introductory or logo form and a timer can help you accomplish this. A problem can arise whenever a long Form_Load event procedure is employed in an application. Programs that make heavy use of list boxes that are filled by accessing the database fall in this category.
The form doesn't appear until the entire Form_Load event procedure is completed. This can cause your user to wonder if the computer is hung up, especially if the delay is for more than a few seconds. You could issue the Show method at the top of the Form_Load procedure and show the window frame, but the rest of the controls won't be painted until the procedure finishes.
A partial solution is to create a logo screen that becomes the Startup form for the application. If, however, you issue the Show method for the main form directly as in the following listing, this logo form doesn't paint immediately either.
One way around this problem is to create the logo form and place a timer on it. You can enable the timer in the Form_Load event for that form. The timer event procedure can issue the Show method for the second form without making the logo form wait.
This immediate feedback will allow your users to remain calm while the main form is loading.
Forms and Picture boxes have a property called AutoRedraw. When AutoRedraw is set to True, all graphics on the object are saved in an area of memory called the canvas. The size of this canvas is determined by the size of the area it represents. The advantage of this canvas becomes apparent when the window is covered and then uncovered by another window. The window's graphics repaint automatically only if AutoRedraw is set to True. These graphics that reappear automatically are said to be "persistent."
Windows 95 takes care of redisplaying the window and controls, but your application must take care of redisplaying graphics in a form or picture box. The easiest way to do this is by setting the AutoRedraw property to True.
A negative feature of the AutoRedraw canvas is that it consumes memory. The size of the canvas is determined by the size of the object that is being redrawn. If your form has no graphics objects on it, always set the AutoRedraw property to False.
Note: Always set the AutoRedraw property of a form to False if it contains no graphical objects.
You may want to set AutoRedraw to False even on a Form that has a graphic object on it. If the Form's graphics are localized around a part of the form, it is often better to create a Picture Box control, draw the graphics on it, and set its AutoRedraw property to True. This enables you to set the AutoRedraw property of the Form to False without losing any graphics if it is covered and then uncovered. Since the Picture Box is only a fraction of the size of the whole form, the memory savings can be substantial.
Note: If a form has graphical objects on it, try to localize them into a Picture Box control that has AutoRedraw set to True. Then set the AutoRedraw property for the form to False.
If the graphics objects are really simple, you can employ the Paint event to redraw them. The Paint event is triggered by Windows whenever a part of the form has been covered up and then exposed. You can set all AutoRedraw properties to False and include the code that draws these objects from scratch in the Paint event procedure. This saves all of the memory that would normally be used for the canvas.
Because Windows has a graphical user interface, the performance of graphics processing methods is critical to overall application performance. In this section, we will look at several areas where design decisions can have a large impact on the performance of the application as a whole. In this section, we will look at a number of design decisions and their performance impact.
Whenever you want to include pictures or images to your forms, you have the following decisions to make:
We will examine each issue separately.
There are two controls in the toolbox that are capable of displaying pictures on your forms: the Picture Box control and the Image control. Selecting the correct one to use can save memory and speed execution.
The Picture Box control is really a little window that is placed inside another window. It consumes resources just like another form does. As such, you can use a Picture Box control to group OptionButtons and to display output from graphics methods. You can also display text written with the Print method. The Picture Box control is the only standard Visual Basic control that can be placed in the client area of a MDI form. It can be used to group controls in order to create a Toolbar. This control can also act as a destination link in a DDE conversation. If you want to accomplish one of these tasks, you will have to use the Picture Box control.
If, however, you only want to display a picture on a form, and you are not interested in the advanced functionality available in the Picture Box control, you can use the Image control. The Image control is a light resource consumer compared to the Picture Box control. We can run a test to see how much resource each of them consumes. Figure 21.3 shows a form with a picture on it. We will create two applications that each have eight of these pictures in them.
Fig. 21.3 This form contains numerous controls to be measured by the following program.
This test compares an executable that was created with no controls with one that has eight pictures, and to one that has eight images. We can look at the size of the executable file that is produced by Visual Basic for each of these cases in Table 21.3.
Table 21.3
Table 21.3 Sizes of Executable Files
Program Size (Bytes)
Blank Form 5440
Eight Images 121,792
Eight Pictures 121,952
The size of the executable file doesn't show much difference in the size of the executables based on the use of the Image control. When the applications are run, however, we see that the size of the programs differ significantly. Table 21.4 shows the results of this test as compared to the system with a program that displays a blank form running.
Table 21.4
Table 21.4 Comparing Sizes of Executable Files
Program Free Memory(Kbytes) Difference(Kbytes)
Blank Form 21,532 —
Eight Images 21,419 113
Eight Pictures 21,361 171
In summary, it took 113Kb of memory to add the eight Image controls (about 14Kb per control) to the blank form. It took 171Kb of memory to add the eight Picture Box controls, or about 21Kb per control. Therefore, we conclude that a Picture Box control consumes 50 percent more resources than an Image control.
Note: Use Image controls to display simple bitmaps and metafiles instead of Picture Box controls, unless you need the additional functionality provided by the Picture Box control.
In conclusion, use a Picture Box control to group option buttons, to display output from graphics methods, to display text written with the Print method, or to group controls to create a Toolbar. Use the Image control to do all simple picture displays.
The next decision that you must make is whether you will load graphics using the LoadPicture function or embed these picture in the form itself. Once again, this is a question of execution speed verses program size—loading is slower, but produces smaller executables. We can run a test to see how much larger and faster an application that stores pictures becomes. Start by creating an application that contains four metafiles in Image controls on two virtually identical forms. Use the metafile called c:\vb\metafile\business\apptbook.wmf that is pictured in figure 21.3.
Create an executable file called storpic1.exe, and run it from the Windows 95 Run command on the menu. The code for this application will look like Listing 21.6.
Listing 21.6 STORPIC1.BAS Showing and Hiding a Form that Contains a Metafile
Next, create a similar application that uses the LoadPicture method to load the metafile into the Image controls during the Form_Load event procedure using the Listing 21.7 code.
Listing 21.7 LOADPIC1.BAS Preloading a Metafile
Call this form Form1. The reason that we assign null strings to the Images in the Unload event procedure is to recover the memory that was used to store the pictures. Create another form called Form2 that has identical code for the Load and Unload events, but the following code for the command button:
Call this application loadpic1.exe. Both of these programs display a form with four pictures and a command button on it. When the command button is pressed, the second form is displayed and it also has four pictures and a command button. Pressing the command buttons toggles between the two forms; functionally, they are identical. However, they are very different internally. The storpic1 program stores all eight metafiles as pictures in the application. The loadpic1 application loads the four pictures for each form during Form_Load, and then unloads them during Form_Unload. It also unloads the forms instead of hiding them.
The results are shown in Table 21.5.
Table 21.5
Table 21.5 Executable Size on Disk
Executable Name Disk File Size
loadpic 1 10,432 Bytes
storpic 1 124,352 Bytes
As you can see, storing the pictures takes up drastically more space on disk. The size of the programs during execution is also larger as shown in Table 21.6.
Table 21.6
Table 21.6 Executable Size at Runtime
Executable Name Runtime Size
loadpic 233,000 Bytes
storpics 299,000 Bytes
The reason that this size difference is not as great at runtime is because when the size was measured, one of the forms was loaded with four pictures.
If you examine the code, you will see that the code needed to load and unload the pictures is far more complex that that needed to process the stored picture application. In addition to that, when you distribute the application, you must also distribute the file containing the picture. Then if your user inadvertently deletes the picture file, your application won't run and the user must call for support. To make matters worse, the loading and unloading of pictures takes time and therefore slows down your application. To determine how much slower loading and unloading makes your program, we need to run a test.
Listing 21.8 SHOWUNL1.BAS Showing and Hiding a Form
We changed the Command1_Click to Load and Unload the forms three times each. This was done in order to get a large enough elapsed time to measure. Next, change the storpic1 application to contain the code in Listing 21.9.
Listing 21.9 SHOWHID1.BAS Showing and Hiding the Form
This code uses the Show and Hide methods to accomplish the same purpose. Table 21.7 shows the times measured by the two programs.
Table 21.7
Table 21.7 The Execution Times
Program Elapsed Time
ShowHid1 1 Second
ShowUnl1 9 Seconds
What a difference in execution speed! So now you don't like loading pictures because it takes too long, and you don't like storing them because they take up so much room. You are like the customer in the bakery that was too hard to please. One day his bread was raw and he complained. The next day it was burned, and he complained again. The baker protested that some people are impossible to please.
There is another approach that borrows the best from each of these two approaches. It involves creating a form that is never displayed but stores all of the pictures that are needed in Image controls on all forms. This enables a simple assignment of the Picture property value instead of a Form_Load. Here is how it works. Create a new form called Form3. Add one image control to this form. Change the Load event procedures in the other two forms to the following:
This code performs an assignment of the Picture property value of Form3!Image1 to the other Image controls in the application. The effect on the size of the application is outstanding (only 231 KB). This is because only one copy of the picture is in memory and all of the Image controls use it to display. The size of the executable grew to 26,208 bytes, which is only slightly larger than the loadpic1 application. The concern over the inadvertent deletion of the picture file is gone, however, because a copy of the picture is stored in the executable file as part of Form3. The time required to execute the six form loads is reduced to around seven seconds. Thus, the dummy form approach gives a reasonably sized executable file somewhat better performance than the loadpic1 application and a better distribution model. These improvements are based on the fact that the same picture is used in several places in the application. This is often the case in applications that use cell animation or buttons with pictures on them.
In conclusion, load pictures to reduce memory usage, store them in the application to maximize performance, and use a dummy form when the same picture is used in several places in the same application. Table 21.8 lists the different performance characteristics.
Note: Stored pictures provide the fastest execution. Loaded pictures require the least memory and disk space. Performance can be improved by using dummy forms to store pictures that are used in multiple places in the application.
Table 21.8
Table 21.8 Summary of Results from Test Programs
Program Size on Disk Size in Memory Time to Run
ShowUnl1 10,432 233KB 9 Seconds
ShowHid1 124,352 299KB 1 Second
Dummy Form 26,208 213KB 7 Seconds
In tuning performance, it's important to wisely choose techniques to use when adding pictures to your Visual Basic applications. By choosing the correct approach, you can optimize on size, speed, or balance the two.
Now that you are more familiar with the performance characteristics of built-in objects, you are ready for a discussion of coding techniques and their effects. If you write a program to balance a checkbook that takes too long to calculate the results, your users won't be happy. In this section, you will learn how to avoid this problem.
To appreciate the following discussion, you must understand how memory is managed in Visual Basic 4.
In Visual Basic 4, the following memory allocations are available to your program:
You must consider these limitations when writing your applications, or you will need a very good runtime error-handler in your code. Don't forget that it's much better to build a guard rail on the edge of a cliff that to park an ambulance at the bottom of it.
One of the great features of Visual Basic is the Variant data type. These magic variables handle overflow without a hitch, perform data conversions, and allow all kinds of flexible programming practices. Whenever you are testing out concepts and creating prototypes, use them wherever possible.
When creating production systems, use the Variant data type sparingly, or not at all. Because Variant is the default data type, avoid using variables that are not declared explicitly.
To help you follow the practice of including a declaration for every variable, include the phrase "Option Explicit" in each module that you create. When you choose Tools, Options, and check the Require Variable Declaration option in the Environment tab, then this phrase will appear automatically whenever you create a new module. This option gives an error message for each undeclared variable.
Run the following example to see that this is important. Create a new project and place one button on the form. In the first part of the experiment, place the Listing 21.10 code in the command button's event procedure.
Listing 21.10 VAR1.BAS Using the Variant Data Type
In this example, the variables i, j, and k are undeclared and—by default—of type Variant. When run, this program took 39 seconds to complete. If, however, you add the following declarations to the example, the Variant data type is overridden by the Integer and Long statements.
Running the application again, it completes in 17 seconds. The only difference was the explicit datatyping of the variables instead of accepting the default Variant type.
Note: Avoid using the Variant data type in production systems. It is simply too slow for large systems.
Each numeric data type is listed here in order, from the fastest to the slowest:
There are two reasons you should avoid using fixed-length strings: they are slow, and they use precious stack memory. To demonstrate how slow they are, we can run the Listing 21.11 code attached to a form with only one command button.
Listing 21.11 FIXLEN.BAS Fixed-Length Strings
This code executed in a staggering 39 seconds. Next, we can run a program that uses variable-length strings instead. The code in Listing 21.12 uses these.
Listing 21.12 VARSTR1.BAS Variable-Length String
Amazingly, this program ran in less than 10 seconds. The only explanation is the fact that stack-processing normally looks at certain memory locations to learn where the top of the stack is located. Variable-length strings follow a pointer to the exact beginning of the string in a separate data segment. This would lead one to expect that the variable-length string would process more quickly, but the magnitude of the difference is surprising.
In addition to this, putting strings on the stack is flirting with danger, because it's difficult to predict the exact amount of stack space that is will be needed in production.
Note: Always use variable-length strings instead of fixed-length strings to store your textual data. Variable-length strings are handled more efficiently by Visual Basic.
Given this difference in processing times, it is difficult to envision a situation where a fixed-length string would be preferable.
The performance problems that we encountered with fixed-length strings raise doubt about fixed-length arrays as well. Fixed-length arrays are those that are declared with Global or Dim. Memory for them is allocated at that time in a data segment that is given to that array individually.
Variable-length (or dynamic) arrays are those whose memory is allocated in a subroutine or function body using the ReDim statement. The ReDim statement allows the same array to change sizes during the execution of the program. One advantage of the variable-length array is that memory for it is allocated at the last possible instant, and by consequence, not allocated when not needed. Additionally, when the procedure ends, the memory allocated to the array is recovered automatically.
Because the size advantage clearly goes to the variable-length array strategy, we only need to test the relative speed of the two approaches.
Listing 21.13 CHKSPD1.BAS Measuring String-Processing Speed of Fixed-Length Arrays
This code executed in 11 seconds. Now, modify the code to use a variable-length array instead, as shown in Listing 21.14.
Listing 21.14 CHKSPD2.BAS Measuring String-Processing Speed of Variable-Length Arrays
This code also completed in 11 seconds. Therefore, we conclude that there is a memory size advantage to using variable-length arrays that does not exact any execution-time penalty.
Note: Applications that use variable-length arrays run at the same speed as those that use fixed-length arrays, but require less memory.
One important use of the dynamic array is to create Visual Basic built-in objects at runtime. The following code creates five forms and allocates memory for them at runtime.
No memory is allocated to hold these forms until the ReDim statement is executed. Thus, if you had three sets of five forms that were allocated in three different subroutines, your application would only allocate memory for five of them at a time, thereby reducing the running size of the application by the size of 10 forms in memory.
Whenever you run Visual Basic, or any other Windows application, you are making extensive use of Dynamic Link Libraries (DLLs). Windows is built upon three of the libraries:
All of the routines contained in these libraries are available to you as a Visual Basic developer. Most of the time, developers call the Windows DLLs to perform some task that is difficult or impossible to do directly in Visual Basic code. Occasionally, you may bypass an equivalent function in Visual Basic in favor of a faster function in a Windows DLL.
Calling DLL routines is not complicated, but it is more involved than simple Visual Basic function calls. First, you must declare the Function that you want to call in the general section of your module. Then you use the function just like any other function in Visual Basic. Listing 21.15 shows an example program that calls a DLL routine.
Listing 21.15 DLL1.BAS Calling DLLs
The first line on this example is a declaration of the function. This tells Visual Basic what the function call will look like and where to find it. The phrase Lib "user" tells Visual Basic that the routine is in the DLL called user, which is distributed with every copy of Windows.
The word ByVal indicates that a parameter is being passed by value. The statement that begins with Const is defining one of the standard constants in Windows. It will be used later in the code.
The purpose of this routine is to learn the number of lines that exist in a text box on the screen. The line that reads text1.SetFocus sets the focus on the text box that we want to examine. By setting the focus and then issuing the command hWd% = GetFocus(), we get the handle (hWd%) of that object. An object's handle is how it is known to Windows. It is kind of like a social security number, which is how a person is known to government computers. With the handle stored in hWd%, we can find the answer to the question about how many lines are in the text box by executing the following line:
This stores the information in a label that is displayed on the form.
Creating DLLs for yourself is a strategy that some developers use. The actual creation of these libraries is beyond the scope of this book, but we can describe the process in general. Using a C/C++ compiler, you can write routines in C/C++ that normally make Windows Application Programming Interface (API) calls. These routines are then compiled and linked into a special executable library with the suffix .DLL. All of the commonly available compilers support the creation of these libraries. (They can be created using other languages also, but C/C++ is the most common way).
You store this .DLL file in the Windows system directory. When you Declare a function that comes from that library, Windows automatically loads it into memory for you. When you are through with it, Windows automatically unloads it.
The advantage of doing all of this work is simple: speed. Certain kinds of algorithms run much faster if written in C/C++ than they do in Visual Basic. This is because C and C++ are languages that are truly compiled into machine instructions. Additionally, C/C++ designers had performance foremost in their minds when creating the language, whereas the Visual Basic designers were more concerned with ease of use.
Note: Consider coding complex and CPU-intensive routines in C/C++ and making them into a Dynamic Link Library. Call these DLL routines from within Visual Basic.
Many developers confuse Label controls and Text Box controls. While both of them can be used to display text on a screen, a Text Box control also allows multi-line displays and text entry. As a result, it is much larger than a Label control. To demonstrate this, we created an application that contained 32 Text Box controls. When loaded in memory, it occupied 250KB. Then we created a form that contained 32 Label controls. It only occupied 218KB of memory. Thus we can conclude that a Text Box control is 1024 bytes larger than a Label control.
In terms of performance, this means that anytime you use a Text Box control where a Label control would have worked, you wasted 1014 bytes of precious memory. As always, this is of little concern in trivial applications, but of real concern in production systems.
Note: Use Labels instead of Text Boxes if possible. Use Text Boxes only when you need the additional functionality.
The performance of the database is critical to the performance of applications that use them. In fact, many applications use the database so heavily that database performance overwhelms all other performance considerations when optimizing the application. Databases introduce considerably more overhead than simple files because they deal with so many problems that files ignore. Among other things, databases are expected to coordinate access by several users at the same time, protect itself from corruption when a system failure occurs, and verify data as it is being entered. Databases are also supposed to operate properly after a new field has been inserted into the schema.
None of these challenges are insurmountable. The number of successful database programs on the market prove that it is achievable. They achieve these goals by adding special code for each new feature. This increases the load on the system and provides performance challenges to you, as an application developer. The database vendors have provided special features that speed up the operation of their products, but most of them work only if you tell them to.
The most basic and most important performance improvement that can be made is in the indexes that you put on the tables in the database. Indexes are special data files that contain the approximate locations of important data. In Visual Basic, these indexes are normally created using the Data Manager add-in.
To be effective, indexes must be created for the fields that will be used to access the data. For example, an index on the zip code field will not help you if you are searching for everyone who drives a Honda. You instead need to create an index on the make of the car to speed this query.
At times, designers are tempted to index every field. While this certainly would speed queries, it slows down database updates because so many indexes must be updated. In addition, all those indexes take up a lot of disk space. Realistically, you should only create indexes on fields that you know you will use to access data.
Note: Create indexes only on those fields that will be used often to access rows in the database. Otherwise, you will be introducing overhead to the updating process for no benefit.
Some fields, such as gender, may be used quite often as criteria for a query but are not of any value in speeding things up. If half of your rows qualify for membership in the index, it is probably just as fast to process the data by primary key as it is with a special index.
To obtain good performance from a client/server database, it is critical to use set level logic in your program. For example, it is possible to access your data one row at the time and test for a certain condition in your Visual Basic code. If the row passes the test, you display it. If it fails the test, you ignore it. This is a poor way to design an application. By sending a request for one row at the time, you are preventing the query optimization code in the database management system from working its magic. If the database is physically located on a server on the network, then you will be flooding the wires with data that you don't want. It is far better to include code like the following:
By so doing, the database engine will be free to send you only those rows which contain a Department field that equals 4812. None of the other rows will be sent.
Note: Use set-level commands wherever possible. Avoid processing one row at the time because it is a very inefficient way to process data in a client/server environment.
In this chapter, you were introduced to concepts and strategies that help speed up the execution and shrink the size of your application at runtime. You learned how to measure both the speed of an application and its size.
In the section on the performance of objects, you saw examples of how to manage multiple forms in the same application. You also learned to manage graphics, particularly in the form of pictures. You also learned when to use Label controls and when to employ Text Box controls instead.
Then you learned how to efficiently allocate variables, strings, and arrays. Then you were introduced to the concept of Dynamic Link Libraries. Finally, you were introduced to the topic of database access. The importance of indexes and set-level processing was discussed in some detail.
| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |
| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |
To order books from QUE, call us at 800-716-0044 or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.