There are three ways to do almost anything in FoxPro, but which is the most efficient? This session will examine techniques for optimizing overall application performance through benchmark and volume testing, code coverage analysis, and will discuss alternatives such as distribution of processing and interface techniques to improve perceived performance.
FoxPro programmers often get themselves into trouble when they assume that a system they are developing will run fine on a client's system just as it does on their development machine. Developers are often astounded when an application runs at a snail's pace on a client's 486/66, 16 Mb RAM machine when it ran like greased lightening on their Sextium-266 machine with a gig of RAM.
Issues also come up when the client tries to run the end-of-quarter closing, processing a million record file, when the developer tested the routine with the 12 records he was willing to type in.
In this document, I'll try to cover some of the issues you can be thinking about during the development cycle to ensure that your code will run at an adequate speed when and where it counts - in front of the client. We'll talk about how to identify slow processes, and more closely examine them to determine possible causes and cures.
The issues of tuning data-based query and processing power to take advantage of Rushmore optimization has been covered many times in conferences as well as in many books and magazines, so I will only give it cursory coverage here. If there is any doubt in this area, review the Developer's Guide, Chapter 18, for guidance.
Always, always, always (any questions?) create an index on DELETED()
Whenever a table is read using a SQL SELECT or FOR clause, Rushmore attempts to determine the records needed in the result set purely from reading the much smaller .CDX file. However, if SET DELETED is ON (typical of many applications, making DELETED records "invisible"), the final result set must be generated by reading every single record, to determine if the deleted flag (first byte of each record) is set. This step can be eliminated completely if an index tag is created on the DELETED() status, as in:
Rushmore attempts to determine the optimal way to retrieve records specified in a SQL statement or FOR clause by reading the arguments on the left side of the WHERE or FOR clause and trying to match them exactly to the arguments used to create index tags.
TIP: Rushmore cannot use "conditional" index expressions which use the FOR clause, nor will it use index expressions containing NOT. Avoid these if possible. If you must create these indexes, consider creating a second, "plain" index, for use with Rushmore.
SYS(3054) is a new function exposed in Visual FoxPro 5.0. Calling SYS(3054,1) will cause information on Rushmore's use of indexes for joins to be displayed to the screen. SYS(3054,11) will display similar information on filtering criteria. Also, look at SYS(3050) for memory mgmt:
February 1997's edition of FoxTalk had an excellent article by Flavio Almeida and Walter Loughney, "Set Turbo On: How Visual FoxPro Memory Usage Affects Performance, " that gives a great explanation of how setting foreground and background memory limits on FoxPro can significantly affect performance.
I once had the difficult task of speeding up an enormous Foxbase application. It was a massive and abstract piece of work with everything - the forms, the menus, and even the validation - driven from tables (shades of the power tools!). The problem was that it was slow! Watching a form draw on the screen, component by component, was painful! The solution required a few days R&D, very little code, and the insertion of one function call into numerous points in the system.
The results were dramatic. Forms snapped into view. Menu selection was crisp and responsive. Upper management was pleased with the results, and data entry personnel estimated the system was four or five times faster.
How was this phenomenal speed-up accomplished? No processing code was improved. No Rushmore optimizations were added. In fact, the system ran just as slowly as it had run all along. The only change was that the Foxbase equivalent of LockScreen - SAVE SCREEN TO and RESTORE SCREEN FROM - was used to simulate a faster response. In the amount of time it took for the users to recognize the screen, determine what they were supposed to do and press the appropriate keys, the form had completed drawing and was ready to continue processing.
- We have all done it - clicked on a button to get some task started, and have nothing happen. Click again, maybe the computer didn't hear you. Again and again. Just when you're ready to reboot, the process fires - four times. Don't aggravate the customer - be responsive.
Set THISFORM.LockScreen True before updating or refreshing the form, then LockScreen False when all changes are completed. FoxPro repaints the screen once instead of many times, actually speeding up the application. This also provides a psychological speed-up, and the operator does not see each control refreshing, then another, then another…
Most ActiveX controls are not aware and do not respond to LockScreen properties - they are, in effect, their own Windows applications, with their own responsibilities to update themselves. Some ActiveX controls, such as the TreeView, can be tricked into quicker response by locking the screen (with LockScreen, above), moving them off-screen (to 10000,10000, perhaps), updating the controls, moving them back on-screen and then unlocking the screen. Thanks to Ken Levy for this tip.
In some cases, developers will have written the best code they can, and performance will not be acceptable. Instead of tearing into the code and rewriting large portions of it in an attempt to improve performance, alternatives outside of coding should be considered. If the application is used in only a few machines, or a hardware fix can be applied to the server alone, the cost of the upgrade can be significantly less than the cost of the developers time. In addition, an overall throughput improvement will benefit not only this application, but also all other work done on the machines.
Win32 applications use virtual memory based on available disk space. More, defragmented space can speed applications.
Memory chips are rated in nanoseconds, while disk access is in milliseconds, a million times slower. If your application is chugging away using disk swap space, additional RAM can make a dramatic difference.
Consider a processor upgrade. While this is not often the limiting element in a FoxPro application, processor upgrades are available inexpensively, and can have surprising improvements in disk and video subsystem performance on some systems.
100Base-T, 100 Mb/s networking, is becoming far more reasonable in price.
New variations of IDE and especially SCSI - Fast, Wide, Ultra and SCSI-III - promise disk throughput in the tens of megabits per second, excellent for processing large files. Combined with a large, smart disk controller with buffering and read-ahead behaviors, PC systems can approach the throughput or larger-scale systems. Look into controllers and technologies which off-load the work from the main processor.
FoxPro can be a very graphic-intensive language, as it maintains the entire "client" area of its window itself, redrawing every control itself, unlike some systems which turn these tasks over to the operating system.
Benchmarking an application can be tricky. Care must be taken to identify that the right things are measured. If a routine combines an 250-millisecond I/O operation with processing which could take from 100 to 300 microseconds, it doesn't really matter how long the processing takes in the overall routine.
Repeating exactly the same tests can give wildly different results, even on the same machine, as many other processes may be going on at the same time, interfering with the overall measurement. Similarly, if you want others to be able to repeat your tests, you should eliminate any many other running tasks as possible, and be sure to specify the exact hardware and software configuration used in the test.
So, individual measurements rarely work.
Running the same series of commands in a large loop helps smooth the measurement error or interference of other tasks into a more fair average.
The basic technique is to store the time just before and just after the test to determine the elapsed time:
This technique can be extended to compare two rival techniques:
lnTrial2 = SECONDS() - nStart
=Result("With Macros","Without Macros",lnTrial1, lnTrial2)
The Result UDF is a simple routine to display the results:
Code Coverage Analysis is a new feature added to Visual FoxPro 5.0, and while it is somewhat limited in its abilities to give us performance information, it can be of help. The primary purpose of code coverage is to allow the developer to verify that all lines of code have been tested (covered) as part of a comprehensive testing scheme. Start code coverage by issuing the command:
Note that there is no command to SET COVERAGE ON or OFF. SETting COVERAGE TO a file automatically turns the feature on, and issuing the SET command again with no filename turns it off. Code coverage produces a text file containing one line of comma-delimited values for each line of code that fired. These values are of the format:
where the duration is an N(7,3) number of the time this command took, class is a 30-character class name, procedure is a 60-character string of the procedure file, line is the integer line number within the routine and file is the filename where the source was found. The file can be converted to a FoxPro table with a routine found in the Developer's Guide:
Similar output could be created in earlier versions of FoxPro 2.x and 3.x. The trick is to use the debugger's watch window to evaluate a low-level file write with each line of code executed. Issue the command:
to open the file. In the watch window, add the expression:
Run the program of interest. When done, issue FCLOSE(lnHandle) to close the file, then a similar routine to the above can be used to convert the output to a DBF: