Hey all,

First off, this other thread already has asked the same question (with no responses):

Thread# 1004997

I will provide some more details about my situation, as it sounds like it may be similar.

We have a table called COLNOTES, it has an index tag: "file_num+id+STR(order,7)". In VFP 8.0, I run the following query:

SELECT * FROM colnotes WHERE file_num+id+STR(order,7) = 'TESTGARN' + 'G'

Notice that the index tag expression is part of the WHERE clause. SYS(3054) says there is partial optimization, and this query returns results instantaneously (not bad, considering COLNOTES has nearly 1 million records in it).

I run the exact same query against the exact same table in VFP 9.0. SYS(3054) shows Rushmore optimization of "none", and the resulting thermometer bar popup confirms that things are not so quick. The query takes 15 seconds and returns the very same records.

I even tried setting ENGINEBEHAVIOR to 80 and 70 in the VFP 9.0 session, and no change. There is no optimization going on.

If I rework the query to use just file_num+id in the WHEREclause (still an indexed expression), I see "partial" Rushmore optimization in VFP 9.0, and the results come back as fast as they would with the VFP 8.0 scenario. So, having multiple fields in the expression is not the problem. I can only assume it is is because the slower way involves an STR() function? Nope, not so fast...

Using an even MORE complex expression (yes, we have some crazy indexing going on with this table), I tried running the following query:

SELECT * FROM colnotes WHERE file_num+id+DTOC(date,1)+user+note = 'TESTGARN' + 'G'

Now the WHERE expression (still indexed) involves FIVE fields and a DATE() function. The query runs instantly, showing "partial" Rushmore once again.

So, it is only the "file_num+id+STR(order,7)" expression that offers no optimization in VFP 9.0 (regardless of ENGINEBEHAVIOR) but works fine in 8.0. All other queries seem to be fine.

Anyone else notice any slowdowns in VFP 9.0 that they were able to track down in this fashion? I always thought Rushmore just used a straight tag expression comparison, but something is sure funky with this. I can rework this query and get my form back to being speedy, but would still like an explanation if possible. Any ideas?

Thanks,
JoeK