Performance Enhancement through Set-Based Data Access

Article
10/23/2006

One of the most important factors in building a fast, efficient client/server application is minimizing the amount of data you pull down from the server. Because client/server applications can access potentially huge amounts of data on a remote server, using traditional local navigation techniques could result in a slow client/server application. To speed performance, you use set-based data access techniques to filter the amount of data you download.

Accessing Set-Based Data Efficiently

Remote data is set-based; you access remote data by selecting a set of data from a large data store using SELECT - SQL statements. The most important difference between building a traditional local application and building a client/server application is the contrast between traditional Visual FoxPro navigational techniques and set-based server data access techniques.

Using Traditional Navigational Techniques

In traditional local database programming, you can access discrete and often large amounts of data by using the GOTO BOTTOM command, which you then query against. You can navigate through data by issuing a SET RELATION command to create a temporary relationship between two tables and then issuing a SKIP command to move through the related records.

While this method of navigating records could be used against remote data, it can be inefficient against large remote data stores. For example, if you create a remote view that accesses a large table on a remote data source and then issue the GOTO BOTTOM command, you must wait while all the data in the view is retrieved from the data source, sent across the network, and loaded into your local system's view cursor.

Using Parameterized Queries

A more efficient approach for accessing remote data is to download just the data you need and then requery to obtain specific additional or new records. Use a SELECT statement based on parameters to download a specific small set of data and then access new records by using the REQUERY( ) function to request a new set of data.

You don't issue the GOTO BOTTOM command against remote server data because this would:

Unnecessarily burden network resources by downloading huge amounts of data.
Slow performance of your application by handling unneeded data.
Potentially reduce accuracy of the data in the local cursor because changes to remote data aren't reflected in the local cursor until you requery.

For example, if you want to create a client/server application that accesses the orders for a particular customer, create a remote view that accesses the Customer table. Create another remote view that accesses the Orders table, but parameterize the view based on the cust_id field. Then use the current customer record as the parameter for the view of the Orders table.

You can use the parameter to scope the downloaded data set to just the right amount of data. If you request too little data, you can lose performance because you'll have to requery the remote server more frequently. If you request too much data, you can waste time downloading data you won't use.

Choosing the Best Client/Server Design

The following examples describe how to gain the benefits of client/server technology and avoid the pitfalls of poor programming techniques. The first method uses traditional programming practices to retrieve all the data from a remote data source into local cursors that are then related with the SET RELATION command. The second, third, and fourth methods adopt progressively smarter data-fetching techniques, effectively limiting the amount of data downloaded with a "just-in-time" methodology that provides the freshest data and fastest response time over a network.

Using an Unoptimized Client/Server Strategy

A straightforward, unoptimized client/server application uses local data navigation techniques with remote data. For example, if you have 10-million customer records and 100-million order records on a remote data source, you can create an inefficient application that downloads all the Customer and Order records into local cursors. You could then index on 100-million order records, create a temporary relationship between the Customer and Orders tables in your local cursors, and use the SKIP command to navigate through the records.

This method is not optimized for performance, but might, however, be useful if the one side is local and the many side is remote.

Filtering the Many Side

A slightly improved client/server application limits the many side of the relationship, but retrieves all of the one side so you can skip through the records. In this scenario, you create a remote view of the many side of the relationship, the Orders table, parameterized on the customer ID. You then download the entire Customer table.

While creating a parameterized view on the Orders table is an improvement over downloading all of the orders, you still retrieve unnecessary information by continuing to download the entire Customer table. The Customer table is also increasingly out-of-date as changes are made by other users on your system. This method might be beneficial if the one side of the relationship contains a small data set.

Filtering the One Side

A better client/server programming technique creates remote views for all remote data. You limit the number of Customer records downloaded into the remote view of the Customer table by using the SELECT statement in the view to select just the customers for one region. You then create a remote view of the many side of the relationship, the Orders table, parameterized on the customer ID.

This scenario retrieves a smaller set of records. You use the SKIP command to skip on the one side of the relation (the Customer view). You use the REQUERY( ) function to access new data on the many (Orders) side.

In this example, you limit, or filter, both the one side and the many side of the relationship, and can still use the SKIP command to navigate through the filtered data. This method is be recommended if the one side of the relationship, even after being filtered, is still sufficient to provide information for a successive set of queries before you requery the remote server.

Using the Primary Key to Access the One-to-Many Relationship

The most efficient client/server programming paradigm gives up the luxury of using the SKIP command, and creates a form that requests input or selection of the customer ID, which is then used as a parameter for a remote view of the Customer table. This parameter is also used as a parameter for a remote view of the Orders table.

For example, you could create a one-to-many form in which the customer information forms the one side, and a Grid control displays the many side of the relationship. The Grid control can be bound to the customer ID chosen in the one side of the form. You can then set the MaxRecords property of CURSORSETPROP( ) to 1, and use the following code to populate the one side of the form:

SELECT * FROM customer WHERE customer.cust_id = ?cCust_id

When the user wants to view a different customer's record, they input or select a new customer ID. The form requeries the data source for the orders for the new customer ID and refreshes the Grid control with the new order data.

Using these techniques, your application downloads just the data you need, at the time it's needed. You speed response over the network by limiting the amount of data downloaded, and you provide fresher information to the user by requerying the data source just before you display requested information.

This method is recommended when you want to access the one-to-many relationship randomly using any primary key value. You might want to download the primary keys into a control, such as a drop-down list, when you open the form and then provide a control that the user can choose to refresh the list of primary key values on demand.

Using the Data Environment in Client/Server Applications

When you use remote data in a form, include the views in the form's data environment. You can set the AutoOpenTables property for the data environment to false (.F.) so that you can specify when the application refreshes the views with the remote data. Set the ControlSource property for text boxes or other data-bound controls after you call the data environment's OpenTables method, typically in the code associated with the Init event of the form. For more information on setting form properties, see Creating Forms.

Share via