There definitely seems to be a major difference in doing SQL selects
from Foxpro 8 to FoxPro 9. Specifically FoxPro 9 seems to handle
indexes comprised of multiple fields differently from FoxPro 8. For
example, suppose you had an index like this:

alltrim(lastname)+alltrim(str(idnumber,10,0))

In FoxPro 8, you could do a select with the exact index expression on
the left of the WHERE clause in your SQL statement. In FoxPro 9, the
statement apparently will not be optimized. To achieve the same
result, you would have to have an index on a single field expression
and put that first in your where clause. For example, supposing you
wanted to find someone by idnumber and lastname, assuming you have an
index on idnumber, you would use:

where idnumber=TargetNumber and alltrim(lastname)=TargetName

Or am I missing something?

Kevin Clark
Seton Home Study School

Kevin,
I don't believe any non-fixed length index expression was ever truly optimized, even though it may have reported it as such.

Have you tried something like:
padr(alltrim(lastname)+alltrim(str(idnumber,10,0)) , 40)

Rick

"Kevin Clark" <KevClark64 (AT) hotmail (DOT) com> wrote

I don't know if such expressions were fully optimized in FoxPro 8. I
do know that I have run into several occasions where FoxPro 8 would
return instant results and FoxPro 9 takes a very long time. So there
must be some difference in the SQL engine in FoxPro 9 that would
account for this.

It's not a big problem since the query can be rewritten or a new index
created if necessary, but there is definitely a difference.

Kevin Clark
Seton Home Study School

Kevin,
Remember that as with any beta, some optimizations aren't included until just before the release AND there is often extra debug code included to help users report problems. It's never a good idea to benchmark betas against production releases. Indeed changes have been made to the SQL engine - just look at all the additional capabilities that are included! It'll be interesting to do some performance testing once we have the real code. Of course some things may need to be re-written to see the true enhancements - multiple Selects can now be a single statement!

Rick

"Kevin Clark" <KevClark64 (AT) hotmail (DOT) com> wrote