Kevin,

>This is driving me nuts.
>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

Such performance is abysmal, even with no tricks at all. Makes me wonder if perhaps the setup/base data is very bad. As you specifically test un-cached data, you are measuring the time to read the cdx for all 4 tags (hence Tamar's hint about _deleleted and mine to try with a structure avoiding some tags) and move to some of the records. If memo fields are selected in one or both tables as well, it will add a bit to move inside the fpt as well. If you have really only this query, you could create a special index encompassing all filters - but if you go that way, optimizing just a few queries will give an abundance of sometimes overlapping indeces, which will definitely hurt your insert and probably your update/replace performance. ONLY in very special cases and small doses recommended!

Running on a 2.5' HD will make it slower - could it be that your partition/drive is HEAVILY fragmented and nearly filled to capacity ? Any files written then will be scattered all over the HD incurring inordinate amounts of time to always jump to the next sector. Defrag the partition before measuring cold times. Or a bad sector might slow you down. I am guessing that both tables including cdx have a size of about 30 MB, which a current 2.5 HD should read per second if the head doesn't have to jump.

>In testing, the second and third time the queries are faster,
How much ?

>but the moment I put it in production and the web server is connecting to the production server, I always get the slower times.

The vfp component called by the web server should run on the same box where the data lies. So vfp component *and* .dbf should be on the production server - otherwise you'll always incur LAN-speed hits on Rushmore and a client-server approach with a real server database would be the technological recommendation. What/How are you exactly measuring ? Factored out round-trip-time of Web Connect ?

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

regards

thomas