Query and View Acceleration
You can improve query and view performance by adding indexes, optimizing local and remote processing, and optimizing parameter expressions.
Adding Indexes to Remote Tables
Remote indexes can make queries significantly faster. Multiple-table queries are faster if the tables are indexed on the joining fields. Having indexes on fields that are included in a query's WHERE clause can also improve performance.
Clustered indexes provide the best performance. On SQL Server, each table can have one clustered index. The SQL Server Upsizing Wizard automatically creates clustered indexes on tables that had a primary key in Visual FoxPro.
Tip
While indexes on table fields used in queries can speed processing, indexes on result sets can slow performance. Use indexes on result sets with care.
Optimizing Local and Remote Processing
If you need to process a combination of local and remote data, create a remote view that combines all remote data in a single view. You can then join the remote view with the local data in a local view. Because Visual FoxPro fetches both views completely before joining and filtering the combined view, it's important to limit the size of the view result set.
You gain speed in remote processing by limiting the remote view result set to the minimum amount of data needed by your application. When you retrieve less data into a remote result set, you minimize the time required to download remote data into your local query or view cursor.
Optimizing Parameterized Views
You can speed data retrieval during REQUERY( ) operations on an open, parameterized view by compiling the view before it's executed. To precompile or "prepare" a view, set the Prepared property on the view to true (.T.).
Optimizing Parameter Expressions
View and SQL pass-through parameters are Visual FoxPro expressions and are evaluated in Visual FoxPro before being sent to the remote server. Evaluation time for the expression is important, because it lengthens the query execution time.
See Also
Concepts
Performance Improvement on Updates and Deletes