Optimizing Access to Remote Data

Article
11/14/2006

Data retrieval from any remote database is expensive. In order to get data from a server database, the following steps must occur:

The client issues the query to the remote database.
The server parses and compiles the query.
The server generates a result set.
The server notifies the client that the result is complete.
The client fetches the data over the network from the server. This step can happen all at once, or the client can request that results be sent in pieces as requested.

You can use a number of techniques to speed up the retrieval (or update) of data, such as:

Retrieving only the data you need
Updating remote tables efficiently
Sending statements in a batch
Setting packet size
Delaying retrieval of memo and binary data
Storing lookup data locally
Creating local rules

Retrieving Only the Data You Need

In most applications that use remote data, forms and reports don't need to access all the data from a table at once. Therefore, you can speed performance by creating remote views that fetch or update only the fields and records you want, which minimizes the amount of data that needs to be transmitted across the network.

To create queries that minimize the overhead of data retrieval from remote sources, follow these suggestions:

Specify only the fields you need. Don't use the statement SELECT * FROM customers unless you need all the fields from the table.
Include a WHERE clause to limit the number of records downloaded. The more specific your WHERE clause, the fewer records are transmitted to your computer, and the faster the query will finish.
If you cannot predict at design time what values to use in a WHERE clause, you can use parameters in the clause. When the query is executed, Visual FoxPro uses the value of a parameter variable or prompts the user for the search value. For example, this query allows the application or user to fill in the region at run time:
```
SELECT cust_id, company, contact, address ;
   FROM customers ;
   WHERE region = ?pcRegion
```
Set the NoDataOnLoad property of the corresponding Data Environment Cursor object. This technique is commonly used with parameterized views in which the data for the parameter comes from the value of a control on a form.

Updating Remote Tables Efficiently

When you use a view to update a table on a remote data source, Visual FoxPro must check whether the record or records you are updating have been changed. To do so, Visual FoxPro must examine the data on the server and compare it to the data being held on your computer. In some instances, this can be a time-consuming operation.

To optimize the process of updating data on remote data sources, you can specify how Visual FoxPro should check for changed records. To do this, you indicate the WHERE clause that Visual FoxPro should generate in order to perform the update.

For example, imagine that you are using a view based on a customer table on a remote data source. You created the view using a SELECT - SQL statement such as this one:

SELECT cust_id, company, address, contact ; 
   FROM customers ;
   WHERE region = ?vpRegion

You want to be able to update all four fields that you have specified in the view except the key field (cust_id). The following table illustrates the WHERE clause that Visual FoxPro will generate for each of the options available under the SQL WHERE clause.

Note The OLDVAL( ) function returns the pre-update version of fields you modified, and the CURVAL( ) function returns the current value stored on the remote data source. By comparing them, Visual FoxPro can determine whether the record has changed on the remote data source since you downloaded it to your computer.

Setting	Resulting WHERE clause
Key fields only	`WHERE OLDVAL(cust_id) = CURVAL(cust_id)`
Key and updatable fields (default)	`WHERE OLDVAL(cust_id) = CURVAL(cust_id) AND OLDVAL(<mod_fld1>) = CURVAL(<mod_fld2>) AND OLDVAL(<mod_fld2>) = CURVAL(<mod_fld2>) AND ...`
Key and modified fields	`WHERE OLDVAL(cust_id) = CURVAL(cust_id) AND OLDVAL(company) = CURVAL(company) AND OLDVAL(contact) = CURVAL(contact) AND OLDVAL(address) = CURVAL(address)`
Key and timestamp	`WHERE OLDVAL(cust_id) = CURVAL(cust_id) AND OLDVAL(timestamp) = CURVAL(timestamp)`

In general, you should choose an option for the SQL WHERE clause in this order of preference:

Key and timestamp, if the remote database supports timestamped fields, which is the fastest way to tell if a record has changed.
Key and modified fields, because the fields you update to the server are almost always a subset of the total number of fields that you could update.
Key and updatable fields.
Key fields only. Using these settings implies that the remote server will insert an entirely new record using the changed key, and will delete the old record.

Sending Statements in a Batch

Some servers (such as Microsoft SQL Server) allow you to send a batch of SQL statements in a single packet. This speeds performance because you reduce network traffic, and because the server can compile multiple statements at once.

For example, if you specify a batch size of four, then update 10 records in a database, Visual FoxPro sends four statements such as the following to the server database in one batch:

UPDATE customer SET contact = "John Jones" ; 
   WHERE cust_id = 1;
UPDATE customer SET contact = "Sally Park" ; 
   WHERE cust_id = 2;
UPDATE customer SET company = "John Jones" ;
   WHERE cust_id = 3;
UPDATE customer SET contact = "John Jones" ;
   WHERE cust_id = 4

To send statements in a batch

In the Options dialog box, choose the Remote Data tab, and then under Records to batch update, specify the number of records to include in a batch.

-or-
Call the DBSETPROP( ) or CURSORSETPROP( ) functions to set these properties:
- Set Transaction to 2.
- Set BatchUpdateCount to the number of statements to send in a batch.
  
  -or-

In the View Designer, choose Advanced Options from the Query menu to display the Advanced Options dialog box.
In the Performance area, next to Number of records to batch update, specify the number of statements to send in a batch.

Note You should experiment with different values for this property and the PacketSize property to optimize your updates.

Setting Packet Size

You can optimize access to remote servers by fine-tuning the size of the network packet that is sent to and retrieved from the remote database. For example, if your network supports large packet sizes (greater than 4,096 bytes), you can increase the packet size in Visual FoxPro in order to send more data each time you read or write to the network.

To set packet size

Call the DBSETPROP( ) or CURSORSETPROP( ) functions and set the PacketSize property to a positive integer value. The default value is 4,096.

Note Different network providers will handle this property differently, so you should consult your network service documentation. Novell NetWare, for example, has a maximum packet size of 512 bytes so setting the PacketSize property to a value greater than this will have no additional benefit.

Delaying Retrieval of Memo and Binary Data

If you're storing Memo or binary data on a remote server, you can improve performance by delaying the download of this data until your application actually requires it.

To delay retrieval of memo and binary data

In the Options dialog box, choose the Remote Data tab, and then under Remote view defaults, set Fetch memo.

-or-
Call the DBSETPROP( ) or CURSORSETPROP( ) functions to set the FetchMemo property.

Storing Lookup Data Locally

Many applications include static lookup data such as state abbreviations, postal codes, and employee titles. If your application contains this type of data, and if the table is not too large, you might be able to speed up your application by keeping copies of this information on each user's computer, because lookups do not generate network traffic.

This technique is primarily useful for data that never changes or changes very rarely. If the data does change on occasion, you must devise a strategy for downloading a new copy of the lookup table to each user's computer.

Creating Local Rules

You can gain efficiency in your application by creating local field-level and record-level rules within Visual FoxPro, rather than relying on rules defined on the server. These rules can prevent data that doesn't conform to data or business rules from getting into the database.

By defining rules in Visual FoxPro, you trap the invalid data before it's sent across the network, which is faster, and which gives you better control for handling error conditions. However, using local rules also means that you must coordinate them with rules on the remote server. For example, if there are changes to the rules on the server, you might have to change your local rules to match.

For details about creating local rules, see Updating Data in a View in Creating Views.

Share via