Hi Kevin

Try to grab the SRO records first since it seems your filtering is being applied to sro. You might then not need max(). Then join the results with firm.

Also don't filter sro in the join. You're joining firm to sro, so only include filters that apply to firm in the join. I'd move the sro filters from the join to the where.

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