Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

> ## Field lists

> The DataServer fully supports the use of field lists in queries (`DEFINE` `QUERY`, `FOR` `EACH`, `PRESELECT`, and SQL `SELECT` statements). Using `FIELD` and `EXCEPT` clauses can greatly improve performance for `NO-LOCK` queries, particularly if the DataServer is a remote configuration where the query results must be passed over a network. When a field list is used, unneeded data is not returned. The field list does not guarantee that it will restrict the data to the specified fields. It can be necessary to return additional fields such as those required to accommodate the values of a selected index.

> For example, the following statement returns the same results for an OpenEdge database and an MSS data source:

>     DEFINE QUERY myquery FOR customer FIELDS (cust_num name) SCROLLING.

>     OPEN QUERY myquery FOR EACH customer NO-LOCK WHERE cust_num < 6 BY name.

> 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. If a lock upgrade is required, the field list is ignored and all fields are retrieved.

> 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 the data source. 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. Lookahead and block cursors gain performance by prebinding the fields of your result set. For maximum efficiency, any text or image fields should be explicitly excluded from your field list if possible because MSS does not allow those fields to be pre-bound.

> Programmers are responsible for coding their applications to restrict the use of their query buffers to the fields specified by the field list. References to fields outside the field list are not caught by a compile time error. Sometimes such a reference will return a run time error, but that is not guaranteed. The following code will return a run time error reporting that the `st` field is missing from the `customer` buffer:

>     FOR EACH customer FIELDS (cust-num name)
>         WHERE NAME = "Off the Wall" NO-LOCK:
>             FIND FIRST order WHERE order.st = customer.st NO-LOCK.
>     END.

> The following code will not return a run time error because the `CAN-FIND` expression resolves to `FALSE`, masking the fact that there was in fact no `customer.st` value to compare:

>     FOR EACH customer FIELDS (cust-num name)
>         WHERE NAME = "Off the Wall" NO-LOCK:
>             CAN-FIND(FIRST order WHERE order.st = customer.st NO-LOCK).
>     END.

> See the “Record Phrase” entry in OpenEdge Development: Progress 4GL Reference for more information on the `FIELDS` option.

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