Progress/400
Product Guide
Field Lists
Progress/400 supports field lists. Field lists can improve performance because the entire record is not returned to the client. Progress returns only the requested fields. However, using field lists in a query does not automatically ensure performance improvements. The following list explains what field lists can do and where they are appropriate:
- Fields lists allow you to select a short list of fields to retrieve in a query. Only the fields listed in the FIELDS clause of DEFINE QUERY are retrieved, thus shortening the data length of the record.
- Field lists are enabled only in a NO-LOCK query. If the query specifies SHARE-LOCK (the default) or EXCLUSIVE-LOCK, field lists are turned off and the entire record is retrieved.
- Specifying a field list in conjunction with a NO-LOCK query enables record prefetch. This allows the Progress/400 DataServer to pack the shortened field list records together, up to the size of the Message Buffer Size (-Mm) startup parameter.
These factors influence performance when using field list queries:
- Message Buffer Size (-Mm) startup parameter — To control the size of the network message, use the Message Buffer Size (-Mn) startup parameter when you start the Progress client. For details, see the "Using the Message Buffer Size (-Mm) Startup Parameter" section in Remote Access to Progress/400 DataServer."
- Total number of records — If a file contains only a few records (less than 100), using field lists does not enhance performance. To see the benefit of field lists, the file must contain enough records (5,000-1,000,000) so that a normal query takes 20 to 30 seconds to run.
- Record length — Field list improvements are most dramatic when the record length of a file is close to, or greater than, the Message Buffer Size (-Mm) startup parameter. For example, assume that the file “FILEA” has a record length of 4200 bytes and a -Mm parameter setting of 4000. Since the record length is larger than the -Mm setting, each whole record requires two network packets. The first packet contains the first 4000 bytes of the record, the second contains the remaining 200 bytes. The record is reassembled on the client. Also, if the file has only a 3000-byte record length, you can still send only one record per network packet. In both of these cases, a field list query can be quite helpful. If, for example, a query requires only two fields (totaling 20 bytes), the DataServer can pack 200 field list records into the 4000-byte network packet.
- Specifying too many fields in the FIELDS phrase — If your field list query specifies more than approximately half of the fields in the file, the additional overhead to process the field list outweighs any benefit, thus potentially degrading performance. Generally, you should limit the fields in a query (browser) to just a few and reread specific users’ selected records by RECID.
Progress/400 provides the same support for both a DB2/400 database and a Progress database when using field list queries. The following example returns the same result for both a Progress database and a DB2/400 database:
Include the SCROLLING option to enable record prefetch. You must include the NO-LOCK option when you open queries with field lists, as in the following example:
Similarly, you must include the NO-LOCK option in FOR EACH statements that include field lists, as in the following example:
Alternatively, you can tune the database to have NO-LOCK as the default. This allows you to use field lists without specifying the NO-LOCK query option.
Use field lists to retrieve only those fields that your application requires. (For performance reasons, the Progress/400 DataServer retrieves the first index field even when you do not include it in the field list. In cases when the DataServer can predict that a query requires a refetch, it retrieves the entire record.) The DataServer allocates memory based on the maximum size specified for a field in a record. Omitting larger fields from a query enhances performance.
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 DB2/400 RDBMS. For example, the Progress/400 DataServer fetches the name and the zip field to process the following query:
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 |