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

Thanks to everyone who replied. This is what I did. The LEFT OUTER
JOIN for the two tables contained the following JOIN condition:
-
ALLTRIM(slavefile.ssn) + ;
LEFT(slavefile.lname,4) + ;
LEFT(slavefile.fname,4) = ;
ALLTRIM(eog04all.ssn) + ;
LEFT(eog04all.lname,4) + ;
LEFT(eog04all.fname,4)
-
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?
-
Thanks again.
David

.

Relevant Pages

  • Re: SQL Select: Very Fast in VFP8, Very Slow in VFP9
    ... existing index tags, and in your example, you should have a tag on ... > JOIN for the two tables contained the following JOIN condition: ... > When I removed the ALLTRIM functions, the SQL Select statement in VFP9 ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Joining every row from 1 table to every row from another
    ... I need to join all garments to all materials,! ... This can be done in SQL simply by omitting any join condition. ... This should work in Oracle, MySQL, or any other system that supports the most rudimentary features of SQL. ...
    (comp.lang.java.databases)
  • Re: join conditions for files
    ... The problem is in the join condition with '$' ... But SQL LIKE is allowed and a UDF is allowed. ... FUNCTION yourudf(tcContained, tcContains) ... Don't use local aliases made of one letter only, they may confuse Foxpro, since they are reserved names for workspace aliases. ...
    (microsoft.public.fox.programmer.exchange)