Chapter 15 -- Optimizing Applications

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:

• Optimizing Tables and Indexes
• Using Rushmore to Speed Data Access
• Optimizing Forms and Controls
• Optimizing Programs
• Optimizing ActiveX Controls
• Optimizing Applications in Multiuser Environments
• Optimizing Access to Remote Data
• Optimizing International Applications

Optimizing Tables and Indexes

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.

Using Indexes

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.

Optimizing Joins

When you create joins using SELECT - SQL, the following situations can degrade performance and produce unexpected results:

• Joining tables on data that is not a primary or unique key in one of the tables.
• Joining tables containing empty fields.

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.

Using the Project Manager

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.

General Table and Index Optimization Hints

To create the fastest possible tables and indexes, follow the recommendations listed below.

• If record or table buffering is not enabled, use INSERT - SQL instead of APPEND BLANK followed by REPLACE, particularly with an indexed table in a multiuser environment, because indexes only need to be updated once.
• If you need to append a large number of records to an indexed table, it might be faster to remove the index, append the records, and then re-create the index.
• In SQL statements, avoid function calls if possible, especially in statements that will return more than one record, because the statement must be reevaluated (and the functions called again) for each record. If you are creating a SQL statement with variable data, use name expressions or macro substitution in favor of the EVALUATE( ) function. A better strategy yet is to construct the entire statement dynamically, not just individual clauses. For more information, see Using Macros and Creating Name Expressions.
• If you usually use a certain index order, you can improve performance by periodically sorting the table in this order.
• Use .cdx instead of .idx files in multiuser environments because you can update one .cdx file faster than you can update multiple .idx files.

Using Rushmore to Speed Data Access

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.

Understanding Rushmore Technology

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.

Using Rushmore with Tables

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:

• To access data from a single table, use a FOR clause in a command such as AVERAGE, BROWSE, or LOCATE, or use SQL commands to update tables. For a complete list of commands that use the FOR clause, refer to the table below.

  -or-

• To access data from more than one table, use the SELECT - SQL DELETE - SQL, and UPDATE - SQL commands.

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

AVERAGE	BLANK
BROWSE	CALCULATE
CHANGE	COPY TO
COPY TO ARRAY	COUNT
DELETE	DISPLAY
EDIT	EXPORT TO
INDEX	JOIN WITH
LABEL	LIST
LOCATE	RECALL
REPLACE	REPLACE FROM ARRAY
REPORT	SCAN
SET DELETED	SET FILTER
SORT TO	SUM
TOTAL TO

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.

Indexing Effectively for Rushmore

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.

Operating Without Rushmore

Data retrieval operations proceed without Rushmore optimization in the following situations:

• When Rushmore cannot optimize the FOR clause expressions in a potentially optimizable command. For more information on creating an optimizable FOR expression, see the section Combining Basic Optimizable Expressions.
• When a command that might benefit from Rushmore contains a WHILE clause.
• When memory is low. Data retrieval continues, but is not optimized.

Disabling Rushmore

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

• Use the NOOPTIMIZE clause.

  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

• Use the following code:

  SET OPTIMIZE OFF 

To enable Rushmore globally

• Use the following code:

  SET OPTIMIZE ON 

The default setting of Rushmore optimization is ON.

Optimizing Rushmore Expressions

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.

Creating Basic Optimizable Expressions

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:

• eIndex exactly matches the expression on which an index is constructed.
• eExpr is any expression and can include variables and fields from other unrelated tables.
• relOp is one of the following relational operators: <, >, =, <=, >=, <>, #, ==, or !=. You can also use the ISNULL( ), BETWEEN( ), or INLIST( ) functions (or their SQL equivalents such as IS NULL, and so on).

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) 

Understanding When Queries Are Optimized

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).

Combining Basic Optimizable Expressions

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.

Combining Complex 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) 

Optimizing Forms and Controls

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.

Using the Data Environment

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.

Limiting the Number of Forms in a Form Set

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.

Dynamically Loading Page Controls on a Page Frame

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

