Gene,

To expand a little on what Andrew said, when VFP runs SQL the optimizer actually builds temporary indexes on whatever fields you have in the WHERE clause that don't have indexes already if it thinks they will be useful for selecting the data.

The second time around these indexes are already built.

You can help FoxPro be faster by putting indexes on all the fields in your WHERE clause, but the index must exactly match the requested data. Example: WHERE UPPER(MyField)... - index on UPPER(MyField) not just MyField.

You don't, however, want indexes on fields that have only a few discrete values. An example of this is gender, and of course DELETED(). This is because FoxPro copies all the indexes to the local machine when it begins the SQL. It's faster to evaluate a few result records for their DELETED() property than to copy the whole index.

If you're testing your SQL with and without indexes to find the right combination, be sure to close out of FoxPro between tests to eliminate the cacheing.


>I have an application that uses several 50k to 450k record tables and there are many different SQL Select statements. When these SQL Select statements are executed the very first time, it takes up to 4 times as long as it does every time after that. Any suggestions???