>But, VFP9 uses,
only, indexes filtered with FOR [NOT] DELETED() in the SQL command,
>but not in the XBase commands (I consider this a bug)
>
>If iActive_Flag = 1 are very selective (at least 50%) then you can try to add a binary index
>( this reduce the network traffic and speedup the cpu work because reduce the cpu's cache overload )
>
>
>
>SELECT Trans_Employees_Queues
>INDEX ON iActive_Flag = 1 TAG Active BINARY
>
>-- and/or
>
>SELECT Employee_Queue_Schedules
>INDEX ON iActive_Flag = 1 TAG Active BINARY
>
>-- and/or but vfp9 have a bug here !
>
I've added these two indexes.
It seems to speed up the process, however, I still get the same statistics:
SELECT Trans_Employees_Queues.cTrans_Employees_Queues_pk , ;
Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk as cUsGrlink_pk ;
FROM Trans_Employees_Queues ;
INNER JOIN Employee_queue_schedules ;
ON Trans_employees_queues.cTrans_employees_queues_pk = ;
Employee_queue_schedules.cTrans_employees_queues_fk ;
inner join Trans on Trans_Employees_Queues.cTrans_fk = Trans.cTrans_pk ;
WHERE Trans_Employees_Queues.iActive_Flag = 1 ;
AND Employee_queue_schedules.tScheduled_time <= {^2006-05-03 23:59:59} ;
AND Employee_queue_schedules.iActive_Flag = 1 ;
and Trans.cResolution_Codes_fk is NULL ;
Rushmore optimization level for table trans_employees_queues: none
Using index tag Tschedtime to rushmore optimize table employee_queue_schedules
Rushmore optimization level for table employee_queue_schedules: partial
Using index tag Crescod_fk to rushmore optimize table trans
Rushmore optimization level for table trans: full
Joining table trans and table trans_employees_queues using index tag Ctrans_fk
Joining intermediate result and table employee_queue_schedules using index tag Cempque_fk
If it's not broken, fix it until it is.
My Blog