Thomas,

First off, thanks for trying to help, I really appreciate it.

"Any chance of sending the data ? Probably faster than guessing about selectivity and distributions."

In a heartbeat, not real familiar with all the features of UT, what's the best way to get it to you?

There are multiple queries, so I don't think the "special index" is a good route.

I tried your modification of the INLIST and that didn't appear to help.

I'm getting the same results when I'm local or when I'm on the network (with the virgin cache). I was thinking of moving the EXE off the locally hosted web server, but then I'm running it on my local hd on my notebook and still getting these times, so I'm not sure that's the going to solve the problem either.

I haven't defragged my HD in a while, there's currently 3.85 gb free out of 100 gb.

The speed drop on cached data will go down to about 0 to 1 second from the 7 to 9 (you can probably feel my frustration <g>). And there's a series of queries to get the final results, and they have similar times so it takes anywheres from 25 to 35 seconds to return the web page.

The bad sector issue I think is eliminated by the fact I'm getting very similar results on multiple computers, one of which is my laptop, the others are a Windows 2003 server and a Novell server (whatever the latest version is) running on 100mb LAN with the latest CAT cabling utilizing switches.

The Firm table's CDX is 1,235kb, with a 5,241kb FPT and 10,030kb DBF
The SRO table's CDX is 12,225kb with a 82,494 FPT and 72,055 DBF

To measure the SQL speed, I've written a routine that I call immediately before and immediately after the SQL Select, similar this below:

PROCESS.SQLAnalysis(PROGRAM(),JUSTEXT(PROGRAM()) + [_FirstSelect2],LINENO(1),.F.,[FiltersApplied: None Applied],[SQLAnalysis.htm])

The SQLAnalysis program is:

