Hi Gregory,

I think, it works fine right now, so I am not sure, if further optimization is required.

>Nadya,
>
>If v_QuickSearch is a view with parameters you can further optimize the first select, ie
>

>use v_QuickSearch in 0 nodata
>select v_QuickSearch
>
>&& do the test twice, once without the indexes and the second time with the indexes.
>&& look at the Rushmore output
>
>&& create an index for all fields that will be used in the join expressions
>index on cTrans_PK tag cTrans_PK
>index on .....
>
>ViewParameter1 = ...
>ViewParameter2 = ...
>...
>ViewParametern = ...
>
>=Requery()  && pull the data in
>
>&& for the test
>set deleted Off
>=sys(3054,11)
>
>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
>
>
>=sys(3054,0)
>

>________________________________________________________________________________________________________________
>
>>>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