>Also, check how many records are returned for each individual part of the expression: upper(ino), upper(bcode), upper(loccode). If one of these expressions returns too many records (say, more than a thousand),
delete the corresponding index.
The fact that an index too much reduces performance is counterintuitive, but it agrees perfectly with my experience.
The reason this happens is that Visual FoxPro 1) gets (from the index only) all records for a certain key value for each part of the expression (if it can find an index), 2) creates a memory bitmap (i.e., one bit for each record, "include/exclude"), 3) bit-ANDs the results, and 4) fetches the corresponding records.
If a key value returns too many records, part (1) will take too long. If the "too many values" index doesn't exist, Visual FoxPro will, instead, fetch only a few records (according to the other conditions), and apply the additional filter directly to the (few) records.
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
