Performance Tuning Tips in Microsoft Visual FoxPro
Erik Svenson, Microsoft Corporation
Introduction
Performance has always been one of the hallmarks of the Microsoftâ FoxPro® database management system, particularly in the database engine. With the introduction of the Visual FoxPro™ relational database development system and its associated object model, enhanced engine, and client/server features, the overall product becomes much more powerful. The downside of all that power, however, is that the product is more complex. So, while developing robust, object-oriented applications that may also use data from remote data stores becomes easier, it also becomes easier to create slow applications.
This document is intended to provide you with practical tips and techniques for improving the performance of your applications. By applying these techniques to your applications appropriately, you can make significant improvements to your user interface and data access performance.
While some of these tips might seem obvious, we expect that the overall collection will provide you with useful information for improving your overall application's performance. And as these techniques evolve, we encourage feedback and additions to help your fellow developers who use Visual FoxPro improve the performance of their applications.
Hardware Configuration
This section describes tips for tuning the operating system so that it runs optimally with Visual FoxPro. In some cases, you should play with some of the disk caching values to get optimal performance to meet your applications' needs.
Microsoft Windows 95 and Windows NT
For the most part, the Microsoft® Windows® 95 and Windows NT® operating systems and Visual FoxPro work extremely well together. The only exception is when you run Visual FoxPro on a Windows NT Server. The Windows NT Server uses far more memory than Visual FoxPro expects, and therefore Visual FoxPro can tend to over-allocate memory for its own uses.
To remedy this, you should use the SYS (3050) system function to adjust Visual FoxPro's memory buffer pool to approximately one third of its default (see the Memory Management section below). For example, you could add the following code to your startup code or your Config.fpw file:
=SYS (3050, VAL (SYS (3050, 1, 0)) / 3)
Local Data Performance
Rushmore and Indexes
Chapter 15 in the Developer's Guide provides an in-depth discussion of Rushmoreä techniques. The following tips are supplementary.
Use Indexes
It is a simple fact that using indexes improves data queries. Indexes in Visual FoxPro (and previous versions) are data structures based on a tree, so lookups into that index table are very fast.
One of the most unique aspects of Visual FoxPro is the support of indexes based on expressions. Most DBMSs allow indexing on one or more fields. Visual FoxPro allows arbitrary expressions to be used in indexes, even user-defined functions.
You should be aware that, as you add more indexes to your table, updates and inserts to the table are slower because Visual FoxPro needs to update each index. In general, you should only index data that is used in filters and joins.
You should avoid using index expressions in the form of FOR <condition> or NOT <condition> as these expressions are not optimizable for the index. For example:
INDEX ON DELETED() TAG DEL
is Rushmore optimizable, but:
INDEX ON NOT DELETED()TAG NOTDEL
is not. As a special case for queries that you don't want deleted records in, using an index like the first example above will speed up operations when you have SET DELETED ON.
If you use a FOR clause in the expression, Visual FoxPro will not use the index for optimization. For example:
INDEX ON ORDNUM FOR DISCOUNT > 10 TAG ORDDISC
Finally, be careful not to use indexes in which only a few discreet values would be used (like logical fields).
When are Queries Rushmore Optimized?
It is important to understand the cases in which queries or filters made against Visual FoxPro tables will be optimized by Rushmore and when they are not, or only partially optimized.
Visual FoxPro uses Rushmore technology to optimize filter conditions by looking for index key expressions that match the left side of the filter expression. Often, developers mistakenly attempt to match the tag name of an index with a filter expression, only to find that Visual FoxPro cannot optimize queries that way. Here is an example of the wrong way to use indexes:
USE CUSTOMER
INDEX ON UPPER(contact) TAG name
SELECT * FROM customer ;
WHERE contact="BILL" & not optimized. No index on "contact"
Here is the right way to take advantage of an index in a filtered query:
SELECT * FROM customer WHERE UPPER(contact)="BILL" & Optimized!
You can also make use of the SYS(3054) function to determine the level of Rushmore query optimization. SYS(3054) enables or disables the display of Rushmore optimization levels for queries. Issue SYS(3054,1) to display the level of Rushmore optimization; issue SYS(3054, 0) to stop display of the Rushmore optimization level.
Database Engine Performance Features
Use Primary and Candidate Indexes
In FoxPro 2.x, the typical technique for adding records with unique ids was to create an index on your key field and then, when it was time to add a record, run a procedure that used SEEK to see if the id was already used. While this was pretty fast, it was still not optimal.
By using Primary and Candidate keys, inserts (or updates) into Visual FoxPro tables are validated by the database engine for uniqueness, which occurs at a low level and is extremely fast.
International Considerations
Improved Performance with Diacritics
In Visual FoxPro, we've improved the way in which lookups to data containing international characters (characters with diacritic marks like à, ê, ö, etc.) are optimized. If your index expression result is such that the characters can never support diacritic marks (like B, or M), lookups will be faster.
Non-Machine Collate Sequences
There are several reasons why a non-machine collate sequence (such as "General") is slower than the "Machine" collate sequence:
1. Non-machine index keys are twice as large because they contain the diacritic information.
2. Non-machine collation uses many special rules for indexing characters to return proper results.
Because the machine collate sequence is faster, it is usually preferred for joins and seeks, while other collate sequences are perfect for ordering records.
Note Visual FoxPro only utilizes indexes that were created using the current setting of SET COLLATE. So the typical workaround is to have two indexes on your primary search/sort fields:
SET COLLATE TO "GENERAL"
INDEX ON lastname TAG lastname & sort index
SET COLLATE TO "MACHINE"
INDEX ON lastname TAG _lastname & seek/select index
Now, when you want to do a seek, select, or join on the field "lastname" using the faster "machine" index, just SET COLLATE TO "MACHINE" before performing the seek/select/join. Rushmore will use the index created in the machine collate sequence, and the seek/select/join will be very fast.
Using SQL SELECT
If an SQL SELECT is performed in the machine collate sequence, any ORDER BY or GROUP BY will also use the MACHINE sequence. If you need to order the resulting records by a non-machine collate sequence, you can either perform the SELECT in that collate sequence, which may be much slower, or split it into two steps as follows:
* Step 1: SELECT records using the "Machine" collate sequence
SET COLLATE TO "MACHINE" & use only 'machine' indexes
SELECT * FROM table INTO CURSOR temp1 WHERE lastname = "Müller"
* Step 2: Order the records using the "general" collate sequence
SET COLLATE TO "GENERAL" & use only 'general' indexes
SELECT * FROM temp1 INTO TABLE output ORDER BY lastname
Use New Data Types
We introduced four new data types to Visual FoxPro: DateTime, Integer, Double, and Currency. All data that use these types are stored as binary data on disk (Integer is a four-byte binary value; the others are eight-byte binary).
There are two advantages here. First, because the volume of data stored on disk is smaller (an eight digit number stored as Numeric takes eight bytes, whereas it only takes four bytes if it is stored as Integer), loading data and indexes into memory from disk is faster because more actual data can be loaded into memory pages. The second advantage is that no data translation needs to occur. Internally, Visual FoxPro represents integers as four byte binary values and has to translate numerics, which are stored as ASCII on disk. So, every read has to be translated from ASCII to binary and back again. With Integers Doubles, DateTimes, and Currency, such translation doesn't occur so data access is faster.
Of the new data types, Integer is the most important for speed. Wherever possible, use Integer data types for primary and foreign key values. The benefits will be smaller DBFs, smaller indexes, and (perhaps most importantly) much faster joins!
Buffering and Transactions
Keep Transactions Tight
Transactions need to be kept short and to the point. Consider the following example:
BEGIN TRANSACTION
DO FORM BIGFORM TO nResult
IF nResult = IDCOMMIT
END TRANSACTION
ELSE
ROLLBACK
ENDIF
What's happening here? While you might think that this is properly wrapping a transaction around an operation, it is. However, transactions are designed to wrap updates to data only. By doing more than that (with user-interface, as shown above, or even programming constructs like CASE, WHILE, or IF statements), you introduce inefficiencies into the actual updating of the data.
This is significant in Visual FoxPro because the use of transactions requires the locking of records. While in the transaction and updating records, locks on those records are taken and not released until the transaction is committed or rolled back. Even if you issue RLOCK() or FLOCK() followed by UNLOCK, the record locks are held until the END TRANSACTION or ROLLBACK statement. Further, appending records requires Visual FoxPro to lock the header and keep the header lock until the transaction is completed (committed or rolled back). Therefore, in a high-volume, multi-user system, it becomes crucial to minimize the amount of time record locks are held. Thus, a more appropriate application of the above example would be:
* Save method from the cmdSave command button
BEGIN TRANSACTION
UPDATE PRODUCTS SET reorder_amt = 0 ;
WHERE discontinued = .T.
END TRANSACTION
Use SYS(3051)
This SYS function controls the amount of time, in milliseconds, that Visual FoxPro will wait before attempting to lock a record, table, index, or memo after an unsuccessful locking attempt. The default is 333 milliseconds.
If there is a lot of lock contention on your database files, you can improve the performance of your application by adjusting this value up (the maximum is 1000). As a result, your application doesn't waste time trying to get locks across a network very quickly.
However, if the lock contention is not expected to be high, you could adjust this value down (the minimum is 100) to attempt a lock operation more quickly.
Remote Data Performance
Data retrieval from any back-end database is time consuming. In order to get data from a server database, the following steps must occur:
· The client issues the query to the back-end server database.
· The server has to parse and compile the query.
· A result set is generated.
· The server notifies the client that the result is complete.
· The client fetches the data over the network from the server.
In order to speed the retrieval (or update) of data, many techniques can be used.
Retrieve Only the Data You Need
It is the exception rather than the norm that a functional unit in an application (a form or report in most cases) needs to access all the data from a table. By creating remote views that only fetch (or update) the fields you want and the rows you want, the amount of data that moves across the wire is optimized. For example, if you had a remote view that was bound to a form with four controls (customer_id, company, contact, and address from the customers table), executing the view with a SELECT statement like:
SELECT * FROM customers
would be grossly inefficient because you're retrieving many more fields of data than is required. It would be better to have your view's SELECT statement read:
SELECT customer_id, company, contact, address FROM customers
Use a WHERE Clause
To further limit the amount of data retrieved (or updated), the proper use of a WHERE clause speeds the data access. Using the above example, if you only wanted the customers in the Northwest region, you could have your view's SELECT read:
SELECT customer_id, company, contact, address FROM customers
WHERE region = 'NORTHWEST'
Parameterize Your WHERE Clauses
The flexibility of the views and SQL Pass Through technology in Visual FoxPro allows you to use runtime parameters in the WHERE clause of your SQL for SELECT, UPDATE, and DELETE. For example, using the above example, you can retrieve information from any region at runtime:
SELECT customer_id, company, contact, address FROM customers
WHERE region = ?pcRegion
where "pcRegion" is the name of the parameter. This parameter should exist (but doesn't have to) when the view is used or re-queried to retrieve just the information required.