When you use Visual FoxPro to design and run applications, you want to get the best performance from your operating system, from Visual FoxPro, and from your application.
For information about how to optimize your computer and operating system, see Chapter 4, Optimizing Your System, in the Installation Guide.
This chapter describes:
You can speed access to data in tables by using indexes and by using buffering efficiently. In addition, you can use Rushmore technology to optimize your queries.
To speed access to data in a table, use indexes. Adding an index to a table speeds searches, especially if you're able to use Rushmore technology to optimize your search. Indexing also allows you to work with data in a particular order, such as viewing a customer table in order by last name.
If the records in a table have unique keys, create a primary or candidate index on the field. These types of indexes allow Visual FoxPro to validate the key at a low level, resulting in best performance.
In addition to indexing fields used for searching and sorting, you should also index any fields involved in a join. If you join two tables on fields that are not indexed, the join operation can take as much as hundreds of times longer.
An important feature of Visual FoxPro is that you can create an index on any expression. (In some database products, you can index only on fields.) This capability allows you to use indexes to optimize searching, sorting, or joining on combinations of fields, or on expressions derived from fields. For example, you can index a name field based on an expression that uses the SOUNDEX( ) function. That way, your application can provide extremely quick access to names that sound alike.
When adding indexes to your tables, you must balance the benefit you get in retrieval times against a performance loss when updating the table. As you add more indexes to your table, updates and inserts to the table are slower because Visual FoxPro needs to update each index.
Finally, avoid using indexes on fields that contain only a few discrete values, such as a logical field. In these cases, the index contains only a small number of entries, and the overhead of maintaining the index probably outweighs the benefit it provides when searching.
For details about how to index effectively when using Rushmore technology, see Using Rushmore to Speed Data Access later in this chapter.
When you create joins using SELECT - SQL, the following situations can degrade performance and produce unexpected results:
To avoid these situations, create joins based on the relationship between primary keys in one table and foreign keys in the other. If you create a join based on data that is not unique, the end result can be the product of two tables. For example, the following SELECT - SQL statement creates a join which can produce a very large result:
SELECT *;
FROM tastrade!customer INNER JOIN tastrade!orders ;
ON Customer.postal_code = Orders.postal_code
In the example, postal code uniquely identifies a location within a city, but has little value if your intent is to match customer rows and their order rows. The postal code doesn't necessarily uniquely identify a customer or an order. Instead, create a join using a statement such as the following:
SELECT *;
FROM tastrade!customer INNER JOIN tastrade!orders ;
ON Customer.customer_id = Orders.customer_id
In this example, the field customer_id uniquely identifies a specific customer and the orders belonging to that customer, and therefore creates a result set that combines the customer row with each order row.
In addition, use caution when joining tables with empty fields because Visual FoxPro will match empty fields. However, Visual FoxPro doesn't match fields containing null. When creating a join, qualify the field expressions in the join condition by testing for an empty string.
For example, if you think that the customer id field in the Orders table might be empty, use a statement such as the following to filter out order records with no customer number:
SELECT *;
FROM tastrade!customer INNER JOIN tastrade!orders ;
ON Customer.customer_id = Orders.customer_id;
WHERE tastrade!orders <> ""
Tip You can also test for an empty string using the EMPTY( ) function, but including a function call within the filter expression is not as fast as comparing to a constant value.
When you use the Project Manager, you can combine an unlimited number of programs and procedures into a single .app or .exe file. This can greatly increase program execution speed for a couple of reasons.
First, Visual FoxPro opens a program file and leaves it open. Later, When you issue a DO command on a program contained in the file, Visual FoxPro doesn't need to open an additional file.
Second, an application of only one or two files reduces the number of files necessary in the working directory. The speed of all file operations increases as the operating system has fewer directory entries to examine when opening, renaming, or deleting files.
For information on using the Project Manager to create applications, see Chapter 13, Compiling an Application.
To create the fastest possible tables and indexes, follow the recommendations listed below.
To help you optimize the performance of your applications, Visual FoxPro includes Rushmore data access technology. Using Rushmore, you can run certain complex table operations hundreds or even thousands of times faster than without it.
Rushmore technology is a data access technique that uses standard Visual FoxPro indexes to optimize access to data. You can use Rushmore with any Visual FoxPro index, including FoxPro 1.x (.idx) indexes, compact (.idx) indexes, and compound (.cdx) indexes.
Both .cdx and compact .idx indexes use a compression technique that produces indexes as small as one-sixth the size of uncompressed old-format indexes. Visual FoxPro can process a compressed index faster because it requires less disk access, and because more of the index can be buffered in memory. Although Rushmore, like other file access techniques, benefits from the smaller size of compact indexes, it also functions very well with indexes in older formats.
When Visual FoxPro processes very large tables on computers with only the minimum amount of RAM, Rushmore might not find sufficient memory to operate. In that case, Visual FoxPro might display a warning message (“Not enough memory for optimization”). Although your program will function correctly and without losing any data, the query will not benefit from Rushmore optimization.
In its simplest form, Rushmore speeds the performance of single-table commands using FOR clauses that specify sets of records in terms of existing indexes. Also, Rushmore can speed the operation of certain commands such as LOCATE and INDEX. For a complete list of optimizable commands, see the next section, “Using Rushmore with Tables.”
Visual FoxPro SQL commands use Rushmore as a basic tool in multi-table query optimization, using existing indexes and even creating new ad-hoc indexes to speed queries.
Use Rushmore to optimize data access according to the number of tables involved. When you access single tables, you can take advantage of Rushmore anywhere that a FOR clause appears. When you access multiple tables, SELECT - SQL queries supersede all Rushmore optimizations. In an SQL command, Visual FoxPro decides what is needed to optimize a query and does the work for you. You don’t need to open tables or indexes. If SQL decides it needs indexes, it creates temporary indexes for its own use.
To use Rushmore
Choose one of the following options:
-or-
The following table lists commands that use FOR clauses. Rushmore is designed so that its speed is proportional to the number of records retrieved.
Potentially Optimizable Commands with FOR Clauses
If you use a scope clause in addition to an optimizable FOR clause expression, the scope must be set to ALL or REST to take advantage of Rushmore. The NEXT or RECORD scope clauses disable Rushmore. Since the default scope is ALL for most commands, Rushmore works when you omit the scope clause.
Rushmore can use any open indexes except for filtered and UNIQUE indexes.
Note For optimal performance, don’t set the order of the table.
Creating index or tags automatically sets the order. If you want to take maximum advantage of Rushmore with a large data set that must be in a specific order, issue SET ORDER TO to turn off index control, then use the SORT command.
Rushmore cannot take advantage of all indexes. If you use a FOR clause in the INDEX command, Rushmore cannot use the index for optimization. For example, because it contains a FOR clause, the following statement cannot be optimized:
INDEX ON ORDNUM FOR DISCOUNT > 10 TAG ORDDISC
Similarly, Rushmore cannot use an index created with a NOT condition. For example, the following expression can be optimized:
INDEX ON DELETED() TAG DEL
But this one cannot:
INDEX ON NOT DELETED() TAG NOTDEL
In the special case that you want to exclude delete records from a query, using an index like the first example above will speed up operations when you've set SET DELETED to ON.
Data retrieval operations proceed without Rushmore optimization in the following situations:
Though you rarely want to, you can disable Rushmore. When you issue a command that uses Rushmore, Visual FoxPro immediately determines which records match the FOR clause expression. These records are then manipulated by the command.
If a potentially optimizable command modifies the index key in the FOR clause, the record set on which Rushmore is operating can become outdated. In this case, you can disable Rushmore to ensure that you have the most current information from the table.
To disable Rushmore for an individual command
For example, this LOCATE command is not optimized:
LOCATE FOR DueDate < {^1998-01-01} NOOPTIMIZE
You can globally disable or enable Rushmore for all commands that benefit from Rushmore, with the SET OPTIMIZE command.
To disable Rushmore globally
SET OPTIMIZE OFF
To enable Rushmore globally
SET OPTIMIZE ON
The default setting of Rushmore optimization is ON.
Rushmore technology depends on the presence of a basic optimizable expression in a FOR clause or in an SQL WHERE clause. A basic optimizable expression can form an entire expression or can appear as part of an expression. You can also combine basic expressions to form a complex optimizable expression.
A basic optimizable expression takes one of the two following forms:
eIndex relOp eExp
-or-
eExpr relOp eIndex
A basic optimizable expression has the following characteristics:
You can use BETWEEN( ) or INLIST( ) in the following two forms:
eIndex BETWEEN(eIndex, eExpr, eExpr)
-or-
eExpr INLIST(eIndex, eExpr)
Note ISBLANK( ) and EMPTY( ) are not optimizable by Rushmore.
If you create the indexes firstname, custno, UPPER(lastname), and hiredate, each of the following expressions is optimizable:
firstname = "Fred"
custno >= 1000
UPPER(lastname) = "SMITH"
hiredate < {^1997-12-30}
An optimizable expression can contain variables and functions that evaluate to a specific value. For example, using the index addr, if you issue the command STORE "WASHINGTON AVENUE" TO cVar, then the following statements are also basic optimizable expressions:
ADDR = cVar
ADDR = SUBSTR(cVar,8,3)
It is important to understand when queries will be optimized and when they will not. Visual FoxPro optimizes search conditions by looking for an exact match between the left side of a filter expression and an index key expression. Therefore, Rushmore can optimize an expression only if you search against the exact expression used in an index.
For example, imagine that you've just created a table and are adding the first index using a command such as the following:
USE CUSTOMERS
INDEX ON UPPER(cu_name) TAG name
The following command is not optimizable, because the search condition is based on the field cu_name only, not on an expression that is indexed:
SELECT * FROM customers WHERE cu_name ="ACME"
Instead, you should create an optimizable expression using a command such as the following, in which the expression for which you are searching exactly matches an indexed expression:
SELECT * FROM customers WHERE UPPER(cu_name) = "ACME"
Tip To determine the level of Rushmore optimization being used, call SYS(3054).
You can combine simple or complex expressions based on the FOR clause or WHERE clause to increase data retrieval speed, if the FOR expressions have the characteristics of basic optimizable expressions.
Basic expressions might be optimizable. You can combine basic expressions using the AND, OR, and NOT logical operators to form a complex FOR clause expression that might also be optimizable. An expression created with a combination of optimizable basic expressions is fully optimizable. If one or more of the basic expressions are not optimizable, the complex expression might be partially optimizable or not optimizable at all.
A set of rules determines if an expression composed of basic optimizable or non-optimizable expressions is fully optimizable, partially optimizable, or not optimizable. The following table summarizes Rushmore query optimization rules.
Combining Basic Expressions
| Basic Expression | Operator | Basic Expression | Query Result |
| Optimizable | AND | Optimizable | Fully Optimizable |
| Optimizable | OR | Optimizable | Fully Optimizable |
| Optimizable | AND | Not Optimizable | Partially Optimizable |
| Optimizable | OR | Not Optimizable | Not Optimizable |
| Not Optimizable | AND | Not Optimizable | Not Optimizable |
| Not Optimizable | OR | Not Optimizable | Not Optimizable |
| — | NOT | Optimizable | Fully Optimizable |
| — | NOT | Not Optimizable | Not Optimizable |
You can use the AND operator to combine two optimizable expressions into one fully optimizable expression:
FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30} && Optimizable
In this example, the OR operator combines a basic optimizable expression with an expression that is not optimizable to create an expression that is not optimizable:
FIRSTNAME = "FRED" OR "S" $ LASTNAME && Not optimizable
Using the NOT operator on an optimizable expression creates a fully optimizable expression:
NOT FIRSTNAME = "FRED" && Fully optimizable
You can also use parentheses to group combinations of basic expressions.
Just as you can combine basic expressions, you can combine complex expressions to create a more complex expression that is fully optimizable, partially optimizable, or not optimizable. You can then combine these more complex expressions to create expressions that again might be fully or partially optimizable, or not optimizable at all. The following table describes the results of combining these complex expressions. These rules also apply to expressions grouped with parentheses.
Combining Complex Expressions
| Expression | Operator | Expression | Result |
| Fully Optimizable | AND | Fully Optimizable | Fully Optimizable |
| Fully Optimizable | OR | Fully Optimizable | Fully Optimizable |
| Fully Optimizable | AND | Partially Optimizable | Partially Optimizable |
| Fully Optimizable | OR | Partially Optimizable | Partially Optimizable |
| Fully Optimizable | AND | Not Optimizable | Partially Optimizable |
| Fully Optimizable | OR | Not Optimizable | Not Optimizable |
| — | NOT | Fully Optimizable | Fully Optimizable |
| Partially Optimizable | AND | Partially Optimizable | Partially Optimizable |
| Partially Optimizable | OR | Partially Optimizable | Partially Optimizable |
| Partially Optimizable | AND | Not Optimizable | Partially Optimizable |
| Partially Optimizable | OR | Not Optimizable | Not Optimizable |
| — | NOT | Partially Optimizable | Not Optimizable |
| Not Optimizable | AND | Not Optimizable | Not Optimizable |
| Not Optimizable | OR | Not Optimizable | Not Optimizable |
| — | NOT | Not Optimizable | Not Optimizable |
You can combine fully optimizable expressions with the OR operator to create one expression that is also fully optimizable:
* Fully-optimizable expression
(FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}) ;
OR (LASTNAME = "" AND HIREDATE > {^1996-12-30})
To create partially optimizable expressions, combine a fully optimizable expression with an expression that is not optimizable. In the following example, the AND operator is used to combine the expressions:
* Partially-optimizable expression
(FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}) ;
AND "S" $ LASTNAME
Partially optimizable expressions can be combined to create one expression that is also partially optimizable:
* Partially-optimizable expression
(FIRSTNAME = "FRED" AND "S" $ LASTNAME) ;
OR (FIRSTNAME = "DAVE" AND "T" $ LASTNAME)
Combining expressions that are not optimizable creates an expression that is also not optimizable:
* Expression that is not optimizable
("FRED" $ FIRSTNAME OR "S" $ LASTNAME) ;
OR ("MAIN" $ STREET OR "AVE" $ STREET)
You can also make significant improvements in the forms and controls in your application.
Tip For information about setting and getting properties efficiently, see Referencing Object Properties Efficiently later in this chapter.
If you use 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 Load event. When you use the data environment, Visual FoxPro uses low-level engine calls to open the tables and set up the indexes and relations.
Use form sets only when it is necessary to have a group of forms share a private data session. When you use a form set, Visual FoxPro creates instances of all forms and all controls on all forms in the form set, even though the first form in the form set is the only one being displayed. This can be time consuming, and is unnecessary if the forms don’t have to share a private data session. Instead, you should execute DO FORM for other forms when they're needed.
However, if you do use a form set, you will gain some performance back when you access the forms in the form set, because the forms will already be loaded but not visible.
Page frames, like form sets, load all the controls for each page when the page frame is loaded, which can cause a noticeable delay when the page frame is loaded. Instead, you can dynamically load page controls, as needed, by creating a class out of the controls on each page, then loading them as the page is activated.
To dynamically load page controls
For example, if your controls class is named cnrpage1, you would add the following code:
IF THIS.ControlCount = 0
THIS.AddObject("cnrpage1","cnrpage1")
THIS.cnrpage1.Visible = .T.
ENDIF
You can speed the load time for a form that contains many data-bound controls if you delay the binding of those controls until they're needed.
To dynamically bind controls to data
customer.company field:* 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
THIS.Refresh
ENDIF
If you must make several changes to the screen — for example, change the values of several controls at once — you can reduce the overall time required to update the screen by delaying screen refresh until all changes are done. 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 the changes have all been completed:
To delay screen refresh
For example, the following example changes the display properties of several properties at once, moves to a new record, and only then refreshes the screen with new information. If LockScreen were not set to true, each of these operations would repaint the affected controls individually and the overall update performance would seem sluggish.
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.
Tip This technique doesn't provide any benefit if you're updating only a single control.
Because the Refresh method and Paint event are called frequently, you can improve performance in forms by reducing the amount of code in these methods. Similarly, to speed the load time for a form, you could move code from the Init event to a less frequently used event such as Activate, Click, and GotFocus. Then, you use a property on the control (such as Tag or a custom property) to keep track of whether the control has already run code that only needs to be run once.
By writing your code carefully, you can write the fastest possible programs. There are several ways to improve program performance in Visual FoxPro:
To write the fastest programs possible, follow the recommendations listed below.
Note For an example showing how to create a smaller (and therefore faster) index, run Solution.app, located in the Visual Studio …\Samples\Vfp98\Solution directory. Choose View Samples by Filtered List, select Indexes from the drop-down list, and then choose Create Small Indexes Using BINTOC( ) from the list that appears.
Tip You can test how much memory each object consumes by calling the SYS(1016) function.
If you use name expressions instead of macro substitution, program performance will greatly improve. For example, if you assign a value to the variable cFile, a name expression created with cFile is faster than macro substitution.
cFile = "CUST"
use &cFile && Macro substitution, slow
use (cFile) && Name expression: faster, preferred
By understanding how Visual FoxPro works with properties and objects, you can make your applications run more efficiently.
When you reference an object property with the object.property syntax, Visual FoxPro must search for the object before it can access the property. If you must access the property repeatedly, this search strategy can slow performance.
To avoid referencing the same procedure repeatedly (such as in a loop), read the property value into a variable, make changes, and then set the property once when you're through. For example, the following code fills a property array by first creating an array in memory, filling it, and then setting the property only once at the end:
* Copy string to a local variable
lcChar = THISFORM.cCharString
LOCAL laCharArray[256] && Create local array
FOR nCounter = 1 to 256
laCharArray[x] = SUBSTR(laChar,x,1)
ENDFOR
* Copy the local array to the property array
ACOPY(laCharArray,THISFORM.aCharArray)
If you update more than one property for the object, Visual FoxPro must search for the object multiple times, which can affect performance. In the following example, the code causes Visual FoxPro to search through four objects (such as THISFORM, pgfCstInfo, pgCstName, and txtName) to find the property to be set. Because the code sets two properties, the fourfold search is done twice:
THISFORM.pgfCstInfo.pgCstName.txtName.Value = ;
"Fred Smith"
THISFORM.pgfCstInfo.pgCstName.txtName.BackColor = ;
RGB (0,0,0) & Dark red
To avoid this overhead, use the WITH … ENDWITH command. This method causes Visual FoxPro to find the object once. For example, the following example accomplishes the same task as the previous one, but faster:
WITH THISFORM.pgfCstInfo.pgCstName.txtName
.Value = "Fred Smith"
.BackColor = RGB (0,0,0) & Dark red
ENDWITH
You can also store an object reference in a variable, then include the variable in place of the object reference:
oControl = THISFORM.pgfCstInfo.pgCstName.txtName
oControl.Value = "Fred Smith"
oControl.BackColor = RGB (0,0,0) & Dark red
If you use Automation or ActiveX controls in your application, you can fine-tune the application to get the best performance out of both ActiveX controls and Automation.
For best performance when using ActiveX controls in your forms, use the following suggestions:
If your application interacts with other applications, you can get the best performance using the following techniques.
In some cases, Automation servers (such as Microsoft Excel) will always start a new instance, even if one is already running. To remedy this and improve performance, use the GetObject( ) function instead of CreateObject( ). For example, the following call will always use an existing instance, if it exists:
x = GetObject(,"excel.Application")
In contrast, the following call creates a new instance:
x = CreateObject("excel.Application")
If you call GetObject( ) but the server isn't already running, you will get error 1426. In that case, you can trap for the error and call CreateObject( ):
ON ERROR DO oleErr WITH ERROR()
x = GetObject(,"excel.application")
ON ERROR && restore system error handler
PROCEDURE oleErr
PARAMETER mError
IF mError = 1426 then
x = CreateObject("excel.application")
ENDIF
Executing expressions that use objects within the Automation server can be expensive, particularly when evaluated multiple times. It is much faster to store objects’ references to variables for reference. For details, see Optimizing Repeated References to a Property earlier in this chapter.
If you're writing applications for a multiuser environment, performance is particularly important, because inefficiencies are multiplied. In addition, if multiple users are accessing data, your application must handle issues of concurrency and network access.
To handle these issues, you can:
You might also benefit from the suggestions for working with data stored on remote servers. For details, see Optimizing Access to Remote Data later in this chapter.
If your application attempts to lock a record or table and is unsuccessful, you can have Visual FoxPro automatically retry the lock after a small interval. However, each lock attempt results in more network traffic. If network traffic is already heavy, sending repeated lock requests adds a burden to the network, and results in overall slowdown for all users.
To address this situation, you can adjust the interval between lock attempts. By using a larger interval (which results in fewer retries per second), you reduce network traffic and gain performance.
To adjust the lock retry interval
When using transaction processing, you must design transactions to minimize the impact that they have on other users. While a transaction is open, any locks set during the transaction remain locked until the transaction is committed or rolled back. Even if you issue an explicit UNLOCK command, locks are held until the END TRANSACTION or ROLLBACK command.
Furthermore, appending records to a table requires Visual FoxPro to lock the table header. The header remains locked for the duration of the transaction, preventing other users from also appending records.
To minimize the impact of transactions, design them so that they begin and end as close to the actual data update as possible; the ideal transaction contains only data update statements.
If you are adding transaction processing to data updates made in a form, don't open a transaction, run the form, and then commit the transaction when the form is closed. Instead, put the transaction processing statements in the event code for the Save button (for example):
* Save method from the cmdSave command button
BEGIN TRANSACTION
UPDATE PRODUCTS SET reorder_amt = 0 WHERE discontinued = .T.
END TRANSACTION
Data retrieval from any remote database is expensive. In order to get data from a server database, the following steps must occur:
You can use a number of techniques to speed up the retrieval (or update) of data. The following section discusses these strategies:
In most applications that use remote data, forms and reports don't need to access all the data from a table at once. Therefore, you can speed performance by creating remote views that fetch or update only the fields and records you want, which minimizes the amount of data that needs to be transmitted across the network.
To create queries that minimize the overhead of data retrieval from remote sources, follow these suggestions:
SELECT * FROM customers unless you need all the fields from the table.SELECT cust_id, company, contact, address ;
FROM customers ;
WHERE region = ?pcRegion
When you use a view to update a table on a remote data source, Visual FoxPro must check whether the record or records you are updating have been changed. To do so, Visual FoxPro must examine the data on the server and compare it to the data being held on your computer. In some instances, this can be a time-consuming operation.
To optimize the process of updating data on remote data sources, you can specify how Visual FoxPro should check for changed records. To do this, you indicate the WHERE clause that Visual FoxPro should generate in order to perform the update.
For example, imagine that you are using a view based on a customer table on a remote data source. You created the view using a SELECT - SQL statement such as this one:
SELECT cust_id, company, address, contact ;
FROM customers ;
WHERE region = ?vpRegion
You want to be able to update all four fields that you have specified in the view except the key field (cust_id). The following table illustrates the WHERE clause that Visual FoxPro will generate for each of the options available under the SQL WHERE clause.
Note The OLDVAL( ) function returns the pre-update version of fields you modified, and the CURVAL( ) function returns the current value stored on the remote data source. By comparing them, Visual FoxPro can determine whether the record has changed on the remote data source since you downloaded it to your computer.
| Setting | Resulting WHERE clause |
| Key fields only | |
| Key and updatable fields (default) |
|
| Key and modified fields |
|
| Key and timestamp |
|
In general, you should choose an option for the SQL WHERE clause in this order of preference:
Some servers (such as Microsoft SQL Server) allow you to send a batch of SQL statements in a single packet. This speeds performance because you reduce network traffic, and because the server can compile multiple statements at once.
For example, if you specify a batch size of four, then update 10 records in a database, Visual FoxPro sends four statements such as the following to the server database in one batch:
UPDATE customer SET contact = "John Jones" ;
WHERE cust_id = 1;
UPDATE customer SET contact = "Sally Park" ;
WHERE cust_id = 2;
UPDATE customer SET company = "John Jones" ;
WHERE cust_id = 3;
UPDATE customer SET contact = "John Jones" ;
WHERE cust_id = 4
To send statements in a batch
-or-
-or-
Note You should experiment with different values for this property and the PacketSize property to optimize your updates.
You can optimize access to remote servers by fine-tuning the size of the network packet that is sent to and retrieved from the remote database. For example, if your network supports large packet sizes (greater than 4,096 bytes), you can increase the packet size in Visual FoxPro in order to send more data each time you read or write to the network.
To set packet size
Note 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.
If you're storing Memo or binary data on a remote server, you can improve performance by delaying the download of this data until your application actually requires it.
To delay retrieval of memo and binary data
-or-
Many applications include static lookup data such as state abbreviations, postal codes, and employee titles. If your application contains this type of data, and if the table is not too large, you might be able to speed up your application by keeping copies of this information on each user’s computer, because lookups do not generate network traffic.
This technique is primarily useful for data that never changes or changes very rarely. If the data does change on occasion, you must devise a strategy for downloading a new copy of the lookup table to each user’s computer.
You can gain efficiency in your application by creating local field-level and record-level rules within Visual FoxPro, rather than relying on rules defined on the server. These rules can prevent data that doesn’t conform to data or business rules from getting into the database.
By defining rules in Visual FoxPro, you trap the invalid data before it's sent across the network, which is faster, and which gives you better control for handling error conditions. However, using local rules also means that you must coordinate them with rules on the remote server. For example, if there are changes to the rules on the server, you might have to change your local rules to match.
For details about creating local rules, see the section Updating Data in a View in Chapter 8, “Creating Views.”
If you're developing international applications, you might need to manage the collating sequence of your data for optimal performance. This section discusses:
If your data doesn't include diacritical marks, such as accents (á) or umlauts (ü), you can improve performance by using the machine collating sequence because:
Because the machine collate sequence is faster, it's usually preferred for joins and searching, while other collate sequences are perfect for ordering records.
When you create an index, Visual FoxPro uses the current setting of SET COLLATE. Therefore, if you want to create two indexes with two collating sequences, you can use a sequence of commands such as the following:
SET COLLATE TO "MACHINE"
INDEX ON lastname TAG _lastname && join/seek index
SET COLLATE TO "GENERAL"
INDEX ON lastname TAG lastname && sort index
When you want to seek, select, or join on the field lastname, issue the command SET COLLATE TO “MACHINE” before performing the operation. Rushmore will then use the index created in the machine collate sequence, and the search operation will be very fast.
When you issue a SELECT - SQL command, Visual FoxPro uses the current collating sequence for searching and for the ORDER BY and GROUP BY clauses. If you want to search and sort using different collating sequences, you can split your SQL commands into two steps as follows:
* Select records using one collating sequence
SET COLLATE TO "MACHINE"
SELECT * FROM table INTO CURSOR temp1 ;
WHERE lname = "Müller"
* Order records using a different collating sequence
SET COLLATE TO "GENERAL"
SELECT * FROM temp1 INTO TABLE output ORDER BY lastname