Consultor Eletrônico

FIND, FOR EACH x WHERE ... OR sequential search & lock 915


INTRODUCTION:
=============
This Technical Support Knowledgebase entry describes how to use
PROGRESS 4GL to select records efficiently when the selection criteria
are such that either one condition or another (or others)
must be met.

WHY YOU NEED TO KNOW THIS:
===========================
In PROGRESS Version 6 and earlier, the server process uses
information from a single index to determine the subset of records
being selected by the client process. The server is restricted to
identifying a single, contiguous subset as identified by the data in
the index blocks. An OR operator in a WHERE clause does not, in and
of itself, identify a contiguous subset. Consequently,
ALL records that meet any selection criteria up to that point, if any,
are returned to the client process for testing against the selection
criteria. This not only generates extra CPU activity to execute the
test, but can also lead to other side effects. On a remote client,
it will also generate unnecessary network traffic sending records to
the client CPU for testing. And, unless the records are being read
with a NO-LOCK, all records being sent to the client for testing are
SHARE-LOCK'd (or EXCLUSIVE-LOCK'd), even on a FIND FIRST/LAST
request. (Doing so ensures that the data on the records yet to be
tested cannot be changed by another user during the time it takes to
complete the test.) This often leads to a Lock Table Overflow error.

PROCEDURAL APPROACH:
====================
To provide the OR selection functionality, without using the OR
statement in the WHERE clause, set up WORKFILE records where each
condition being selected is represented by its own workfile record.
Then use a FOR EACH block against the workfile records in which
another FOR EACH block against the real records is nested.

This approach will work provided the field(s) being tested are
already indexed fields.

For example, suppose you need to identify all the orders for a
customer where the order-num is either 4 or 15, because you believe
there are problems to be straighted out on the records, but you
aren't sure which one has the problem.

Define a workfile which has the same fields as the index you would
use. Create a workfile for each unique condition you are
testing for, and assign the corresponding values.

ON-LINE PROCEDURES OR UTILITIES:
===============================
/* This block will cause all orders for customer #6 to be SHARE-LOCK'
not just those with the desired order numbers, until all records
for customer #6 are read. */

FOR EACH order WHERE cust-num = 6 AND
(order-num = 15 OR order-num = 4):
DISPLAY cust-num order-num WITH TITLE "OR CLAUSE".
END.

/* This section of the procedure causes only the one record being
examined during the wf-order loop to be locked, and only for the
duration of that iteration of the loop. This can be verified by
adding pause statements within the block and using another client
process to get an EXCLUSIVE-LOCK on another customer #6 order. */

DEF WORKFILE wf-order
FIELD cust-num LIKE order.cust-num
FIELD order-num LIKE order.order-num.
CREATE wf-order.
ASSIGN wf-order.cust-num = 6
wf-order.order-num = 15.
CREATE wf-order.
ASSIGN wf-order.cust-num = 6
wf-order.order-num = 4.
FOR EACH wf-order:
FOR EACH order WHERE order.cust-num = wf-order.cust-num AND
order.order-num = wf-order.order-num:
DISPLAY order.cust-num
order.order-num WITH TITLE "USING WORKFILE".
END.
END.


REFERENCES TO WRITTEN DOCUMENTATION:
====================================
PROGRESS Language Reference: the "FOR Statement" reference entry,
"NOTES" section; the "COMPILE Statement" reference entry, the
definitions for LISTING and XREF filename

PROGRESS Programming Handbook:
Chapter 8, "Transactions and Error Processing";
Chapter 12, "Writing Multi-user Applications," especially
the section on "Resolving Locking Conflicts"

Progress Software Technical Support Note # 11784