Progress
DataServer for
Microsoft SQL Server
Guide


Field Lists

The DataServer fully supports the use of field lists in queries (DEFINE QUERY, FOR EACH, PRESELECT, and SQL SELECT statements). For example, the following statement returns the same results for a Progress database and an MSS data source:

DEFINE QUERY myquery FOR customer FIELDS (cust_num name) SCROLLING NO-LOCK. 

Include the SCROLLING option to enable GET PREVIOUS. You must include the NO–LOCK option when you open queries that are defined with field lists.

Similarly, you must include the NO–LOCK option in FOR EACH statements that include field lists, as in the following example:

FOR EACH customer FIELDS (cust_num name) NO-LOCK: 

Field lists are effective only when you also specify the NO–LOCK option. This option ensures that the DataServer does not have to refetch rows, which can slow performance.

Use field lists to retrieve only those fields that your application requires. (For performance reasons, the DataServer retrieves the first index field even if you do not include it in the field list. In cases where the DataServer can predict that a query will require a refetch, it retrieves the entire record.) The DataServer allocates memory based on the maximum size defined for a field in a record. Omitting larger fields from a query can enhance performance. In addition, combining lookahead cursors and field lists greatly improves a query’s performance.

When you specify a field that has an extent, the query returns the entire array.

When the DataServer processes a query with a field list, it caches the fields that are part of the field list and any other fields that the query specified, which you can then access without making another call to ODBC. For example, the DataServer fetches the name and the zip field to process the following query:

FOR EACH customer FIELDS (name) WHERE zip = 01730 NO-LOCK: 

NOTE: Cached fields might have performance implications if you modify the record later, as the DataServer must refetch the record to place a lock on it.

If you specify a field list in a join, you might have to adjust the cache size for lookahead cursors, either with the CACHE–SIZE option in a QUERY–TUNING phrase or at the session level with the -Dsrv qt_cache_size startup parameter.

Any performance gained through field lists is lost if you use nonlookahead cursors.

See the Record Phrase entry in the Progress Language Reference for more information on the FIELDS option.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095