Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

OpenEdge 4GL Query concepts (FOR EACH, FIND, GET, INDEX)

« Go Back

Information

 
Article Number000012195
EnvironmentProduct: Progress
Version: 7.x, 8.x, 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: Language (4GL/ABL)
Question/Problem Description
4GL query concepts (FOR EACH, FIND, GET, INDEX)
What are 4GL queries?
What are the elements of query execution
Characteristics of FOR EACH queries
Characteristics of PRESELECT queries
Characteristics of FIND queries
Characteristics of GET queries
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
4GL Query concepts ( FOR EACH , FIND , GET , INDEX )

CONTENTS
========

1. Introduction
2. What are 4GL queries?
3. The elements of query execution
4. Execution strategy
5. Characteristics of FOR EACH queries
6. Characteristics of PRESELECT queries
7. Characteristics of FIND queries
8. Characteristics of GET queries


INTRODUCTION
============

****** PLEASE NOTE: The contents of this document are accurate as of the time of its writing. Due to ongoing development it is likely that certain details may change without notice. Programmers are advised to test thoroughly to verify any changes made in accordance to the information given here. This article should not be treated as a functional specification and is provided for informational purposes
only. ******

This document describes the functionality and behavior of PROGRESS 4GL queries. Its purpose is to help in understanding the peculiarities of PROGRESS 4GL and the requirements they impose on the query engine.

This document concentrates on those features that affect the PROGRESS server. It discusses 4GL semantics and client features to the extent that they are needed to understand how queries are executed. It does not describe the complete query semantics as they are manifested in 4GL programs. It does not address PROGRESS SQL queries or queries for foreign data managers (DataServers).

It is assumed that the reader is somewhat familiar with the concepts of indexes, index cursors, client buffers and some other basic terms related to the PROGRESS database.

WHAT ARE 4GL QUERIES?
=====================

4GL Queries are data manipulation operations that are used to retrieve or change data stored in the database. In PROGRESS version 6 and earlier, 4GL queries came in two main flavors: FIND queries and block oriented queries (FOR EACH and PRESELECT). In version 7, the OPEN QUERY/GET flavors of queries were added, with the expectation that they would replace FIND queries in most cases.

GET queries were added because although FIND queries provide very useful functionality, they are very difficult to optimize exactly because of that functionality. There was a need to provide better performance, but also to maintain existing functionality, and the best way to achieve that was by adding new flavors of queries with somewhat different functionality.

The 4GL language constructs used with the three query types are:

- FOR EACH/PRESELECT
- FOR EACH
- REPEAT PRESELECT
- DO PRESELECT

- FIND
- FIND [FIRST | NEXT | LAST | PREV]
- FIND
- FIND CURRENT
- FIND record WHERE ROWID(record) = expression
- CAN-FIND ([FIRST | LAST])
- CAN-FIND (record WHERE ...)

- GET
- DEFINE QUERY
- OPEN QUERY
- GET [FIRST | NEXT | LAST | PREV]
- GET CURRENT

Other 4GL language constructs related to queries

- BY - specifies result ordering via an index or sort
- USE-INDEX - forces use of a particular index. Ensures order if no BY clause used
- SCROLLING - attaches a result list to a query
- CACHE - attaches a query cache and a result list to a query
- REPOSITION - moves the current position of a query
- INDEXED-REPOSITION - allows repositioning of a query which uses a single index by directly repositioning the index cursor
- NO-WAIT - prevents query from waiting if record is locked, and returns control to the 4GL program immediately
- CONTAINS - query through word-index
- FIELDS - defines which fields to retrieve


THE ELEMENTS OF QUERY EXECUTION
===============================

This section describes the basic concepts, structures and operations involved in the execution of queries.

The table below, from the demo(sports) database, is used in many of the examples. There is an index defined on the cust-num column, another on the name column, and another on the zip column.

