WHERE tExpire_date < ...
tExpire_date is field from Step1 right?

>Ok, here is what I've tried, that seems to slightly reduce the execution time on my local PC:
>

>select top 200 trans.cTrans_pk, ;					
>space(16) as cCommission_Owner_UsGrLink_fk, ;
>space(16) as cQueue_names_fk, ;
>0 as iAssigned, ;
>step1.tScheduled_Time, ;
>step1.cTrans_Employees_Queues_Pk, ;					
>step1.tExpire_date ;
>from trans INNER join ;
>(select ;
>NVL(Employee_Queue_Schedules.tScheduled_Time, {^3000-01-01 00:00:00}) ;
>as tScheduled_Time, Trans_Employees_Queues.tExpire_date, ;
>Trans_Employees_Queues.cTrans_Employees_Queues_Pk,  ;
>Trans_Employees_Queues.cTrans_fk from Trans_Employees_Queues ;
>LEFT JOIN Employee_Queue_Schedules ON ;
>Trans_Employees_Queues.cTrans_Employees_Queues_pk = ;
>Employee_Queue_Schedules.cTrans_Employees_Queues_fk ;
>and Employee_Queue_Schedules.iActive_Flag = 1 ;
>where Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk IS NULL ;
>and Trans_Employees_Queues.cQueue_names_fk = 'E06FDF1EC96545BF' ;
>and Trans_Employees_Queues.iActive_Flag = 1) step1 ;
>on Trans.cTrans_pk = step1.cTrans_fk ;
> ;					
>where Trans.cDepartment_Code ='O' and Trans.cResolution_Codes_fk is NULL and Trans.iAccount_locked_flag = 0					
> AND Trans.cProduct_Line_Code NOT BETWEEN "600" AND "604"
> AND tExpire_date < {^2006-06-21}
> order by tScheduled_Time, tDate_Received
>

>
>Using index tag Ccommis_fk to rushmore optimize table trans_employees_queues
>Using index tag Cqueuen_fk to rushmore optimize table trans_employees_queues
>Rushmore optimization level for table trans_employees_queues: partial
>Rushmore optimization level for table employee_queue_schedules: none
>Joining table trans_employees_queues and table employee_queue_schedules using index tag Cempque_fk
>Using index tag Crescod_fk to rushmore optimize table trans
>Using index tag Cproductcd to rushmore optimize table trans
>Rushmore optimization level for table trans: partial
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and table trans using index tag Ctrans_pk
>
>
>>>Joining intermediate result and table employee_queue_schedules (Cartesian product)
>>
>>BING BING
>>
>>Probably the bottleneck - Sorry, the query is to long to grok fast.
>>BUT: I've rearranged similar joins where vfp thought cartesian profuct was needed
>>into 2 or 3 "filtered" selects,
>>(rerunning the second query part and adding a
>>where pk not in (Select pk from previos select_result
>>describes "filtered")
>>and the speed gains were sometimes drastic on tables both near the 2 gig border.
>>
>>Perhaps more elegant solutions would have been possible by defining specialized
>>indices, but tests were too time consuming - good enough for that use case.
>>
>>Hint: "Split" your code so that 2 queries run:
>>One to create the intermediate result as a cursor or table
>>One to Join it with employee_queue_schedules
>>
>>Probably the second query will take most of the time, whereas the first query will be relatively fast. *If so*, you have
>>a) provably identified the botttleneck inside ther query (Just time it)
>>b) reduced the complexity of the bottlenck
>>c) testing alternatives for the second join should be easier
>>
>>Hint: Build a Start Scenario and work of programmatic versions first to get the expressions correct by working of opened tables and the text/code from sys 3054. Works better for me for experimenting...
>>
>>Just having both tables physically to inspect and tweak the SQL for the last join helps as well as the possibility to query for selectiveness of criteria.
>>
>>regards
>>
>>thomas