>>if you remove the where filter, how many records return the query ?
>
>I removed the indexes on iActive_Flag since they didn't change the query performance. Also it looks like FORCE did speed it up. As I said, the results are not really conclusive. If I do this program twice, the first time it executes in 34 sec. ( without FORCE clause), the second time in the same VFP session it takes 2 sec.
>
>If I close VFP the query bellow takes 15 sec.
>
>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 ;
> inner join Trans on Trans.cTrans_pk = TEQ.cTrans_fk ;
> where EQS.iActive_Flag = 1 and EQS.tScheduled_time < {^2006-05-03 00:00:00} ;
> and TEQ.iActive_flag = 1 ;
> and Trans.cResolution_Codes_fk is Null
>
>&tcSql
>Rushmore optimization level for table eqs: none
>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
>
>
>Without FORCE it takes 34 sec.
>
>I'm running right now from the network database and there are no users in the system.
When VFP execute a network query, it allocate a lot of cache levels:
- remote hd cache
- remote hd controller cache ( if exists )
- remote OS file system cache ( if enabled )
- remote CPU cache ( HW size )
- local OS file system cache ( if enabled )
- local VFP application cache ( SYS(3050) )
- local CPU cache ( HW size )
This makes not deterministic the run-time,
and with share data, a lot of bugs exists.