CUST-NUM NAME STATE ZIP
======== ======================================= ===== =====
1 Second Skin Scuba AZ 85369
2 Match Point Tennis TX 75431
3 Off The Wall PA 15632
4 Pedal Power Cycles MA 02145
5 Flying Fat Aerobics NY 14728
6 Lift Line Skiing MA 02114
7 Fallen Arch Running FL 32010
8 Butternut Squash Inc. CA 92243
9 Spike's Volleyball NV 89411
12 Batter Up Baseball KY 42088
13 Blue Line Hockey ME 04474
14 Birdy's Badminton OK 73048
15 Hoops Croquet Co. MA 02111
20 StickyWicket Cricket MN 56468
21 Ship Shape Yachting CT 06612
22 Pocket Billiards Co. CA 92371
23 Sub Par Golf CO 80482
24 On Target Rifles MS 39657

INDEX CURSORS

An index cursor is a structure which is used to maintain a position within an index. It can be moved to the next index entry, the previous entry or a specific entry, and can be used to read the record at the current entry.

Index cursors are managed by the server on behalf of clients. A cursor is opened, closed, repositioned and used to fetch records for a specific client, at the client's request.

Multiple index cursors can be opened on one or more indexes at any time. Any interaction between them (such as repositioning of related cursors) is initiated by the client; as far as the server is concerned there is no relationship between them.

INDEX BRACKETS

An index bracket is a set of consecutive entries in an index. A bracket is defined by the indexidentifier (index number), a lowest key value (the low limit) and a highest key value (the high limit). All index entries starting with the low value and up to the high value are included in the bracket. A fundamental method for retrieving data is by means of a bracket scan, an operation in which all the index entries from the low limit to the high limit are examined.

There are two classes of brackets: equality brackets and range brackets. Equality brackets define a set of consecutive index entries that have an equality match on a key (or on part of a key). The low and high limit key values are the same. Range brackets define a set of entries from the low key value to the high key value. The entries in a range bracket often have many different key values.

To evaluate a query, PROGRESS always used at least one index bracket.
The 4GL compiler analyzes the query to see if it can use the elements of a WHERE clause, OF, USING, etc., the available indexes and key components to form brackets. The most restrictive brackets will be the most efficient to apply first. If the query does not define any brackets, the compiler will supply a default bracket, usually a table's primary index.

The expression

(name BEGINS "M")

describes a set of entries where the name starts with the letter "M", which is part of the name key. This forms a range bracket.

The expression

((name > "Off The Wall") and (name < "Quick Toss Lacrosse"))

describes a set of consecutive entries by the name index. This forms a range bracket.

USING ONE INDEX BRACKET TO RETRIEVE RECORDS

To retrieve records using an index bracket, the client opens a new cursor, or uses an already open one. It then sends the server a request that includes the cursor identifier and the bracket range (low and high key values), and asks for the next, previous, first or last records in the bracket.

The following queries each use the default bracket on the entire
index:

for each customer: /* entire table desired, no where clause */
end.

for each customer by state: /* sort, no index on state */
end.

for each customer where (state = "OH"): /* no index on state */
end.


The following queries each use a single bracket on part of one index:

for each customer where (zip = 12345):
end.

for each customer where (name = "Off The Wall"):
end.


for each customer where (name begins "B"):
end.


for each customer where (zip > 50000) and (zip < 60000):
end.


for each customer where (name = "Off The Wall") and (zip > 50000):
end.


USING MORE THAN ONE INDEX BRACKET TO RETRIEVE RECORDS

Multi-bracket queries are used by the GET, FOR EACH and PRESELECT statements in version 7 and later. They provide functionality similar to that of index cursors, but can use multiple index brackets and multiple index cursors and provide better performance. They combine index brackets by applying algorithms which take advantage of the index structure, and improve performance for queries with OR and AND operators. For example, if a single index bracket were to be used to execute the query

for each customer where (cust-num <= 10) or (name = "Mary"):
end.


the whole customer table would have to be scanned, because no smaller bracket will include all of the desired records. Using two brackets, many fewer records need to be accessed.

The following queries use two brackets on one index.

for each customer where (zip = 12345) or (zip > 40000):
end.

for each customer where ((zip > 50000) and (zip < 60000)) or ((zip > 70000) and (zip < 80000)):
end.


The following query uses three brackets on two indexes.

for each customer where (name = "Off The Wall") or (zip = 17030) or (name = "StickyWicket Cricket"):
end.


The following query uses four brackets on two indexes.

for each customer
where ((name = "Off The Wall") and (zip = 01824)) or
((name = "StickyWicket Cricket") and (zip = 22070)):
end.