1. Design your form as you normally would, including all controls on all pages.
   
   
2. When your design is complete, go to the second page of your page frame, and save the controls you find there as a class.
   
   
3. Open the class you created, and ensure the controls are still properly laid out.
   
   
4. Repeat Steps 2 and 3 for the third and subsequent pages of the page frame.
   
   
5. In the Activate event of the second and subsequent pages of the page frame, add objects and make them visible.

   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 

Dynamically Binding Controls to Data

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

1. Put the tables and views for your form in the data environment so that they're opened when the form is loaded.
   
   
2. For each bound control, add code to its GotFocus event code that binds the control to the data value. For example, the following code binds a ComboBox control to the 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 

Delaying Screen Refresh

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

1. Set the form s LockScreen property to true.
   
   
2. Update the controls as required.
   
   
3. Call the form s Refresh method.
   
   
4. Set the form s LockScreen property to false.

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.

Reducing Code in Frequently-Used Methods

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.

Optimizing Programs

By writing your code carefully, you can write the fastest possible programs. There are several ways to improve program performance in Visual FoxPro:

• Following the general programming performance hints provided below.
• Using name expressions instead of macro substitution.
• Referencing object properties efficiently.

General Programming Performance Hints

To write the fastest programs possible, follow the recommendations listed below.

• Choose the correct data type for your data. In particular, use the Integer data type for numeric information whenever possible, as it is processed most efficiently. Wherever possible, use Integer data types for primary and foreign key values, which will result in smaller data files, smaller (and therefore faster) indexes, and faster joins.

  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.

• Avoid reopening files, which slows performance. Instead, assign files to work areas as you open them, then use the SELECT command to choose a specific work area as needed.
• Use FOR ENDFOR loops rather than DO WHILE ENDDO loops when possible, because they're faster.
• When you copy data from multiple fields, SCATTER TO ARRAY is faster than SCATTER MEMVAR.
• To use memory most efficiently, avoid creating objects before you need them, and clear objects when you finish with them to free memory.

  Tip   You can test how much memory each object consumes by calling the SYS(1016) function.

• Send output to the topmost window whenever possible; updating windows behind the top window is substantially slower. Causing output to scroll behind a window is nearly a worst-case event.
• Disable status display with the SET TALK OFF command, which eliminates the overhead of screen update.
• Set the SET DOHISTORY command to OFF to avoid updating the command window each time a program runs.

Using Name Expressions Instead of Macro Substitution

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 

Referencing Object Properties Efficiently

By understanding how Visual FoxPro works with properties and objects, you can make your applications run more efficiently.

Optimizing Repeated References to a Property

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) 

Referencing Multiple Properties Efficiently

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 

Optimizing ActiveX Controls

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.

Using ActiveX Controls Efficiently

For best performance when using ActiveX controls in your forms, use the following suggestions:

• Start Automation servers in advance. Controls bound to general fields will generally perform better when the servers for those data types (like Microsoft Excel or Word) are already running on the client s machine.
• Insert objects As Icon. When you insert an ActiveX control into a field, insert it as an icon or placeholder rather than as an entire object. This reduces the amount of storage space required because Visual FoxPro stores a presentation image with the object, which can consume a lot of storage space. Inserting an object as an icon also increases performance for drawing the object.
• Use image controls. If you want to display a bitmap (such as a company logo), image controls are much faster than OLEBound controls.
• Use manual links whenever possible. Manual links to objects are faster because they avoid the notification time required for automatic links, and because the server doesn t need to be started to draw the object. If you don t need to update an object frequently, use manual links.

Optimizing Automation Performance

If your application interacts with other applications, you can get the best performance using the following techniques.

Avoiding Multiple Instances of the Server

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 

Referencing Objects Efficiently

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.

Optimizing Applications in Multiuser Environments

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:

• Adjust lock retry interval.
• Use transaction processing efficiently.

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.

Adjusting Lock Retry Interval

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

• Call the SYS(3051) function, passing it the number of milliseconds to wait between each lock attempt.

Using Transaction Processing Efficiently

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 

Optimizing Access to Remote Data

Data retrieval from any remote database is expensive. In order to get data from a server database, the following steps must occur:

1. The client issues the query to the remote database.
   
   
2. The server parses and compiles the query.
   
   
3. The server generates a result set.
   
   
4. The server notifies the client that the result is complete.
   
   
5. The client fetches the data over the network from the server. This step can happen all at once, or the client can request that results be sent in pieces as requested.

