>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
>
>
>
3 seconds after re-loading VFP
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 into cursor curTest
Using index tag Tschedtime to rushmore optimize table eqs
Rushmore optimization level for table eqs: partial
Rushmore optimization level for table teq: none
Joining table eqs and table teq using index tag Cempque_pk
Using index tag Crescod_fk to rushmore optimize table trans
Rushmore optimization level for table trans: full
Rushmore optimization level for intermediate result: none
Joining intermediate result and table trans using index tag Ctrans_pk
>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
>
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 into cursor curTest
Using index tag Tschedtime to rushmore optimize table eqs
Rushmore optimization level for table eqs: partial
Rushmore optimization level for table teq: none
Joining table eqs and table teq using index tag Cempque_pk
Rushmore optimization level for intermediate result: none
Using index tag Crescod_fk to rushmore optimize table trans
Rushmore optimization level for table trans: full
Joining intermediate result and table trans using index tag Ctrans_pk
Also takes 3 seconds.
I was testing with my local tables.
If it's not broken, fix it until it is.
My Blog