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

2. Create an index on all filter conditions where the filter offers a high level of discrimination.

3. If you use the environmental setting of SET DELETED ON, try an index on DELETED() to see if that helps or hurts.

Jay