Re: SQL Select: Very Fast in VFP8, Very Slow in VFP9

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi David,

Since the slavefile.ssn and the eog04all.ssn fields were the same size
(containing social security numbers, 9 characters in length), the
ALLTRIM functions were superfluous.
-
The new JOIN condition is --
-
slavefile.ssn  + ;
LEFT(slavefile.lname,4) + ;
LEFT(slavefile.fname,4)  = ;
        eog04all.ssn   + ;
        LEFT(eog04all.lname,4) + ;
        LEFT(eog04all.fname,4)
-
When I removed the ALLTRIM functions, the SQL Select statement in VFP9
suddenly ran AT LEAST as fast as it ran in VFP8!
-
Does this mean that in VFP9, SQL Select statements containing the
ALLTRIM function cannot be optimized and should be avoided?

Basically, if you use ALLTRIM, then only in some special cases there is a chance of optimizing such an expression. Who knows in advance how long will be this trimmed string?

In your case I would use a different expression. Instead of composing two strings with a kind of "internal" structure I would compare those "internal" parts directly:

slavefile.ssn = eog04all.ssn ;
..AND. left(slavefile.lname, 4) = left(eog04all.lname, 4) ;
..AND. left(slavefile.fname, 4) = left(eog04all.lname, 4)

With this expression there are a lot more chances for the optimizer to optimize.

Regards
Bernhard Sander
.