Multi-bracket queries are typically more efficient (faster) than single index cursors in retrieving records, but they can not be repositioned like index cursors: multi-bracket queries can only be moved forward to the next record, and operations such as GET PREV must be supported by the client via a result list; single-bracket queries can be repositioned by repositioning the underlying index cursor, a feature which is accessible to the 4GL via the INDEXED-REPOSITION
option.

RECORD SELECTION

Record selection means determining whether a record satisfies a query by evaluating an expression involving its contents rather than finding it via an index. The checking is done by evaluating E-code (Expression Code Segment) - code which returns true or false - against the record. For example, in the query

for each customer
where (cust-num < 10) and (city = "Boston"):
end.


where the cust-num field is indexed and the city field is not, PROGRESS does the following:

- fetches all records where cust-num < 10 via an index bracket on the cust-num index
- performs a selection on each retrieved record to find out whether it contains the value "Boston" in the city field.

In version 6 and earlier, selection is done exclusively by the client. As a result, a remote version 6 server may send many records to the client which the client checks and discards. A version 7 server is capable of doing most selections, and sends only those records which fully satisfy the query to the client.

It is important to note that there are some selection operations that the server cannot do, either because they require access to program variables in the client, or because they are not implemented on the server (the most important such function is CAN-FIND which is not yet implemented on the server). In such a case, the server sends the records to the client along with an indication that it cannot perform the selection, and the client must do it.

QUERY BY WORDS

Queries by words are supported through the 4GL CONTAINS clause. They use word-indexes, which have the same structure as regular indexes, but contain an entry for each word in a character field rather than one entry for the whole field, as regular indexes do.

Evaluating CONTAINS clauses differs from evaluating other index expressions in two ways:

- a CONTAINS clause is viewed as a single index bracket by the
client, but may actually use multiple brackets, depending on the
expression. For example, the query

for each claim
where (description contains "lawyer | attorney"):
end.


requires two brackets. The decision of how many brackets and how to use them is done by the server, at run-time. This allows the 4GL program to not only change the words in the clause, but also the operators between the words.

- the CONTAINS clause cannot be evaluated during selection - a word-index must always be used. As a result, the client cannot deal with it, and the server must evaluate it using the word-index.

FINDING RECORDS BY ROWID

A query of the form

find customer where (rowid(customer) = <some rowid value>)

does not require the use of any index. The ROWID identifies the exact location of the record in the database, and the server can retrieve it without using any index bracket.

UNIQUE FIND QUERIES

A FIND query without a NEXT, PREV, FIRST or LAST performs an operation which is different from other FIND operations: it not only locates a record, but also ensures that it is the only record which satisfies the query; if more than one record is found, it returns an error. In order to achieve this, PROGRESS must find the first record, and then look for the next record and not find one. The second step can be very costly in some cases: for example, if the city field is not indexed, the query

find customer where (city = "Boston")

requires PROGRESS to scan the customer table until such a record is found, and then continue scanning the rest of the table to ensure that no other such record exists.

Whenever possible, the verification is done by the server, but if the server cannot execute the query, the client must verify the record's uniqueness in addition to performing the selection.

JOINS

PROGRESS translates joins into multiple single-table queries, which are the only types of queries the server can execute today. For example, the query

for each customer, each order of customer:

is executed by the client by opening to queries: one the customer and one for order. After each customer record is received, the client opens the second query for orders where order.cust-num = customer.

cust-num, and retrieves all the orders for the customer. When it is done with the orders it goes to the next customer, and so on.

FIELD LISTS

The FIELDS and EXCEPT clauses instruct PROGRESS to retrieve only selected fields from a record. This reduces the number of bytes sent over the network when accessing a remote server.

Field lists do not reduce the amount of database I/O performed by a query. Records are always read into the server's buffers in their entirety. The server then discards any unnecessary fields from it.

In single-user or self-serving mode, reducing record sizes does not improve performance. Nevertheless, PROGRESS does discard the unwanted fields in order to help detect 4GL programming errors which would occur when running the same program with a remote server.

PREFETCH - MULTIPLE RECORDS INTO NETWORK MESSAGES

In order to reduce network traffic, a remote PROGRESS server can package multiple records into each network message when executing a query. It packages up to the size of one network message, which is controlled by the value of the -Mm startup parameter.

