Quite complicated, indeed.

First, you should check whether each of the fields in the criteria, individually, matches few records in the result, or matches a lot. If it matches a lot of records, it is better NOT to have an index.

Second, if I understand correctly, it seems that you are joining several tables at once. If the data is R/O (like, for a report), it is often much faster to join two tables, join the result with a third table, etc. The difference in time is sometimes enourmous.

If you don't want to delete indices, you might want to select records for a condition where only a few records are selected (like, the unassigned records), and then filter the result on additional conditions.

>Here it is. I've guessed it's the most time consuming piece, but I need to verify it by some tests:
>
>

>	lparameters tnNumAccounts, tcUserID, tcQueuePk, ;
>		tyLowB, tyHighB, tcAdditionalFilter
>
>* Get the unassigned accounts
>	local lcSQL, ldStartDate, lcFutureDate, lcAdditionalJoins
>
>	ldStartDate = this.dStartDate
>	lcFutureDate = TimeToStr(datetime(3000,1,1))
>	lcAdditionalJoins = ""
>
>	if not empty(m.tcAdditionalFilter) and 'ccarriers_fk' $ lower(m.tcAdditionalFilter)
>		text to lcAdditionalJoins noshow pretext 7
>		 INNER JOIN Trans_Insurances on ;
>		 Trans.cTrans_pk = Trans_Insurances.cTrans_fk
>		 INNER JOIN Insurances on ;
>		 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
>		 INNER JOIN Carrier_Branches on ;
>		 Insurances.cCarrier_Branches_fk = Carrier_Branches.cCarrier_Branches_pk
>		ENDTEXT
>	endif
>
>	if used('curAccountsToAssign')
>		tcAdditionalFilter = strtran(m.tcAdditionalFilter,"Trans.","",-1,-1,1)
>		lcAdditionalJoins = strtran(m.lcAdditionalJoins,"Trans.","",-1,-1,1)
>		text TO lcSQL TEXTMERGE NOSHOW PRETEXT 7
>				select top <<m.tnNumAccounts>> *, 0 as iAssigned ;
>					from curAccountsToAssign ;
>					<<m.lcAdditionalJoins>> ;
>					where between(yCurrent_Balance_Amount, <<m.tyLowB>>, <<m.tyHighB>>) ;
>					AND cQueue_Names_fk = '<<m.tcQueuePk>>'
>		ENDTEXT
>	else
>		text TO lcSQL TEXTMERGE NOSHOW PRETEXT 7
>				select top <<m.tnNumAccounts>> trans.cTrans_pk, ;
>					NVL(Employee_Queue_Schedules.tScheduled_Time, <<m.lcFutureDate>>) as tScheduled_Time, ;
>					Trans_Employees_Queues.cTrans_Employees_Queues_Pk, 0 as iAssigned, ;
>					space(16) as cCommission_Owner_UsGrLink_fk, ;
>					space(16) as cQueue_names_fk ;
>					from trans INNER join Trans_Employees_Queues ;
>					on trans.cTrans_pk = Trans_Employees_Queues.cTrans_fk ;
>					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 ;
>					<<m.lcAdditionalJoins>> ;
>					where Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk IS NULL ;
>					and Trans_Employees_Queues.cQueue_names_fk = '<<m.tcQueuePk>>' ;
>					and trans.cDepartment_Code like '<<this.cDepartment_Code>>' ;
>					and trans.cResolution_Codes_fk is NULL ;
>					and	Trans.iAccount_locked_flag = 0 ;
>					and between(trans.yCurrent_Balance_Amount, <<m.tyLowB>>, <<m.tyHighB>>) ;
>					and Trans_Employees_Queues.iActive_Flag = 1
>		ENDTEXT
>	endif
>
>	lcSQL = m.lcSQL + iif(not empty(m.tcAdditionalFilter), ;
>		" AND " + m.tcAdditionalFilter,"") + ;
>		" AND tExpire_date < " + DateToStr(m.ldStartDate) + ;
>		" order by tScheduled_Time, tDate_Received"
>

>
>Notice the m.tcQueuePk parameter. We have ~100K records with ~100-200 of UNASSIGNED BILLER queue.