>>>I have been given an application that has about 400,000 records in two tables and then a few more tables scattered around. It runs fairly quickly on one machine but if you put it out on the server and then the people around the state hit on the application (only about 5 people at a time max) it is incredibly slow.
>>>
>>>The client believes that Foxpro's native setup whenever doing a SQL statement is to pull the entire contents of the tables across the network into local memory and then do the SQL statement. Is this true? What can I do to make the SQL statement happen on the server and only the results be sent to the client?
>>>
>>>If you need more information (and I'm sure you probably do) please let me know. Thanks so much in advance for any assistance you can give.
>>
>>Denise --
>>
>>VFP is a file server not a client server environment. But, VFP does optimize its performance within the constraints of that design.
>>
>>A SELECT does pull down information from the server. But, if the query is appropriately Rushmore optimized, it requires only the indexes which match join and filter conditions in the statement. Without full Rushmore optimization, VFP will pull down records themselves for comparison, although in some situations that does provide the best performance, when the result set has already been filtered.
>>
>>The best place to start is reading the documentation on Rushmore technology: in "Understanding Rushmore Technology" in Chapter 15, "Optimizing Applications," in the Programmer's Guide.
>>
>>Check the SYS (3054) function to determine what level of Rushmore optimization the query is at right now. That will help analyzing how to go. Do note that full Rushmore optimization may not give the best performance -- and that shows up in indexes that do not offer high discrimination between records (ZIP code offers more discrimination than does a field like Hired). But, much of this depends on data distribution, network, etc. so experimenting with different options will give the best results for your specific situation.
>>
>>To improve, consider the following strategies:
>>
>>1. Create an index on all values used in join conditions. Make sure that the index expression matches EXACTLY the expression used in the join condition.
>
>The help states exactly this: the index expression has to match exactly the expression used in the join condition - or else, the index won't be used. What the help doesn't state is that this can be good or bad, depending on the circumstances.

>
>>3. If you use the environmental setting of SET DELETED ON, try an index on DELETED() to see if that helps or hurts.
>
>The index on DELETED() does NOT offer a high level of discrimination, and is therefore (usually) counterproductive, especially for large tables over a network.
>
>The first thing I recommend, for improving performance, is to check whether this index exists and to get rid of it.
>
>Hilmar.

Hilmar --

Your FAQ does a nice job of covering the various issues in some detail.

I don't think that you've read my post, though, in the spirit in which its intended.

First, I would always put a filter on a join condition as these are generally the most discriminating fields. I can see an exception for some lookup tables with low discrimination.

Secondly, I was trying to provide some guidelines for experimentation. I know that there are situations in which low discriminating expressions, including on deleted, do offer a performance benefit. It really is up to the VFP developer to have general guidelines to tailor to their specific case.

Jay