This is called prefetch, because it requests the server to fetch additional records before the client requests them.

The initiative for this operation comes from the client. When the client sends a query request to the server, it can set to flags in the request: the first indicates that it would prefer to receive more than one record in each message when possible; the second indicates that, in addition, it wants each message to contain as many records as will fit. If only the first flag is set, the server will balance the goal of reducing the number of network messages with the need to provide reasonable response time, and may send less than full messages. If both are set, the server will not respond until it has completely filled a message packet.

The client sets both flags when executing a PRESELECT or when pre-sorting query results. For example, in:

do preselect each customer: /* client asks for rowids only */
... /* in full messages */
end.


the client reads all the customer records and stores their rowids in a result list before entering the loop. It does not proceed until it has all the records, so it doesn't benefit if the first message comes back faster and is only partially full.

The client sets the first flag when executing a FOR EACH or scrolling query with the NO-LOCK (unless it contains a NO-PREFETCH qualifier).
For example,

for each customer no-lock: /* client asks for multiple records */
... /* per message if possible */
end.


This is limited to NO-LOCK because it requires fetching and locking more records than the 4GL asked for: in this example, when the FOR EACH loop asks for the 1st record, the client might get back four records. If a lock is used, the client will have obtained locks on the extra records, which are not yet available to the 4GL - they are stored in the network buffer - and if one of them causes a deadlock, the client cannot release it, because its 4GL program isn't aware that it got it.

RESULT LISTS

Result lists are lists of rowids of a query's retrieved records. They are kept by a client and updated as the results of a query are received from a server. They are used to allow the client 4GL program to execute operations such as GET PREV on multi-index bracket queries, where the server can only do GET NEXT. In addition, they are used by PRESELECT queries, and by queries where the results must be sorted (see sorting, below).

SORTING QUERY RESULTS

When a BY clause is used with a query, PROGRESS must produce the query results in the specified order. It does so in one of two ways:

- if an index that is in the desired order exists, and a single bracket on it can be used to execute the query while retrieving the minimum number of records, it is used.

- otherwise, the query is executed in a two pass process. First every record is fetched, and the fields required to determine the order are retrieved, along with the ROWIDs. These values are placed in a result list, which is then sorted in the desired order. In the second pass records are retrieved again, using the sorted ROWIDs in the result list.

QUERY REPOSITIONING

Queries with result lists can be repositioned via the REPOSITION statement. Most such operations are executed by the client using the result list, and don't require server support. For example:

define query q for customer scrolling.
open query q preselect each customer.

/* the next statement will simply reposition the query within */
/* the result list */

reposition q to row 3.

/* the next statement will read customer 3 via its rowid */
/* stored in the result list */

get next q.


The REPOSITION statement may be handled by the server only when INDEXED-REPOSITION is used on a single index bracket, single table query. For example:

define query q for customer scrolling.
open query q for each customer indexed-reposition.

find customer where ... xxx = rowid(customer).

/* the next statement will cause the serve to reposition the */
/* index cursor used by the query */

reposition q to rowid xxx.


THE QUERY CACHE

A query cache is a client mechanism that keeps the most recently read records of a query in the client's buffer pool. This speeds up browsing, when GET PREVIOUS and GET NEXT requests are issued repeatedly.

A query cache is created by specifying the CACHE option in the DEFINE QUERY statement. It is created by default for queries which are browsed by the 4GL browser.

The following program fragment shows how it can be used:

define query q for customer cache 10.
open query q for each customer no-lock.
get next q. /* gets cust 1 */
get next q. /* gets cust 2, leaves cust 1 in the cache */
get prev q. /* gets cust 1 from cache without access to */
/* database or server */


Query caches can only be used with queries that use NO-LOCK.


EXECUTION STRATEGY
==================

For almost all queries, there will be a variety of different ways to retrieve the desired data. Among the choices that must be made are:

- which index brackets to use

- how to use the index brackets in conjunction with each other

- what expression to use for record selection

This is decided by the PROGRESS 4GL compiler when a program is compiled. The resulting structures describing the execution strategy are stored in the generated r-code. For example, if the cust-num and zip fields are indexed and the city field is not, the query

for each customer
where ((cust-num <= 10) and (city = "Boston")) or (zip > 01824):
end.


