Optimising Views and CursorAdapters
There are many considerations when optimising Visual FoxPro for client-server operation. The art to Client-Server optimisation is to ensure that the minimum amount of information is sent to the workstation with the minimum number of transactions. FoxPro has many features designed to help optimise the transactions.
Some of the optimisations result from common sense or good database design whereas others involve detailed setup of the FoxPro components used in client-server computing or use of server functionality to perform processing on the server.
Remote Views are usually defined with the View Designer or may be defined with the CREATE VIEW command. Remote Views are essentially SQL SELECT statements and care is required to ensure that the indexes and other performance features are set up correctly for maximum performance on the server. If the server is taking too much time to return the query the workstation cannot speed up.
The Advanced Options for a View can be used to specify options that affect performance at the Workstation but do not alter any server side characteristics.
Remote View Advanced Options
The number or records to fetch at a time (FETCHSIZE) allows FoxPro to perform a progressive fetch for an asynchronous query. Control returns to the application after the specified number of records have been received from the server and the remainder are received in the background. This value might be set lower when running an application over a modem line.
The Connection should be asynchronous for the number of records to fetch at one time to apply. A synchronous query will retrieve all the records at once before returning control to the application.
The maximum number of records (MAXRECORDS) will prevent the Workstation from receiving more records than specified. Note here that some servers will carry on processing regardless but the workstation process will stop after receiving the number of records specified.
The Fetch Remote Data As Needed option (FETCHASNEEDED) modifies the behaviour of the progressive fetch so that processing does not automatically continue in the background and records are only fetched from the server when requested by the application.
Too many memo fields can clog up network traffic and the Fetch Memo option (FETCHMEMO) allows memo fields to be brought over from the server only when the required field is active in the View. The character length for the memo fetching to operate can also be determined.
Some remote views, especially parameterised views, may be executed repeatedly in an application. The server will interpret the SQL statement passed by the remote view each time the view is requeried. The Precompile SQL on Backend Server option (PREPARED) will allow the SQL statement to be compiled on the server and run faster on subsequent requeries. Not all Servers will support precompilation however.
The properties defined for a Remote View may also be defined with the DBSETPROP command as an alternative to the Advanced Options window in the Database Designer.
The Advanced Options correspond to the following database container properties for a view:
The PREPARED property is important especially for parameterised views that are called repeatedly. SQL Server allows SQL statements to be compiled on the server so that performance is faster on subsequent re-use. Performance on subsequent requeries of a parameterised view will increase if a parameterised view is prepared on the server the first time it is executed.
DBSETPROP( 'authorscontractview', 'view', 'prepared', .T. )
In addition, the BATCHUPDATECOUNT can be used to automatically batch a number of transactions before sending them to the server. In a high transaction environment, this allows several updates to be processed with a single transaction over the network transaction and can help reduce network traffic.
The transactions sent to the server can be controlled programmatically by setting table level buffering and using TABLEUPDATE() to update the server as required.
There are other properties of a view and of fields in a view which can be useful in determining the structure of the remote view. These are documented in the help under the DBSETPROP topic.
CURSORSETPROP can be used to alter some of these properties after the view has been opened.
The following recommendations for initial remote view settings should cover most situations and ensure adequate performance:
· SendUpdates should be true.
· Shared Connection.
· Update key and modified fields.
· Asynchronous Queries under careful program control (remember to SQLCANCEL before running a subsequent query) or synchronous with careful use of the timeouts to protect against runaway queries.
· 100 Records fetched at a time (10 for dial up access).
· FetchMemo to retrieve memos only when required.
· Do not CompareMemo on update.