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

group into a subquery, and uses IN ()

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, Client, firm.phone as Phone, firm.fax as Fax,;
 SRRefNo,  sro_id,case_id 
FROM (SELECT firm_id , MAX(sro.client) as Client
            , MAX(sro.srref) as SRRefNo
            , sro.sro_id, sro.case_id 
           FROM Analyzer!sro WHERE sro.sro_status IN (10,20,30,33,40) AND sro.type = "I" AND &lcSelection GROUP BY 1) sro
  JOIN analyzer!firm ON sro.firm_id = firm.firm_id 
ORDER BY 2 asc, 4 descend 
INTO CURSOR curFirmsWithOrders readwrite