Hi!

In first query join conditions will work on following scheme:

1. For all purchord select child records from poline
2. For all records from above result select child records from suspendc

In second query there is a difference:

1. For all poline select child records from suspendc
2. For all purchord select child records from above result

If you have much more records in the poline than in purchord, query 2 will run slower.

All above because join conditions processed in strict order in VFP. In SQL Server you will have no difference because SQL Server rearranges join conditions to get best performance (unless you use special keyword after 'FROM' in SQL SELECT).

>Can anyone tell why SELECT number 1 runs faster than SELECT number 2 - the only difference is the JOIN statements.
>
>*Obtain Orders/lines and any Suspend records
>SELECT purchord.cl_ref,poline.pl_start,poline.pl_end,poline.lo_code,suspendc.su_stdt;
> FROM purchord;
> LEFT OUTER JOIN poline;
> ON purchord.po_ref = poline.po_ref;
> LEFT OUTER JOIN suspendc;
> ON poline.pl_ref = suspendc.pl_ref;
> WHERE purchord.cl_ref = "EZ 12010";
> AND !EMPTY(po_number);
> AND (ASCAN(gaDayCare,poline.lo_code) > 0 ;
> OR ASCAN(gaHHHC,poline.lo_code) > 0 ;
> OR ASCAN(gaTransport,poline.lo_code) > 0 ;
> OR ASCAN(gaOvernight,poline.lo_code) > 0 ;
> OR ASCAN(gaMeals,poline.lo_code) > 0) ;
> AND poline.pl_start <> poline.pl_end ;
> AND poline.pl_start <> suspendc.su_stdt ;
> INTO CURSOR polines;
> ORDER BY poline.pl_start,suspendc.su_stdt
>
>And NUMBER 2
>
>*Obtain Orders/lines and any Suspend records
>SELECT purchord.cl_ref,poline.pl_start,poline.pl_end,poline.lo_code,suspendc.su_stdt;
> FROM purchord;
> LEFT OUTER JOIN poline;
> LEFT OUTER JOIN suspendc;
> ON poline.pl_ref = suspendc.pl_ref;
> ON purchord.po_ref = poline.po_ref;
> WHERE purchord.cl_ref = "EZ 12010";
> AND !EMPTY(po_number);
> AND (ASCAN(gaDayCare,poline.lo_code) > 0 ;
> OR ASCAN(gaHHHC,poline.lo_code) > 0 ;
> OR ASCAN(gaTransport,poline.lo_code) > 0 ;
> OR ASCAN(gaOvernight,poline.lo_code) > 0 ;
> OR ASCAN(gaMeals,poline.lo_code) > 0) ;
> AND poline.pl_start <> poline.pl_end ;
> AND poline.pl_start <> suspendc.su_stdt ;
> INTO CURSOR polines;
> ORDER BY poline.pl_start,suspendc.su_stdt
>
>Thanks
>Kev