>This is important.
>Then VFP's SQL engine is not very smart and a big bug exists.
>with 3 or more join tables:
>- With FORCE it loses the compass, for the first two tables load the index but doesn't uses it.
>- Without FORCE it choice a very bad join way.
>
>Explain better:
>with FORCE VFP uses a step-by-step correct way,
>BUT it have a serious BUG VFP skip to optimize the first two Tables !!,
> where the SQL syntax drive the steps:
>

>- load the first join table
>( if on the WHERE exist a left part of a condition that match to an table1's tag key it load and NOT uses the index tag,
>this create a loss of time )
>- load the second join table
>( if on the WHERE exist a left part of a condition that match to an table2's tag key it load and NOT uses the index tag,
>this create a loss of time )
>- join the intermediate result1 with intermediate result2
>( if on the ON exist a condition that match to an table2's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>- load the third join table
>( if on the WHERE exist a left part of a condition that match to an table3's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>...
>

>
>
>without FORCE VFP have another serious issue,
>it uses this logic:
>because it want reorder the joins in COUNT(*) Ascending order
>( this minimize the joins loop cost ), it uses this:
>

>- load the first join table
>( if on the WHERE exist a left part of a condition that match to an table1's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>- load the second join table
>( if on the WHERE exist a left part of a condition that match to an table2's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>- load the third join table
>( if on the WHERE exist a left part of a condition that match to an table3's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>- load the ...
>...
>- reorder intermediate result in COUNT(*) order
>- join the first intermediate result with the second intermediate result
>( if on the ON exist a condition that match to an table2's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>- join the current join intermediate result with the third intermediate result
>( if on the ON exist a condition that match to an third intermediate result table's tag key it load and uses the index tag,
>this can speed up the job or it can to be a loss of time )
>

>
>Then
>- without FORCE and network tables, VFP load all the tables into the local cache,
> if the where condition is not very selective, this can require a lot of time
>because the ON condition doesn't filter any records at this point !
>( this explain 34 sec. )
>- with FORCE VFP have a bug and doesn't filter Employee_Queue_Schedules.
>
>Then, without FORCE uses derivate subquery to force the STEP BY STEP good order:
>

>select cTrans_pk, step1.cUsGrlink_pk ;
>	from Trans;
>         inner join ;
>           (SELECT TEQ.cTrans_fk,cCommission_Owner_UsGrLink_fk as cUsGrlink_pk   FROM Employee_Queue_Schedules EQS ;
>              inner join Trans_Employees_Queues TEQ;
>                    on TEQ.cTrans_Employees_Queues_pk = EQS.cTrans_Employees_Queues_fk
>              WHERE EQS.iActive_Flag = 1 and EQS.tScheduled_time < {^2006-05-03 00:00:00};
>                 AND TEQ.iActive_flag = 1) step1 ;
>         on Trans.cTrans_pk = step1.cTrans_fk ;
>    where Trans.cResolution_Codes_fk is Null
>
>

>
>with FORCE EXIST A WORKAROUND
>VFP optimize where conditions into the ON clause
>

>select cTrans_pk, cCommission_Owner_UsGrLink_fk as cUsGrlink_pk ;
>    from FORCE Employee_Queue_Schedules EQS ;
>      inner join Trans_Employees_Queues TEQ on TEQ.cTrans_Employees_Queues_pk = EQS.cTrans_Employees_Queues_fk ;
>         AND EQS.iActive_Flag = 1 and EQS.tScheduled_time < {^2006-05-03 00:00:00} ;
>        and TEQ.iActive_flag = 1 ;
>        inner join Trans on Trans.cTrans_pk = TEQ.cTrans_fk ;
>        where Trans.cResolution_Codes_fk is Null
>

Fabio,

That's a great explanation. I'm going to try both ways. Too bad I can not give you five stars for this.