Share via

Optimization of International Applications

  • Article

If you are 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 does not 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 is 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 collating sequence set by using the SET COLLATE, CREATE TABLE - SQL, ALTER TABLE - SQL, or INDEX commands. For example, if you want to create two indexes with two collating sequences, you can use SET COLLATE and a sequence of commands such as the following:

SET COLLATE TO "MACHINE"
INDEX ON lastname TAG _lastname     && Join or 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 Query Optimization uses the index created in the machine collate sequence, and the search operation runs very quickly.

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

See Also

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