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
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.