Optimization of International Applications

  • Article

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 = "Mller"
* Order records using a different collating sequence
SET COLLATE TO "GENERAL"
SELECT * FROM temp1 INTO TABLE output ORDER BY lastname

See Also

Optimizing Access to Remote Data | Optimizing Applications | Optimizing Your System | Optimizing Applications in Multiuser Environments | Optimization of ActiveX Controls