Chapter 22 -- Optimizing ClientServer Performance

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:

• Optimizing Connection Use
• Speeding Up Data Retrieval
• Speeding Up Queries and Views
• Speeding Up Forms
• Improving Performance on Updates and Deletes

Optimizing Connection Use

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.

Using Shared Connections

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.

Controlling Connection Timeouts

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:

• Results of a query from the server are pending.
• The connection is in manual transaction mode. You must commit or roll back the transaction and switch to automatic transaction mode before the connection can be closed.

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.

Releasing Connections

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.

Speeding Up Data Retrieval

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.

Using Progressive Fetching

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.

Fetching Data On Demand

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.

Controlling Cursor Fetching

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.

Canceling a SQLEXEC( ) Statement

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.

Controlling Fetch Size

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

• In the View Designer, choose Advanced Options from the Query menu. In the Data Fetching area of the Advanced Options dialog box, use the spinner to set a value for Number of Records to Fetch at a time.

  -or-

• Set the FetchSize property with the DBSETPROP( ) function to set the view definition s fetch size.

  -or-

• Set the FetchSize property with the CURSORSETPROP( ) function to set the active view cursor s fetch size.

  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) 

Using Delayed Memo Fetching

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.

Optimizing Data Fetching Performance

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.

Speeding Up Queries and Views

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.

Speeding Up Forms

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:

• Request as few records as possible. For example, use a filter or query to limit the size of the record set. Make sure that the server can process any restrictions you use.
• Use as few remote fields as possible in views underlying your forms.
• Use as few forms that access remote views as possible in your form set. When you open a form set, all the forms in the form set are opened and populated with data as applicable. By limiting the number of forms in your form set, especially those that must connect to a server and retrieve remote data, you shorten the time the form set takes to load.
• Use fewer bound controls that access remote data. Each combo box, list box, and grid that's bound to a remote table or query requires a separate query to the server when the form is opened. Avoid controls containing totals, or list boxes and combo boxes that have large row sources.
• If users need to compare multiple sets of data, consider storing the data returned by the server in temporary local tables. Provide a form in which the user can use the previously stored data, or execute a new query.

Storing Lookup Tables Locally

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:

• Store tables that never change and aren't too large (such as the names and abbreviations of the regions or states in your country) in the local Visual FoxPro application database. If the table is joined in queries or views with remote tables, you should also keep a copy of it on the server to avoid joining local and remote data.
• Store tables that rarely change (such as a list of company buildings) both on the server and in the local application database. Provide a way for the user to download the table when the data does change.
• Store tables that change occasionally but not daily (such as a list of employees in a small company or department) both on the server and in the local application database. Your application should automatically refresh the local version each time it starts. This method uses extra time when the application starts, but speeds up queries when the application is running.

Displaying Fields Only On Request

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:

• If your form is based on a view, place Memo or General fields off screen on another form page. Add a label to the form, such as Page down to see notes and pictures, that informs the user how to display the information. Set the FetchMemo property on the view or cursor to false (.F.), so that Visual FoxPro doesn't retrieve Memo or General fields until they're displayed on screen.
• Set the Visible property to false (.F.) for controls bound to Memo or General fields. Add a toggle button or command button that sets the property to true (.T.), so that the user can choose to view the contents of these controls.
• Display the most important fields on a main form, and provide a button labeled More Information that opens another form containing other fields. Base the second form on a view that's parameterized by the primary key field on the main form. For example, suppose you have a main form based on a view whose SQL SELECT statement includes the following code:

  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 

Improving Performance on Updates and Deletes

You can speed up Update and Delete statements by:

• Adding timestamps to your remote tables.
• Using the CompareMemo property.
• Using manual transaction mode.
• Using server stored procedures.
• Batching updates.

Adding Timestamps

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.

Excluding Memo Fields from the Update WHERE Clause

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.

Using Transactions

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.

Using Server-Stored Procedures

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.

Batching Updates

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

Optimizing Performance of Updates and Deletes

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.