Hi,

This is driving me nuts.

I'm trying to perform a query that ultimately will run on a Web Connect server app, but right now I'm just running the query on my local, no access being made to a server.

It's a two table join between a Firm table and an Orders table (SRO), with the below stats:
Firm Table: 14,462 records
SRO Table: 92,574 records

My computer is a Pentium 4 Toshiba Notebook running at 3.4 ghz, with 1gb of RAM.

Records Returned: 10
Duration: 9 seconds
FiltersApplied: lcSelection= sro.Case_id ='0000006913'

SELECT firm.firm_id as Firm_Firm_ID, UPPER(firm.name) as Firm_Name, firm.address as Address, ALLTRIM(firm.city) as City,;
 firm.State as State, ALLTRIM(firm.Zip) as Zip, MAX(sro.client) as Client, firm.phone as Phone, firm.fax as Fax,;
 MAX(sro.srref) as SRRefNo, sro.sro_id as sro_id, sro.case_id as case_id 
FROM Analyzer!sro 
  JOIN analyzer!firm ON sro.firm_id = firm.firm_id AND 
       INLIST(sro.sro_status,10,20,30,33,40) AND sro.type = "I" 
WHERE &lcSelection 
GROUP BY 1, 2, 4, 5, 6, 8 
ORDER BY 2 asc, 4 descend 
INTO CURSOR curFirmsWithOrders readwrite

As you can see from the results of the sys(3054,12) output below, there's an index on:
The SRO and Firm Firm_id fields
The SRO Case_ID field
The SRO_Status field
The SRO Type field

Sys(3054,12) Output

Using index tag Sro_status to rushmore optimize table sro
Using index tag Type to rushmore optimize table sro
Using index tag Case_id to rushmore optimize table sro
Using index tag _del to rushmore optimize table sro
Rushmore optimization level for table sro: full
Using index tag _del to rushmore optimize table firm
Rushmore optimization level for table firm: full
Joining table sro and table firm using index tag Firm_id

I have SET ENGINEBEHAVIOR to 7, but now that I've got the grouping of all the fields, maybe I should switch it to 9 and see if that helps.

I need to use the result set for further queries and also insertion into this table of additional records based on those additional queries and so that's why I'm using the READWRITE clause.

Two of the other queries are slow and so when I put them all together, the method takes about 25 + seconds to get the web page redisplayed.

I can't imagine that it should take 9 seconds for returning only 10 records on such a simple query.

Thanks

PS Anyone have a quick way to reduce the impact of VFP's caching so when I do repeated tests of the query above and others like it, it's working in as close to a "virgin" cache state as possible? In testing, the second and third time the queries are faster, but the moment I put it in production and the web server is connecting to the production server, I always get the slower times.