You can use a number of techniques to speed up the retrieval (or update) of data. The following section discusses these strategies:

• Retrieving only the data you need
• Updating remote tables efficiently
• Sending statements in a batch
• Setting packet size
• Delaying retrieval of memo and binary data
• Storing lookup data locally
• Creating local rules

Retrieving Only the Data You Need

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:

• Specify only the fields you need. Don t use the statement SELECT * FROM customers unless you need all the fields from the table.
• Include a WHERE clause to limit the number of records downloaded. The more specific your WHERE clause, the fewer records are transmitted to your computer, and the faster the query will finish.
• If you cannot predict at design time what values to use in a WHERE clause, you can use parameters in the clause. When the query is executed, Visual FoxPro uses the value of a parameter variable or prompts the user for the search value. For example, this query allows the application or user to fill in the region at run time:

  SELECT cust_id, company, contact, address ; FROM customers ; WHERE region = ?pcRegion 

• Set the NoDataOnLoad property of the corresponding Data Environment Cursor object. This technique is commonly used with parameterized views in which the data for the parameter comes from the value of a control on a form.

Updating Remote Tables Efficiently

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	WHERE OLDVAL(cust_id) = CURVAL(cust_id)
Key and updatable fields (default)	WHERE OLDVAL(cust_id) = CURVAL(cust_id) AND    OLDVAL(<mod_fld1>) = CURVAL(<mod_fld2>) AND   OLDVAL(<mod_fld2>) = CURVAL(<mod_fld2>) AND   ...
Key and modified fields	WHERE OLDVAL(cust_id) = CURVAL(cust_id) AND    OLDVAL(company) = CURVAL(company) AND   OLDVAL(contact) = CURVAL(contact) AND   OLDVAL(address) = CURVAL(address)
Key and timestamp	WHERE OLDVAL(cust_id) = CURVAL(cust_id) AND    OLDVAL(timestamp) = CURVAL(timestamp)

In general, you should choose an option for the SQL WHERE clause in this order of preference:

1. Key and timestamp, if the remote database supports timstamped fields, which is the fastest way to tell if a record has changed.
   
   
2. Key and modified fields, because the fields you update to the server are almost always a subset of the total number of fields that you could update.
   
   
3. Key and updatable fields.
   
   
4. Key fields only. Using this settings implies that the remote server will insert an entirely new record using the changed key, and will delete the old record.

Sending Statements in a Batch

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

• In the Options dialog box, choose the Remote Data tab, and then under Records to batch update, specify the number of records to include in a batch.

  -or-

• Call the DBSETPROP( ) or CURSORSETPROP( ) functions to set these properties:
  • Set Transaction to 2.
    
    
  • Set BatchUpdateCount to the number of statements to send in a batch.

  -or-

1. In the View Designer, choose Advanced Options from the Query menu to display the Advanced Options dialog box.
   
   
2. In the Performance area, next to Number of records to batch update, specify the number of statements to send in a batch.

   Note   You should experiment with different values for this property and the PacketSize property to optimize your updates.

Setting Packet Size

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

• Call the DBSETPROP( ) or CURSORSETPROP( ) functions and set the PacketSize property to a positive integer value. The default value is 4,096.

  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.

Delaying Retrieval of Memo and Binary Data

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

• In the Options dialog box, choose the Remote Data tab, and then under Remote view defaults, set Fetch memo.

  -or-

• Call the DBSETPROP( ) or CURSORSETPROP( ) functions to set the FetchMemo property.

Storing Lookup Data Locally

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.

Creating Local Rules

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.

Optimizing International Applications

If you're developing international applications, you might need to manage the collating sequence of your data for optimal performance. This section discusses:

• Using collating sequence efficiently.
• Using SELECT - SQL with multiple collating sequences.

Using Collating Sequence Efficiently

If your data doesn't include diacritical marks, such as accents ( ) or umlauts ( ), you can improve performance by using the machine collating sequence because:

• Non-machine index keys are twice as large because they contain the diacritical information.
• Non-machine collation uses many special rules for indexing characters to return proper results.

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.

Using SQL SELECT with Multiple Collating Sequences

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