>>Hi everybody,
>>
>>This is the code in the VFP database stored procedure. It takes ~30+ sec. to execute on the Network database. Do you see ways to make it quicker?
>>
>>

>>select count(trans.cTrans_pk) as iAccounts_Count, ;
>>	step1.cUsGrLink_pk, ;
>>	sum(trans.yCurrent_balance_amount) as yBalance ;
>>	from trans ;
>>	INNER join ;
>>	(select TEQ.cTrans_fk, ;
>>	iif(not isnull(TEQ.cCommission_Owner_UsGrLink_fk), ;
>>	TEQ.cCommission_Owner_UsGrLink_fk, ;
>>	TEQ.cUsGrLink_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.tScheduled_time <= m.ltTime  ;
>>	and TEQ.iActive_Flag = 1 ;
>>	and EQS.iActive_Flag = 1  and ;
>>	iif(not isnull(TEQ.cCommission_Owner_UsGrLink_fk), .t., ;
>>	(TEQ.iTemporary_Assignment_Flag = 1 and ;
>>	TEQ.tStart_Date <= m.ltTime and ;
>>	TEQ.tExpire_Date >= m.tdDate))) step1 ;
>>	on trans.cTrans_pk = step1.cTrans_fk ;
>>	where trans.cResolution_Codes_fk is null ;
>>	group by 2 into cursor (m.tcAlias) readwrite
>>

>>
>>Thanks a lot in advance.
>
>hmmph, I thought in VFP9 one couldn't use group by ordinal number anymore.
>Anyway, if you split this into 2 seperate SQL-statements by saving one of the inner joins to an intermediate table, is there a time difference ? This would also help you in finding places to tweak performance.
>Also, what indexes are in place?

I'll try to split it up, though it's done by SQL engine anyway the way it's written. Since now it's SP, I can break it to pieces. Originally it was in a class code, so I didn't want to use temp cursors.

Borislav helped to create it as one select instead of using UNION, but now I'm thinking of trying to switch to UNION again. The second portion should not return results, because we rarely use temp owner assignments.