Why does VFP behave so differently with non-Machine collation sequences?
Non-machine collation is slower
Two reasons why a non-machine collate sequence (such as General) are slower than the machine collate sequence:
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.
Since machine collate is faster, use it for joins and seeks, and leave the other collate sequences for ordering records that must be displayed or printed.
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: one built with
SET COLLATE TO "GENERAL" (say) and another built with
SET COLLATE TO "MACHINE". Use the machine index with
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.
NEVER use any other COLLATE sequence in a Visual FoxPro program to locate data! This will most probably cause wrong result sets since Visual FoxPro even in version 6 servicepack 3 has too many bugs. Also, SET COLLATE does not only affect how an index is created, but also any other string comparison, eg:
SET COLLATE TO "GENERAL"
? BINTOC(1) = BINTOC(2)
results in .T. !
Another issue with non-machine indexes: since each byte in the key takes two in the CDX, the 240 byte per key limit is halved. In other words, your index keys can only be up to 120 bytes, so indexing on a 128-byte field (for example) will give an error.
Contributors: Original unknown, Christof Wollenhaupt, Doug Hennig
( Topic last updated: 1999.10.25 12:10:29 PM )