Erik Svenson
Program Manager, Internet Tools and Platforms Division
Microsoft Corporation
Updated October 1996
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 Microsoft 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 article 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.
This section describes tips for tuning the operating system so that it runs optimally with Visual FoxPro. In some cases, you should play with the disk caching values to get optimal performance to meet your applications' needs.
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, so 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 the Visual FoxPro memory buffer pool to approximately one-third of its default (see Memory Management). For example, you could add the following to your startup code or your CONFIG.FPW file:
=SYS (3050, VAL (SYS (3050, 1, 0)) / 3)
Chapter 15, “Optimizing Applications,” of the Visual FoxPro Developer's Guide in the MSDN Library provides an in-depth discussion of Rushmore™ techniques. The following tips are supplementary.
It is a simple fact that using indexes improves data queries. Indexes in Visual FoxPro are data structures based on a tree, so lookups in that index table are very fast.
One of the most unique aspects of Visual FoxPro is the support for indexes based on expressions. Most database management systems (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 in which you don't want deleted records, using an index expression such as 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 discrete values will be used (such as logical fields).
It is important to understand when queries or filters made against Visual FoxPro tables will be optimized by Rushmore and when they will be partially, or not at all, 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) displays the level of Rushmore optimization; issue SYS(3054,0) stops display of the Rushmore optimization level.
In FoxPro 2.x, the typical technique for adding records with unique IDs was to create an index on your key field and, 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.
In Visual FoxPro, we've improved the way lookups to data containing international characters (characters with diacritical marks such as à, ê, and ö) are optimized. If your index expression result is such that the characters can never support diacritical marks (such as B or M), lookups will be faster.
There are two reasons why a non-Machine collate sequence (such as General) is slower than the Machine collate sequence:
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 utilizes only indexes that are created using the current setting of SET COLLATE. So the typical workaround is to have two indexes on your primary search and 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 lastname field using the faster Machine index, use 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.
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
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 values).
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 data 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 .DBF files, smaller indexes, and (perhaps most importantly) much faster joins!
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? If you 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 or even programming constructs such as CASE, WHILE, or IF statements), you introduce inefficiencies in 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
This SYS function controls the amount of time, in milliseconds, that Visual FoxPro will wait before re-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 1,000). 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.
Data retrieval from any back-end database is time consuming. In order to retrieve data from a server database, the following steps must occur:
In order to speed the retrieval (or update) of data, many techniques can be used.
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 and 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 such as:
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 the view SELECT statement read:
SELECT customer_id, company, contact, address FROM customers
To further limit the amount of data retrieved (or updated), the proper use of a WHERE clause speeds the data access. Using the same example, if you only wanted the customers in the Northwest region, you could have your view SELECT statement read:
SELECT customer_id, company, contact, address FROM customers
WHERE region = 'NORTHWEST'
The flexibility of the views and SQL Pass Through technology in Visual FoxPro allows you to use run-time parameters in the WHERE clause of your SQL SELECT, SQL UPDATE, and SQL DELETE statements. For example, you can retrieve information from any region at run-time:
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 requeried to retrieve only the information required.
The Update Criteria tab in the View Designer allows you to specify how you want the data from the view to be updated, inserted, and deleted. The section of the tab labeled "SQL WHERE clause includes" lets you control the content of the WHERE clause for UPDATE and DELETE operations. This becomes critical for controlling data conflicts on the back end as well as being important for performance. Let's consider the above view and assume that the Key field is customer_id. Let's further assume that you want to be able to update all fields except the Key field (which would not be often, because changes to key fields imply a delete followed by an insert)—namely, "company", "contact", and "address." Based on the option you pick in the "SQL WHERE clause includes" option group, your WHERE clause could be any from Table 1.
Table 1. Possible WHERE Clauses for Updates and Deletions
| Setting | Resulting WHERE clause |
| Key Fields Only | WHERE OLDVAL (customer_id) = CURVAL (customer_id) |
| Key and Modified Fields (the default setting) |
WHERE OLDVAL (customer_id) = CURVAL (customer_id) AND OLDVAL (<first_modified_field>) = CURVAL (<first_modified_field>) AND OLDVAL (<next_modified_field>) = CURVAL (<next_modified_field>) AND [and so on.] |
| Key and Updatable Fields | WHERE OLDVAL (customer_id) = CURVAL (customer_id) AND OLDVAL (company) = CURVAL (company) AND OLDVAL (contact) = CURVAL (contact) AND OLDVAL (address) = CURVAL (address) |
| Key and Timestamp | WHERE OLDVAL (customer_id) = CURVAL (customer_id) AND OLDVAL (timestamp) = CURVAL (timestamp) |
It should become clear from the above table that the WHERE clause can greatly affect performance, especially if there are a lot of fields that are updated (the Key and Modified fields) or if there are a lot of fields that could be updated (the Key and Updatable fields). This is because Visual FoxPro needs to compare the values in its "oldval" data buffer (which is just a system cursor) against the values on disk (the server database) to adequately report when an update conflict has occurred (another user or process has changed the data since you first fetched it). The process of such a comparison can be time consuming if a large update operation is occurring.
Therefore, we recommend that you use the Key and Modified fields in most cases. The fields you update to the back end are almost always a subset of the total number of fields that you could update (and certainly a subset of the total number of fields in the view).
For server databases that support timestamps, we recommend that you use the Key and Timestamp setting, even above the Key and Modified Fields choice. A timestamp is a row version and is updated each time a change to the row is made. (By the way, a timestamp data type does not imply a date and time, just a numeric value. You don't need to worry about system clocks on different machines causing update problems.) By using this setting, only one column is compared in the WHERE clause, thus the processing is faster.
Note These settings can also be made using the WhereType property on views via the DBSETPROP() or CURSORSETPROP() functions.
Some server databases (such as Microsoft SQL Server) allow batches of statements to be sent to the server as a single packet. For example, if you set the value of the BatchUpdateCount property to 4 and made updates to the first 10 records, the SQL statements that Visual FoxPro sends to the server database are:
UPDATE customer SET contact = 'John Jones' WHERE customer_id = 1;
UPDATE customer SET contact = 'Sally Park' WHERE customer_id = 2;
UPDATE customer SET company = 'John Jones' WHERE customer_id = 3;
UPDATE customer SET contact = 'John Jones' WHERE customer_id = 4
This technique speeds updates, deletes, and inserts because the sheer number of network packets sent over the wire is reduced and the statements can be compiled by the server database as one batch instead of piecemeal.
You should experiment with different values for this property and the PacketSize property to optimize your updates.
This property can be set in the Advanced Options dialog box on the Query menu, or through code using DBSETPROP() or CURSORSETPROP(). Note that to get the benefit of this, manual transactions should be enabled using DBSETPROP() or SQLSETPROP() with the Transactions property.
The PacketSize property controls the size of the network packet (in bytes) that is sent to and retrieved from the database server. It is actually set by open database connectivity (ODBC) and can be any non-negative value. Different network providers will handle this property differently, so you should consult your network service documentation. Novell NetWare, for example, has a maximum packet size of 512 bytes, so setting the PacketSize property to a value greater than this will have no additional benefit.
The default setting for the PacketSize property is 4,096 bytes. If your network supports larger packet sizes, you could increase the value of this property to have maximum data throughput for the SELECT, INSERT, UPDATE, and DELETE requests.
The FetchMemo property controls whether or not memo and binary (general field) data is fetched when the row is fetched from the back end. By setting this field to .F., binary data is not transferred to your machine until the field is accessed. This will improve the fetch speed of your query because you're delaying the fetch until the data is needed.
There are many instances in which read-only data is accessed fairly often by your application. For example, a states table or a table of employee titles may be widely used in your application. By keeping that data locally (that is, not having every query uploaded to the server), lookups become extremely fast. The type of data for which this technique would be useful is data that never changes or changes very rarely.
It is a little known fact that Visual FoxPro rules support field- and record-level rules on local and remote views. These rules can be used to prevent data that doesn't conform to some data or business rule from getting into the database. The advantage of putting this rule in the view, as opposed to the back-end table, is that you trap the invalid data before it is sent over the wire. The disadvantage is that these rules don't automatically match rules on the back-end server tables. So, if your back-end table rules change in their definition, you would, in most cases, have to change your rules on the view definitions locally. However, if the rules are simple, this is not a huge burden. Also, rule definitions don't usually change that frequently, so you shouldn't expect to update local rules frequently.
To enable local rules, you use DBSETPROP(). Below is an example:
* Make sure the order number field is > 0
=DBSETPROP("orders.ordnum")
This section describes tips for improving the performance of objects in your application, particularly with respect to forms.
By using the Data Environment of the Form Designer or Report Designer, table open performance is much faster than executing USE, SET ORDER, and SET RELATION commands in the form's Load event. This is because Visual FoxPro uses low-level engine calls to open the tables and set up the indexes and relations.
Formsets should only be used when it is necessary to have a group of forms share a private data session. When you run a Formset, you're essentially telling Visual FoxPro to create all the form instances in the Formset and to create all the controls on all of those forms, even though the first form in the Formset is the only one displayed. This can be very time consuming and unnecessary if the forms don't have to share a private data session. Instead, you should execute DO FORM for other forms when they are needed.
On the other hand, if you do use Formsets, you will gain performance in accessing the forms in the Formset because they are already loaded, but not visible.
Pageframes, like Formsets, load all the controls for each page when the Pageframe is loaded. You can use the technique of creating a class out of the controls in each page of your Pageframe.
Your goal is to postpone instantiation of the controls on unseen pages until the user accesses those pages. These are the steps:
IF This.ControlCount=0
This.Addobject("cnrpage1","cnrpage1")
This.cnrpage1.visible=.T.
ENDIF
By using this technique, you speed the loading of forms because the controls on the second and subsequent pages of the Pageframe are not loaded until they are needed.
You can also see a significant boost in performance for forms that have many data-bound controls if you delay the binding of those controls until they are needed.
You can put the tables and views for your form in the Data Environment so that they are opened when the form is loaded. Then, when a control—for example a combo box—has focus, you bind the control to the data value. In the example below, a combo box has been bound to the "customer.company" field in the GotFocus event:
* Check to see if the control has been bound yet.
IF this.RecordSource = ""
* Set the record source to the right value
* and set the record source type to "fields."
this.RecordSource = "customer.company"
this.RecordSourceType = 6
* Refresh the control.
this.Refresh
ENDIF
By using this technique for data-bound controls, form load time will dramatically improve.
This property allows you to delay the screen refresh for any changes made to controls on the form. For example, if you make controls visible or invisible, change control colors, or move records in bound controls, it is much more efficient to delay the painting of those controls until after they've all completed.
thisform.LockScreen = .T.
thisform.MyButton.Caption = "Save"
thisform.MyGrid.BackColor = RGB (255, 0, 0) && Red
SKIP IN customers
SKIP IN orders
thisform.Refresh
thisform.LockScreen = .F.
In this example, we set a caption, changed a background color, and moved records in all the controls bound to customers and orders. Without the LockScreen property, you would see each of these operations cause a paint operation of the affected controls and the overall update performance would seem sluggish rather than snappy.
You should typically use this technique when multiple changes to the display of a form are needed. It is not recommended that you use LockScreen with every display change, but rather with a group of changes as the previous example shows.
When you reference a property of an object with the "object.property" syntax, Visual FoxPro must first search for the object before it can access the property. For example, the following code causes Visual FoxPro to search through four objects (thisform, pgfCustInfo, pagCustName, and cboName) to find each of the three properties to be set:
thisform.pgfCustInfo.pagCustName.txtName.Value = "Fred Smith"
thisform.pgfCustInfo.pagCustName.lblName.Caption = "Name"
thisform.pgfCustInfo.pagCustName.grdOrders.BackColor = RGB (0,0,0)
lcSeek = ALLTRIM(thisform.pgfCustInfo.pagCustName.txtName.Value)
If you will be setting two or more properties of an object in succession, use the WITH … ENDWITH construct. This will cause Visual FoxPro to locate and find the object only once. With the above property settings, using WITH … ENDWITH will be faster:
WITH thisform.pgfCustInfo.pagCustName
.txtName.Value = "Fred Smith"
.lblName.Caption = "Name"
.grdOrders.BackColor = RGB (0,0,0)
lcSeek = ALLTRIM(.txtName.Value)
ENDWITH
Note You can use the <.objectname> syntax anywhere in the WITH … ENDWITH construct as shown with the ALLTRIM function.
Another way to do this is to use object references. Object references are nothing more than variables that hold a reference to an object. Using the example above, you could also do the following:
oPageFrame = thisform.pgfCustInfo.pagCustName
oPageFrame.txtName.Value = "Fred Smith"
oPageFrame.lblName.Caption = "Name"
oPageFrame.grdOrders.BackColor = RGB(0,0,0)
The example above won't necessarily yield a significant performance boost, but if the object (the pagCustName PageFrame object in this case) is referenced several times in your application or in a loop, performance will improve.
Accessing the full <container.object.property> is slower than accessing a variable. This can be especially noticeable within program loops. Typical object-oriented programming practice is to copy the contents of an object property to a variable if it is to be used repeatedly. For example, the following code fills a property array:
FOR x = 1 to 256
thisform.aCharArray[x] = SUBSTR(thisform.cCharString, x, 1)
ENDFOR
The following code is much faster even though more code is executed because the <object.property> code has been factored out of the loop:
* Copy string to a local variable.
lcChar = thisform.cCharString
* Create a local array.
LOCAL laCharArray[256]
FOR nCounter = 1 to 256
laCharArray[x] = SUBSTR(laChar, x, 1)
ENDFOR
* Copy the local array to the property array.
=ACOPY(laCharArray, thisform.aCharArray)
These events occur very frequently. You should eliminate (or reduce) the amount of code you have in these methods. This is especially true for Refresh and Paint methods because they happen very often.
Move Init code to less frequently used methods such as Activate, Click, and GotFocus. Then, you can use a property on an control that only needs to be run once to keep track of whether the control has already run code.
The NoDataOnLoad property on a Data Environment Cursor object for a view is identical to the NODATA clause on the USE command for a view. It causes the view to be opened, but the view doesn't actually fetch any data. This is true for both local and remote views.
You would typically use this technique with parameterized views in your Data Environment (see Parameterize Your WHERE Clauses). For example, you could have a customer information form that uses a view that is parameterized on customer_id. You would enter a valid customer_id and press a Search button. The code of the search button would be something like the following (assuming that the parameter for the view is called vpCustID, where vp is the Hungarian notation used for designating a variable as a view parameter):
* Set the view parameter to the value in the customer id textbox.
vpCustID = thisform.txtCustomerID
* Lock the screen to delay painting.
thisform.LockScreen = .T.
* Issue the requery on the view.
=REQUERY('customerview')
* Refresh the databound controls.
thisform.Refresh
* Remove the lock on the screen.
thisform.LockScreen = .F.
This code is a simple example, but it is typical for processing local and remote parameterized views.
The advantage here is that form load time is greatly reduced because the view doesn't cause any data to be brought back to you, the client. The controls that are bound to fields of the view still get bound because there is an open work area (with no data in it).
You should start OLE servers before you access OLE data. Controls bound to general fields will generally perform better when the servers for those data types (such as Microsoft Word or Microsoft Excel) are already running on the client's machine.
In some cases an Automation server (such as Microsoft Excel) will always start a new instance of itself, even if one is already running. To remedy this (and improve performance), use the GetObject function instead of CreateObject. For example, the following call:
x = GetObject(, "excel.Application")
will always use an existing instance if it exists, whereas:
x = CreateObject("excel.Application")
will create a new instance.
When you use GetObject, you will receive an error from Visual FoxPro if the server is not already running, so you should trap for the error and call CreateObject().
Don't repeatedly evaluate long expressions with subobjects. Executing expressions that use objects within the OLE server can be time consuming, particularly when evaluated multiple times. It is much faster to cache subobjects in variables for reference (see Use Variables for Multiple Assignments).
When you insert an OLE object into a field, you can insert it as an icon or placeholder rather than as the entire object. This will reduce the amount of storage space required because Visual FoxPro stores a presentation image with the object. Also, an increase in the performance for drawing the object will occur because only the icon needs to be rendered.
If single bitmaps are to be used in an application such as a company logo, Image Controls are much faster than OLEBoundControls.
Manual links to objects are faster because of the notification time required with automatic links and also because the OLE server doesn't need to be started to draw the object. If you don't need to update an object frequently, use manual links.
This SYS function allows you to optimize the amount of foreground and background memory that is used for data buffers. Foreground memory refers to memory that is available to Visual FoxPro when it is the foreground (active) application. Background memory refers to memory that is available to Visual FoxPro when it is one of the background applications.
To optimize your application, you can experiment with these values to have Visual FoxPro consume more or less memory for its data buffers.
MEMLIMIT is not recognized by Visual FoxPro. In FoxPro 2.6, MEMLIMIT was a configuration setting that specified the maximum amount of memory FoxPro would allocate for its use. Do not use this configuration setting to limit the amount of memory that Visual FoxPro will use. Use SYS(3050) if necessary.
Although I put this paper together, it would have been nearly impossible without the contributions of several members of the Visual FoxPro team: Geoff Kizer, Tom Cooper, Lori Sargent, Rodney Hill, Dave Berliner, Matt Pohle, and the rest of the Visual FoxPro performance team.
I would also like to thank George Goley of MicroEndeavors, Inc., and David T. Anderson of Alden Anderson Consulting, Inc., for providing many of these tips and for reviewing this paper.