Once you’ve implemented your client/server application, you might find areas where you'd like to improve performance. For example, you can fine-tune your application to gain maximum performance by speeding up forms and queries and increasing data throughput.
This chapter discusses optimization strategies for application performance on the client, network, and server. For information about implementing client/server applications, see earlier chapters in this book.
This chapter discusses:
Establishing a connection uses time and memory on both the client and the server. When you optimize connections, you balance your need for high performance against the resource requirements of your application.
The number of connections used by Visual FoxPro depends on whether you force the closing of unused connections, and how you set the length of the connection idle timeout.
You can use connections exclusively or share a connection. Each method has its benefits. When you use a connection exclusively, your application experiences no contentions for connection resources once a connection is established. If each result set uses an exclusive connection, you can also intermingle asynchronous processing on multiple result sets.
When you use a shared connection, you have one connection for multiple result sets. You must serialize data manipulation operations on the result sets sharing the same connection, and design the application to test the connection for busyness any time conflicts might occur. For information on sharing a connection, see Chapter 8, Creating Views.
If your application doesn’t take any action for a long time, you can reduce connection use by setting the IdleTimeout property on the connection. The IdleTimeout property controls the interval of time connections are allowed to idle before they’re closed by Visual FoxPro. By default, connections wait indefinitely and are not deactivated until specifically closed by the user.
You set the idle time for a connection definition with the IdleTimeout property of the DBSETPROP( ) function; you can set the IdleTimeout property for an active connection with the SQLSETPROP( ) function.
Visual FoxPro closes connections even if Browse windows and forms displaying remote data are still open, and then automatically reconnects when the connection is needed again. However, Visual FoxPro cannot close a connection if:
You set the transaction mode for a connection definition with the Transactions property of the DBSETPROP( ) function; you can set the transaction mode for an active connection with the SQLSETPROP( ) function.
You can improve performance by closing connections that your application is no longer using. Connections are closed automatically for you when you close a view. If the connection is shared by multiple views, Visual FoxPro closes the connection when the last view using the connection is closed.
You can control the connection for a query manually if you don’t want to update the data in a cursor. Use a SQL pass-through query to select the data you need into a local cursor and then close the connection.
You can speed up data retrieval by managing the number of rows fetched during progressive fetching, controlling fetch size, and by using delayed Memo fetching.
You can also use the UseMemoSize view property to return character fields as memo fields and then turn FetchMemo off to enable your application to selectively fetch those character fields converted to memo fields.
When you query a remote data source, Visual FoxPro retrieves complete rows of data and builds a Visual FoxPro cursor. To speed retrieval of remote data, Visual FoxPro employs progressive fetching of view cursors and cursors created asynchronously with SQL pass-through. Rather than requiring you or your application to wait while an entire data set is retrieved, Visual FoxPro executes a query and fetches only a small subset of the result set rows into the local cursor. The size of this subset is 100 rows by default.
Note Synchronous SQL pass-through statements don't employ progressive fetching. The entire result set requested by a SQLEXEC( ) statement is retrieved before control is returned to your application.
As Visual FoxPro retrieves additional rows of data, the local cursor contains increasingly more of the queried data. Since rows are retrieved at different times from the data source, the information in the rows isn't automatically current. If your connection is operating in asynchronous mode, Visual FoxPro returns control to you or your program as soon as it fetches the first subset of data. During idle time, Visual FoxPro performs a background fetch of the remaining rows in the queried data, one subset at a time, into the local cursor. This scenario allows you to use the already fetched data in the cursor without having to wait for the rest of the data.
Note Increasing the number of rows fetched improves performance, but decreases the responsiveness of the user interface. Decreasing the number of rows fetched has the inverse effect.
You can disable progressive fetching and fetch rows only on an as-needed basis by using the FetchAsNeeded database and view cursor property. This can result in more efficient data retrieval for remote views or views retrieving extremely large results sets.
The FetchAsNeeded property is set by default to false (.F.), which means that progressive fetching is employed by default. When you set the FetchAsNeeded property to true (.T.), rows are fetched only when needed. When the FetchAsNeeded property is set to true, you cannot perform an update until you either complete the fetch, call the SQLCANCEL( ) function on the current connection handle, or close the view.
If you want to see the impact of using the FetchAsNeeded property, set the FetchAsNeeded property on a view retrieving a large result set to .T. and then open a browse window on the view and scroll down. The status bar is updated to show the number of rows retrieved as you move through the browse window.
If you want to fetch the entire cursor, you can issue the GOTO BOTTOM command, or any command requiring access to the entire data set.
Tip While you can use the GOTO BOTTOM command to fetch the entire cursor, it’s often more efficient to build a parameterized view that fetches only a single row at a time and requeries as the user changes records. For more information on building high-performance views, see Chapter 8, Creating Views.
Programs don’t provide idle loop processing. To fetch view cursors programmatically, use the GO nRecordNumber or GOTO BOTTOM commands. To fetch cursors created with SQL pass-through in asynchronous mode, call the SQL pass-through asynchronous function once for each row subset.
You can use the SQLCANCEL( ) function to cancel a SQLEXEC( ) statement or a view at any time. However, if the server has completed building the remote result set and Visual FoxPro has begun fetching the remote result set into a local cursor, the SQLCANCEL( ) function cancels the SQLEXEC( ) statement and leaves the local cursor. If you want to delete the local cursor you can issue the USE command, which closes the cursor and cancels the fetch.
The USE command will not cancel a SQLEXEC( )statement if the statement hasn't yet created a local cursor. To determine whether Visual FoxPro has created a local cursor, you can call the USED( ) function.
You control the number of rows fetched at one time by your application from a remote server by setting the FetchSize property on your view. The FetchSize property specifies how many records are fetched into the local cursor from the remote server at one time, through progressive fetching or asynchronous SQL pass-through calls. The default value is 100 rows.
To control the number of records fetched at one time into a view
-or-
-or-
For example, the following code sets the view definition to progressively fetch 50 rows at a time into Customer_remote_view:
? DBSETPROP('Customer_remote_view', 'View', 'FetchSize', 50)
A well-designed application frequently uses delayed Memo fetching to speed downloading of result sets that contain Memo or General fields. Delayed Memo fetching means that Memo and General field contents are not automatically downloaded when you download a result set. Instead, the rest of the fields in the row are quickly downloaded, and Memo and General field contents aren't fetched until you request them by opening the Memo or General field. Delayed Memo fetching provides the fastest downloading of rows, and allows Memo or General field contents, which can be quite large, to be fetched only if needed by the user.
For example, your form might include a General field that displays a picture. To speed performance, you can use delayed Memo fetching to prevent downloading of the picture until the user chooses a “Preview” button on your form. The code behind the “Preview” button then fetches the General field and displays it on the form.
To control delayed Memo fetching, you use the FetchMemo property on your view or cursor. The FetchMemo property specifies whether to fetch the contents of the Memo or General fields when the row is downloaded. The default value is true (.T.), which means that Memo and General fields are downloaded automatically. If your data contains large amounts of Memo or General field data, you might notice increased performance when you set the FetchMemo property to false (.F.).
Note The view must be updatable to allow delayed Memo fetching to work, because Visual FoxPro uses the key field values established by the update properties to locate the source row on the server when it retrieves the Memo or General field. For information on making a view updatable, see Chapter 8, Creating Views.
Use DBSETPROP( ) to set the FetchMemo property on a view, and CURSORSETPROP( ) to set the FetchMemo property on a cursor.
You can use the following recommendations for setting connection and view properties to optimize data fetching. The PacketSize property on your connection has the greatest influence on performance. Also, you can optimize fetch performance using synchronous connections.
| Object | Property | Setting |
| Connection | PacketSize | 4K to 12K1 |
| Connection | Asynchronous2 | .F. |
| View | FetchSize3 | maximum |
1 Set a higher value for rows containing more data; you should experiment to find the best value.
2 Use synchronous connections to increase performance up to 50%, unless you want to be able to cancel SQL statements while executing on the server.
3 The effect of FetchSize is highly dependent on the record size of the fetched result set. In synchronous mode, it does not significantly affect performance, so set it as needed for SQL pass-through asynchronous processing view progressive fetching. FetchSize, if reduced, provides significantly better responsiveness while progressively fetching a view, but slows down the fetch speed. If increased, it increases view fetch performance.
Actual performance depends greatly on your system configuration and application requirements. These recommendations are based on a client machine running Windows NT version 3.5, with ODBC version 2.10 and a SQL Server ODBC driver version 2.05; and a server machine running Windows NT version 3.5 with SQL Server version 4.21 and version 6.0.
You can improve query and view performance by adding indexes, optimizing local and remote processing, and optimizing parameter expressions.
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.
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.
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.).
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.
When you design a form based primarily on server data, take a minimalist approach for the best performance. Determine the data and functionality needed, and delay asking the server for this data and functionality until requested by the user. Requesting data from the server uses processing time and creates network traffic. To request less data in your forms:
Often, an application contains several forms that use the same remote table. If the data in the table doesn’t change frequently, you can speed up form loading and reduce server load using one of the following techniques:
Display fields that take a long time to retrieve data from the server, such as Memo or General fields, only when requested. You can use the following techniques:
SELECT customer_id, company_name, address, city, region, country
FROM customers
In the preceding form, cust_id is bound to thisform.txtCust_id. You could base the second form on the following view, which is used only when the user chooses the “More Information” button:
SELECT orders.order_id, orders.order_date, orders.shipper_id, ;
employee.emp_id, employee.last_name, employee.first_name ;
FROM orders, employee ;
WHERE orders.cust_id = ?THISFORM.txtCust_id ;
AND orders.employee_id = employees.emp_id
You can speed up Update and Delete statements by:
You can improve performance when you update, insert, or delete data in a remote table that contains many fields by adding a timestamp field to the remote table, if your server provides the Timestamp field type.
The presence of a timestamp field in a remote table allows you to use the Visual FoxPro SQL WhereType update option DB_KEYANDTIMESTAMP. This option saves processing time because Visual FoxPro compares only two fields in your view, the key field and the timestamp field, against a remote table to detect update conflicts. By comparing only two fields, rather than all the updatable fields (with the DB_KEYANDUPDATABLE option) or all the modified fields (with the DB_KEYANDMODIFIED option), the DB_KEYANDTIMESTAMP option reduces the time it takes to update remote data. For more information on WhereType options, see Chapter 8, Creating Views.
Note The DB_KEYANDTIMESTAMP option compares the key and timestamp fields only when your remote table contains a timestamp field. If you use the DB_KEYANDTIMESTAMP option against a remote table that doesn't contain a timestamp field, Visual FoxPro compares the key fields only.
The Upsizing Wizard can automatically add timestamp fields as appropriate to tables you export. For more information, see “Timestamp Columns” in Chapter 20, Upsizing Visual FoxPro Databases.
Tip If you do something that alters the structure of a view’s base table, such as adding a timestamp field, you might need to re-create the view. The fields in a view definition are stored in the database, and any changes to the base tables for a view after the view is used aren't reflected in the view definition until you re-create the view.
Whenever appropriate, you can speed updates by preventing view memo fields (fields of type Memo, General, or Picture) from being compared against their base table counterparts. By default, the CompareMemo property is set to true (.T.), which automatically includes memo fields in the SQL WHERE clause generated when you create an updatable view. You can set the CompareMemo property to false (.F.) to exclude memos from the SQL WHERE clause.
For optimum performance, use manual transaction mode and manage transactions yourself. Manual transaction mode allows you to control when you commit a group of transactions, which enables the server to process more statements quickly.
Automatic transaction mode is more time-consuming, because by default every single update statement is wrapped in a separate transaction. This method provides maximum control over each individual update statement, but also increases overhead.
You can improve performance in automatic transaction mode by increasing the setting of the BatchUpdateCount property on the view or cursor. When you use a large BatchUpdateCount setting, many update statements are batched in a single update statement, which is then wrapped in a single transaction. However, if any statement in the batch fails, the entire batch is rolled back.
Tip The BatchUpdateCount property isn't supported by some servers; you should test this property against each remote server before deploying it in your application.
You can create stored procedures on the server, which are precompiled and therefore run very quickly. You can execute stored procedures, send parameters with SQL pass-through, and move additional processing to the server as appropriate for your application.
For example, you might want to collect user input locally and then execute a SQL pass-through query to send the data to the server, calling the appropriate stored procedure. To do this, you might want to create a form on a local cursor or array to collect data and then write code that constructs a SQLEXEC( ) statement by using the name of the server-stored procedure and the parameters to be supplied. You could then add this code to the Click event of a command button titled “OK” or “Commit.” When the user chooses the button, the SQLEXEC( ) statement runs. Using server stored procedures to update remote data can be more efficient, because the stored procedures are compiled on the server.
If your application updates a number of records, you might want to batch updates so they're handled more efficiently by the network and server. Update or Insert statements are batched before being sent to the server, according to the setting of the BatchUpdateCount property of the view. The default value is 1, which means that each record is sent to the server with an update statement. You can reduce network traffic by increasing the value to package multiple updates in a statement.
Tip The BatchUpdateCount property isn't supported by some servers; you should test this property against each remote server before deploying it in your application.
To use this feature efficiently, the view connection should be set to Buffering mode 5, for optimistic table buffering, and changes ideally should be confined to the same fields in each row of the cursor. You can use DBSETPROP( ) to set the BatchUpdateCount property for the view definition; to change the value for an active view cursor, use CURSORSETPROP( ).
You can use the following guidelines for setting view and connection properties to optimize performance of updates and deletes. The BatchSize property on your view has the greatest influence on performance.
| Object | Property | Setting | Notes |
| View | BatchUpdateCount | 10 – 30 rows | Set a higher value for smaller-sized updates.1 Set to increase performance by up to 50%. The default is 1. |
| Connection | Asynchronous | (.F.) | Use synchronous connections to increase performance up to 50%, unless you want to be able to cancel SQL statements while executing on the server. The default is synchronous. |
| Connection | WaitTime | N/A | To increase performance in asynchronous mode, use a shorter wait time; to reduce network traffic, increase the wait time. |
| Connection | PacketSize | 4K to 12K | Has little effect on performance. |
1 Your best value also depends on the speed of your server.
Actual performance depends greatly on your system configuration and application requirements. Experiment with the listed values to determine the best settings for your configuration. The previous recommendations were optimal based on a client machine running Windows NT version 3.5 with ODBC 2.10 and SQL Server Driver 2.05; and a server machine running Windows NT, Version 3.5 with Microsoft SQL Server 4.21 and 6.0.