*******************************************************************
FUNCTION SQLAnalysis()
*******************************************************************
* Method to track the efficiency of an SQL Select by 
* using Rushmore Optimization
* Sample Call:
*
* PROCESS.SQLAnalysis(PROGRAM(),JUSTEXT(PROGRAM()) + [_FirstSelect],LINENO(1),.F.,[],[])
*     <SQL SELECT TO ANALYZE>
* PROCESS.SQLAnalysis(PROGRAM(),JUSTEXT(PROGRAM()) + [_FirstSelect],LINENO(1),.T.,[FiltersApplied: lcSelection= ] + lcSelection,[SQLAnalysis.htm])	
********************************************************************
LPARAMETERS tcMethod, tcSelectName, tnLineNo, tlWriteToFile,tcFiltersApplied,tcFileName
LOCAL lcProperty, lnRushmoreType, lcDuration, lcHTMLHeader, lcHTMLFooter
*
* [*] in cCompanyName indicates to do the analysis otherwise don't do the 
* analysis at all
*
IF [*] $ gowcserver.cCompanyName
	*
	* Add the Property for the SQL being tested and set value to 
	* current time
	*
	lcProperty = [this.] + tcSelectName
	IF VARTYPE(&lcProperty) = [U]
		*
		* Property doesn't exist, add it and use 
		* Sys(2) to set it to the value
		* of the number of seconds since Midnight 
		*
		ADDPROPERTY(This,tcSelectName,SYS(2))
	ENDIF
	IF tlWriteToFile = .F.
		*
		* Turn on the Rushmore Optimization Assessment
		*
		PUBLIC tcSqlSelect
		*
		* The number of [*]'s in CompanyName indicates
		* the type of analysis to do
		*
		DO CASE
		CASE [****]  $ gowcserver.cCompanyName
			lnRushmoreType = 12
		CASE [***]  $ gowcserver.cCompanyName
			lnRushmoreType = 2
		CASE [**]  $ gowcserver.cCompanyName
			lnRushmoreType = 11
		OTHERWISE
			lnRushmoreType = 1
		ENDCASE
		=SYS(3054,lnRushmoreType,"tcSqlSelect")
	ELSE
		lcProperty = [this.] + tcSelectName
		*
		* Calculate duration by subtracting current Sys(2)
		* value from that value stored in the custom 
		* property
		*
		lcDuration = ALLTRIM(STR(VAL(SYS(2)) - VAL(&lcProperty))) 
		*
		* Going with all files an htm file, force it if legacy call has [.txt]
		*
		IF PCOUNT() <> 6
			tcFileName = ALLTRIM(This.oCurrentUser.oData.in_login) + [_SQLAnalysis.htm]
		ELSE
			tcFileName = ALLTRIM(This.oCurrentUser.oData.in_login) + [_] + ALLTRIM(tcFileName)
			tcFileName = STRTRAN(tcFileName,[.txt],[.htm])
		ENDIF
		
		IF !FILE(goWCServer.cDataPath + tcFileName)
			*
			* First Time creating the Analysis file for the user
			* Prepend the Header information which includes
			* CSS styles for colorizing Rushmore analysis results
			*
			lcHTMLHeader	= 	This.CreateAnalysisHeader()
			lcHTMLFooter		= 	[</body></html>]
		ELSE
			lcHTMLHeader	= []
			lcHTMLFooter		= []
		endif
		*
		* StrTran results to get aesthetics in htm file
		*	
		tcSqlSelect=STRTRAN(tcSqlSelect,CHR(10),[<br>])
		tcSqlSelect=STRTRAN(tcSqlSelect,[: full],[:<span class="full"> full</span>])
		tcSqlSelect=STRTRAN(tcSqlSelect,[: none],[:<span class="none"> none</span>])
		tcSqlSelect=STRTRAN(tcSqlSelect,[: partial],[:<span class="partial"> partial</span>])
		*
		* Write results to file
		*
		STRTOFILE(	IIF(EMPTY(lcHTMLHeader) ,[<br>], lcHTMLHeader + [<br>]) + ;
						[Current User: ] + ALLTRIM(This.oCurrentUser.oData.in_login) + ;
						[<br>] + ;						
						[User Type: ] + ALLTRIM(this.oCurrentUser.oData.in_type) + ;
						[<br>] + ;
						[ALIAS: ] + IIF(_tally = 0,[No Records Returned],ALLTRIM(ALIAS())) + ;
						[<br>] + ;
						[Records Returned: ] + IIF(_tally = 0,[No Records Returned],ALLTRIM(STR(RECCOUNT(ALIAS())))) + ;
						[<br>] + ;
						[DateTime: ] + TTOC(DATETIME()) + [<br>] + ;
						[Method: ] + ALLTRIM(tcMethod) 	+ [<br>] + ;
						[Line No: ] + ALLTRIM(STR(tnLineNo)) + [<br>] + ;
						[Duration: ] + lcDuration + [<br>] + ;
						tcFiltersApplied + [<br>] + ;
						[Select: ] + tcSelectName + ;
						tcSqlSelect + [<br>], goWCServer.cDataPath + tcFileName,1)
		RELEASE tcSqlSelect
		=SYS(3054,0)
		REMOVEPROPERTY(this,tcSelectName)
	ENDIF
ENDIF
ENDFUNC

Which gives me an HTML file containing each SQL's analysis in the below format:

Current User: JJB
User Type: SP
ALIAS: CURSROORDERS
Records Returned: 1432
DateTime: 10/10/2006 12:03:49
Method: ARFS.BUILDPREVIOUSORDERS
Line No: 212
Duration: 7
FiltersApplied: lcSelection= sro.Case_id ='0000006913'
Select: BUILDPREVIOUSORDERS_FirstSelect
SELECT sro.firm_id as Firm_ID, sro.sro_id as sro_id, 
sro.case_id as case_id, MAX(sro.client) as Client, 
MAX(sro.srref) as SRRefNo 
FROM Analyzer!sro 
WHERE &lcSelection AND INLIST(sro.sro_status,10,20,30,33,40) AND sro.type = "I" GROUP BY 1, 2, 3 INTO CURSOR curSROOrders readwrite
Using index tag Case_id to rushmore optimize table sro
Using index tag Sro_status to rushmore optimize table sro
Using index tag Type to rushmore optimize table sro
Using index tag _del to rushmore optimize table sro
Rushmore optimization level for table sro: full

Sorry for the long post, and again, thanks for any help. Hopefully the knowledge I gain on the first select will help in the others.

Kevin