>>>I have a VFP 8 application that runs the following query in 0.01 seconds. When I run it in VFP 9 SP1, it takes about 7 seconds:
>>>
>>>SELECT * ;
>>>FROM File ;
>>>WHERE ;
>>> cUoCode + cFtcCentury + cFtcCode + TRANSFORM(lFtdAdversary, "Y") + ;
>>> cFteCode + STR(iFtfEntry) + TTOC(tFtfEntered, 1) = lcKey ;
>>>INTO CURSOR Sql1a
>>>
>>>I read that VFP 9 SP1 corrected the problem with the STR() function not being optimized with Rushmore. Is there a similar problem with TRANSFORM() and/or TTOC? Do you have any ideas to speed up the query without recreating a new index?
>>>
>>>Thanks in advance for your help!
>>
>>I believe the problem is in TRANSFORM. I would probably use STR instead. This would mean you need to change the index :(
>
>You were right about the TRANSFORM. I changed TRANSFORM(lFtdAdversary, "Y") to IIF(lFtdAdversary, "Y", "N") and that sped up the query.
>
>Is there a list of functions that are not Rushmore optimizable?
>
>Thanks again!
I would assume it's for the functions that may return variable string lengths. TRANSFORM with the second parameter as 'Y' returns fixed length, so it must be optimizable. (BYW, Outlook doesn't recognize this word as correctly spelled). It's probably a bug. Let me search here in UT, I think it was discussed already.
If it's not broken, fix it until it is.
My Blog