Hey all,

Another goofy index issue. This time the field is a single field index called "File_num". File_num is an 8-character field, and the index is just on the expression "file_num".

In VFP 9.0, when I query the table using

SELECT * FROM fmrec WHERE file_num = 'FORKEARS'

SYS(3054) shows optimization of "none". Doing the exact same query in VFP 8.0 yields partial optimization (full if SET DELETED is OFF).

Same query in 9.0 with SET ENGINEBEHAVIOR 80, still no optimization.

Here is the real kicker: we have two other tables with the exact same structure as FMREC, RECEIPTS and GEREC. Same fields, same indexes. Same query on those tables (in 8.0 and 9.0) yields partial optimization. The RECEIPTS table has over 3 million records while FMREC has only 100,000, and RECEIPTS runs circles around FMREC. Rushmore is a wonderful thing when it works.

We have been dealing with these little 9.0 issues for months now, and it is getting extremely old. I messed with SET EXACT and SET ANSI and nothing I do can make VFP 9.0 optimize on that tag. Other tags work just fine. This is after doing a DELETE TAG ALL and re-adding all the indexes fresh.

Help?

Thanks,
JoeK