is compiled by generating the following data and storing it into the r-code:

- index bracket r-code, which includes two index brackets, one on the cust-num index and one on the zip index, combined via an OR operator

- selection e-code (Expression Code Segment), which contains the whole WHERE clause

Whenever possible, the compiler will use multiple index brackets.

For WHERE clauses that contain subexpressions connected by an OR operator, multiple brackets will be used when there are usable brackets on both sides of the OR.

For WHERE clauses that contain subexpressions connected by an AND operator, multiple brackets will be used when all the key components are used in equality matches, and the index on one side is not unique.

The compiler uses the following rules for ranking the available indexes when analyzing brackets and deciding which indexes to make
use of:

1) An index that was specified in USE-INDEX

2) A unique index when all key components are used for equality matches.

3) The index with the most equality matches on leading index fields

4) The index with the most range matches

5) Word indexes referenced by the contains operator

6) The index with the most sort matches (BY)

7) A table's primary index

8) The index that comes first alphabetically, by index name

You can determine which indexes will be used by a particular query by examining the output produced by the cross-reference (XREF) option of the 4GL compiler. Each index bracket that will be used will produce one line marked with the tag "SEARCH". When a default bracket on an entire index is being used, it will be marked "WHOLE-INDEX". Sorts required for a BY <column> when there is no suitable index will be marked with the tag "SORT-ACCESS".

Below is an extract from the cross-reference produced by compiling some of the examples used in this section. Only those lines relevant to queries are shown.

1 COMPILE p3.p
1 CPINTERNAL iso8859-1
1 CPSTREAM ibm850
3 SEARCH demo.customer cust-num WHOLE-INDEX
6 SEARCH demo.customer cust-num WHOLE-INDEX
6 SORT-ACCESS demo.customer Phone
9 SEARCH demo.customer cust-num WHOLE-INDEX
15 SEARCH demo.customer zip
19 SEARCH demo.customer name
23 SEARCH demo.customer name
27 SEARCH demo.customer zip
31 SEARCH demo.customer name
37 SEARCH demo.customer zip
37 SEARCH demo.customer zip
41 SEARCH demo.customer zip
41 SEARCH demo.customer zip
48 SEARCH demo.customer name
48 SEARCH demo.customer zip
48 SEARCH demo.customer name
56 SEARCH demo.customer name
56 SEARCH demo.customer zip
56 SEARCH demo.customer name
56 SEARCH demo.customer zip

CHARACTERISTICS OF FOR EACH EACH QUERIES
========================================

- SORT : yes
- JOIN : yes
- DIRECTION : forwards only
- FIELD LISTS : yes
- PREFETCH : with no-lock; at presort
- CACHE : no
- VARIABLE BINDING: once, before entering block
- QBW : yes

EXECUTION

In version 6, FOR EACH queries use a single index cursor and a single index bracket, and their performance is similar to that of FIND queries. In version 7 and later, they use server queries, and utilize multiple index brackets and multiple indexes when possible.

There are two ways to force FOR EACH queries in version 7 to behave like they did in version 6: by using the -v6q startup parameter, or by using the USE-INDEX clause.

BLOCK ORIENTATION

FOR EACH queries are executed within an iterating 4GL block; the next record is always fetched at the top of the block.

RECORD ORDERING

The order of retrieved records is "random" when multiple indexes are used. Otherwise it is based on the index that was chosen. If multiple indexes are possible, the BY phrase and USE-INDEX can be used to guarantee a particular order. In version 6, with USE-INDEX or when using -v6q the order is guaranteed to be that of the index used.

POSITIONING

FOR EACH queries always move forward. The next record is fetched at the top of each iteration, and no navigation is allowed. There is, however, an exception to this rule in version 6, or when using the USE-INDEX or -v6q in later versions: the index cursor can be repositioned via a FIND into the same buffer in a sub-procedure; see the later section about FIND queries.

VARIABLE BINDING

FOR EACH queries evaluate variables used in the WHERE clause once, before entering the block. For example, the  program fragment

i = 5.
for each customer where (cust-num > i):
display cust-num.
i = 1.
end.


gets all customers where cust-num > 5. The fact that the value of the variable i is changed inside the loop doesn't affect the WHERE clause or the outcome of the query.

