Set Oriented Manipulations

Before USoft Developer 5.2, the Rules Engine would always handle manipulations record-by-record. This means that for each record to be manipulated, the Rules Engine checks the record against all restrictive and corrective rules, and after that manipulates the specific record.

As of USoft Developer 5.2, the Rules Engine will only handle manipulations record-by-record if constraints or relationships need to be checked. If no restrictive or corrective rules have to be checked, the Rules Engine handles the manipulation in a set-oriented way and sends the manipulation directly to the database.

By default, set-oriented manipulations will always be used (if possible) in batch applications, and are not allowed in online applications.

In the Authorizer, the Allow Set Manipulations additional parameter specifies whether manipulations are handled set-oriented or not. This parameter can be specified separately for batch and online applications. The default value of this parameter is 'False' for online applications and 'True' for batch applications.

Example

This example demonstrates the difference between handling a statement record-by-record or as a set. Suppose there is an ORDER table and an ORDER_HISTORY table. Some constraint inserts a record in the history table every time the total amount of the order changes.

A batch job, which runs once a month, removes all occurrences of closed orders in the history table.

ORDER
ID	Total		Status
1	15000		CLOSED
2	12750		OPEN
ORDER_HISTORY
Order ID		Datetime		Total
1		23-SEP-1999 13:59:35		12000
1		23-SEP-1999 15:21:15		13200
1		24-SEP-1999 09:01:21		15000
2		24-SEP-1999 08:41:14		12750

The batch job contains a SQL statement:

DELETE FROM order_history oh

WHERE EXISTS (SELECT ''

FROM order o

WHERE o.id=oh.order_id

AND oh.status = 'CLOSED'

)

If manipulations are handled record-by-record, the Rules Engine:

1.	Locks all child records with order_id 1in the ORDER_HISTORY table.

2.	Deletes the first record with order id 1.

3.	Deletes the second record with order id 1.

4.	Deletes the third record with order id 1.

If manipulations are handled set-oriented, the handling of this batch job statement has been optimized: all history records are now deleted in one single SQL statement:

DELETE FROM ORDER_HISTORY

WHERE EXISTS (SELECT ''

FROM ORDER t1

WHERE ORDER_HISTORY.ID=t1.ID

AND t1.STATUS = 'CLOSED')

)