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 a MS SQL Server data source:

DEFINE QUERY myquery FOR customer FIELDS (custnum name) SCROLLING.

OPEN QUERY myquery FOR EACH customer NO-LOCK WHERE custnum LT 6
BY customer.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:

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 postalcode field to process the following query:

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 MS SQL Server 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 (custnum name)
WHERE customer.name EQ "Off the Wall" NO-LOCK:
FIND FIRST order WHERE order.st EQ 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 (custnum name)
WHERE customer.name EQ "Off the Wall" NO-LOCK:
CAN-FIND(FIRST order WHERE order.st = customer.st NO-LOCK).
END.