QUERY CACHE

FOR EACH queries do not use the query cache. A cache is useful only when accessing records more than once, and FOR EACH queries normally do not.

PREFETCH

With NO-LOCK, prefetch is the default for FOR EACH queries. It can be turned off by specifying NO-PREFETCH. If a presort is done, the presort always uses prefetch and field lists, retrieving only the necessary fields and using network messages containing as many records as possible.

CHARACTERISTICS OF PRESELECT QUERIES
====================================

- SORT : yes
- JOIN : yes
- DIRECTION : first, next, prev, last
- FIELD LISTS : yes
- PREFETCH : only at pass 1
- CACHE : no
- VARIABLE BINDING: once, before entering block
- QBW : yes

EXECUTION

As far as the server is concerned, PRESELECT queries execute exactly like FOR EACH queries.

On the client, PRESELECT queries work in a two pass sequence, similar to that of a sorting FOR EACH query: first all records that satisfy the query are read, and a complete result list, with all the ROWIDs, is prepared, and, if necessary, sorted. Then, in response to FIND statements, the records are read again, this time via their ROWIDs
from the result list, and passed to the 4GL program.

For example:

do preselect each customer: /* prepares complete result list */
find next customer. /* gets rowid from list, reads rec*/
find last customer. /* gets last rowid from list, */
/* reads record */
end.


BLOCK ORIENTATION

A PRESELECT query is specified in the header of a 4GL block - either a REPEAT block or a DO block. The preselect pass (the first pass) is completed before the block is entered. Records are read within the block via FIND statements.

RECORD ORDERING

The order of retrieved records is not guaranteed unless a BY or USE-INDEX clause is used. In version 6, without USE-INDEX or when using -v6q, the order is guaranteed to be that of the index used.

POSITIONING

A PRESELECT query can move to the next, previous, first, or last record in the result list. The query cannot be positioned by any other means.

VARIABLE BINDING

PRESELECT queries evaluate variables used in the WHERE clause once, before entereing the block, exactly like a FOR EACH.

QUERY CACHE

Not used.

PREFETCH

Prefetch is used in the first pass, reading the ROWID and, if necessary, sort fields, with messages that contain as many records as possible. It cannot be turned off.


CHARACTERISTICS OF FIND QUERIES
===============================

- SORT : no
- JOIN : no
- DIRECTION : first, next, prev, last, auto
- FIELD LISTS : no
- PREFETCH : no
- CACHE : no
- VARIABLE BINDING: for every record
- QBW : no

EXECUTION

FIND queries always use a single index cursor and single index bracket. The WHERE clause, and optionally a USE-INDEX clause, determine which index is used. The index and buffer used by the query define which index cursor is used. Thus, if two queries use the same buffer and the same index, they use the same index cursor.
For example, the following three queries all use the same index cursor:

find first customer where (cust-num > 10) use-index cust-num

find next customer where (name = "Mary") use-index cust-num

find prev customer where (cust-num < 100)


Thus, and index cursor is not owned by a specific FIND query, and can be used by multiple FINDs. As a result, a FIND query can change the position of an index cursor used by another query and thus affect the result returned by the other query.

BLOCK ORIENTATION

Cursors used by find queries are scoped to the scope of the associated buffer.

RECORD ORDERING

The order of returned records is determined by the index used, which can be specified using a USE-INDEX clause. A BY clause is not allowed.

POSITIONING

In addition to allowing FIND NEXT, PREV, FIRST and LAST, FIND queries also get repositioned automatically by other queries: when a FIND or FOR EACH query fetches a record, all the index cursors which are used by FIND queries for the same buffer are positioned to the same record. For example, if a customer table contains rows with the following values for the columns cust-num and name:

CUST-NUM NAME
======== =======
1 Mary
2 Amnon
3 Chip
4 Sue
5 Jane

The following program fragment, which uses two index cursors - one on the cust-num index and one on the zip index - displays customer number 4:

find first customer where (name = "chip")

find next customer where (cust-num > 0)

display cust-num name


The first FIND finds customer number 3, and causes the index cursor on the cust-num index to be positioned to the same record. The next statement uses the cust-num index, which is now positioned on customer 3, and finds customer number 4. If we omit the first FIND, the program will find and display customer number 1.

