Speeding Up Data Retrieval

Article
10/23/2006

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 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 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 12K¹
Connection	Asynchronous²	.F.
View	FetchSize³	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.