Performance Improvement on Updates and Deletes

Article
10/23/2006

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 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 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.¹ 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.

Share via