The automatic cursor repositioning is performed by the client, and involves the following steps:

- finding all the index cursors used by other FIND queries for the same buffer. This includes cursors used for FOR EACH queries in version 6, or in later versions if the FOR EACH included a USE-INDEX clause, or was compiled with the -v6q flag.

- extracting from the record, for each cursor, the keys used by its index, and building a copy of the entry for the record in that index.

- sending FIND requests to the server using each cursor and the respective key. This positions the cursor on the index entry for that record. In version 6, the FIND request was sent to the server as soon as the original record was found; in version 7 and later, this is postponed until the cursor is used again. This can improve performance significantly for some applications because a cursor may be repositioned multiple times before it is used, it may never be used again, or its next usage may not depend on its current position (e.g., FIND FIRST).

If a buffer is shared my multiple .P's, the cursor will be shared only if the top level .p has a FIND NEXT on that cursor.

VARIABLE BINDING

In FIND queries, variables used in the WHERE clause are evaluated each time before they are executed. For example, the program fragment

i = 5.
repeat:
find next customer where (cust-num > i):
display cust-num.
i = 53.
end.


displays customers 6 and 54. Since the value of i in "cust-num > i" is evaluated each time the FIND is executed, the query looks for customers with cust-num > 53 in the second iteration.

QUERY CACHE

Not used.

PREFETCH

Not used.


CHARACTERISTICS OF GET QUERIES
==============================

- SORT : yes
- JOIN : yes
- DIRECTION : first, next, prev, last, reposition
- FIELD LISTS : yes
- PREFETCH : with scrolling no-lock; at presort
- CACHE : yes
- VARIABLE BINDING: once, before opening query
- QBW : yes

EXECUTION

GET queries use multi-bracket queries, and utilize multiple index brackets when possible.

BLOCK ORIENTATION

None. GET queries can span multiple blocks, procedures and modules.

RECORD ORDERING

Not guaranteed unless a BY or USE-INDEX clause is used.

POSITIONING

In addition to allowing GET NEXT, GET PREV, GET FIRST and GET LAST operations, GET queries also support the REPOSITION operation, which moves the current queryposition to an arbitrary row within the query result.

GET NEXT statements are supported for any query.

GET PREV, GET LAST and GET FIRST statements are supported if either
of the following is true:

1) the SCROLLING option is defined on the query

2) the query uses a single index cursor

The REPOSITION statement is supported only if SCROLLING is defined, since it requires a result list. It can move the query some number of rows forward or backward, to a specific row in the result list, or to a record with a specific ROWID.

Most of these operations are performed by the client, using the result list. The server can perform the following operations:

1) all queries; getting the next record

2) queries using a single index bracket: getting the previous, last and first record; repositioning the index bracket to a
specific record. The latter is similar to FIND query repositioning, and is used only for REPOSITION TO ROWID when
the desired record is not yet in the result list, and INDEXED-REPOSITION is specified.

It should be noted that the result list operations are sometimes time-consuming. For example, if the result list is not complete, a GET LAST on a multi-index query requires fetching all the remaining records in the query to complete the result list. When used correctly, however, the result list provides excellent navigation capabilities for browsing.

VARIABLE BINDING

Variables referenced in the WHREE clause or a GET query are evaluated once, when the query is opened. For example, the program fragment

i = 5.
open query q for each customer where (cust-num > i).
get next q.
display cust-num.
i = 1.
get next q.
display cust-num.


displays customers 6 and 7. The change in the value of i doesn't affect the query.

QUERY CACHE

Used when the CACHE option is specified, or, by default, when a browser is defined on the query.

PREFETCH

When SCROLLING (or a CACHE) is specified, and NO-LOCK is used, prefetch is the default, but can be turned off via a NO-PREFETCH option. If a presort or PRESELECT is done, the presort always used prefetch and field lists, retrieving only the necessary fields with messages that contain as many records as possible.
Workaround
Notes
Progress Documentation is available for viewing online and also for download in PDF format:

References to Other Documentation:
Progress Article(s):
Article 000020490: OpenEdge: References to Online Documentation

References to Other Documentation:
ABL, "Web Paper: ABL Database Triggers and Indexes", Database Index Usage
Attachment 
Last Modified Date2/27/2018 4:39 PM