>Take out the scan. That is your best bet. Use the the scancursor in a select sql joining with cTrans_Pk or something
>
>As I said the requey() takes a looooooooong time.
>
>The best performance is without the scan
>
>You could optimize the scan by comparing cTrans_Pk or so to its previous value (even index the scan cursor before) If it's the same then you do not need to requery()

Hi Gregory,

The code bellow takes 0.094 sec. to execute for 85 recs. However, now I have another concern - in its current way my code is not SQL Server back-end compatible, right?

*---------------------- Location Section ------------------------
*   Library: 	Aquicksearchbiz.vcx
*   Class: 		Quicksearchobject
*   Method: 	CreateFinalResult()
*----------------------- Usage Section --------------------------
*)  Description:
*)

*   Scope:      Public
*   Parameters:
*$  Usage:
*$
*   Returns:
*--------------------- Maintenance Section ----------------------
*   Change Log:
*       CREATED 	01/24/2005 - NN
*		MODIFIED   *-- CHANGE - NN - January 25, 2005 - 14:31:02
*----------------------------------------------------------------
LOCAL lnReccount, loSelect, cStatus, cUserID, lnSeconds
lnSeconds = SECONDS()
SET TALK OFF
SET NOTIFY OFF

STORE "" TO cStatus, cUserID

IF VARTYPE(m.plStop) <> "L"
	LOCAL plStop
	plStop = .F.
ENDIF

SELECT v_QuickSearch.*, ;
	IIF(EMPTY(v_QuickSearch.cResolution_Codes_Fk) OR ;
	ISNULL(v_QuickSearch.cResolution_Codes_Fk), ;
	v_QuickSearch.cStatus_Codes_Fk, ;
	v_QuickSearch.cResolution_Codes_Fk) AS cVisCodes_Fk, ;
	Employee_queue_schedules.cemployee_queue_schedules_pk, USERS.cUserID ;
	FROM v_QuickSearch ;
	INNER JOIN mmVisCollect!Trans_employees_queues ;
	ON v_QuickSearch.cTrans_PK = Trans_employees_queues.ctrans_fk ;
	INNER JOIN mmVisCollect!usgrlink ;
	ON  Trans_employees_queues.ccommission_owner_usgrlink_fk = usgrlink.cusgrlink_pk ;
	INNER JOIN mmVisCollect!Employee_queue_schedules ;
	ON  Trans_employees_queues.ctrans_employees_queues_pk = Employee_queue_schedules.ctrans_employees_queues_fk ;
	INNER JOIN mmVisCollect!USERS ;
	ON  USERS.iid = usgrlink.iuserid;
	WHERE Trans_employees_queues.iactive_flag =  1 ;
	AND  Employee_queue_schedules.iactive_flag =  1 ;
	INTO CURSOR cTempResult

SELECT cTempResult.*, VisCodes.cCode_Description AS cStatus ;
	FROM cTempResult INNER JOIN mmVisCollect!VisCodes ;
	ON cTempResult.cVisCodes_Fk = VisCodes.cVisCodes_Pk ;
	INTO CURSOR cResult

USE IN SELECT('cTempResult')
SELECT c_QuickSearch
APPEND FROM DBF('cResult') FOR NOT m.plStop
USE IN SELECT('cResult')
GO TOP IN c_QuickSearch

IF m.plStop
	THIS.EmptySearchResult() && Search was cancelled
ENDIF

lnReccount = RECCOUNT("c_QuickSearch")

=MESSAGEBOX("Populating cursor took " + TRANSFORM(SECONDS() - m.lnSeconds) + " seconds")

RETURN m.lnReccount