Query Combining

Tip

For UPDATE statements, let the Rules Engine combine the statement used to retrieve and lock the records to be updated with the internal SQL statement used to determine the new values.

Background

Before USoft Developer 5.2, UPDATE statements were always handled as follows: one SQL statement retrieved and locked the records to be updated and for each selected record the new values were queried (and then the record was updated). The BenchMark reasons for these SQL statements were SELECT_FOR_UPDATE and NEWVALUES.

As of USoft Developer 5.2, in some cases these statements can be combined into one single OLD_NEWVALUES statement. This reduces the number of SQL statements sent to the database.

When to use

The Rules Engine is NOT able to combine the SELECT_FOR_UPDATE statement with the NEWVALUES statements if the SET clauses of the UPDATE statement contain:

·	An INVOKE, ROWNUM, or UNION keyword.

·	An outer-join condition.

·	A GROUP BY clause.

·	A group function.

·	A CONNECT BY clause.

These statements are NOT combined for Update on Self constraints.

For applications built in USoft Developer 5.2 or higher, the SELECT_FOR_UPDATE and NEWVALUES statements are combined by default. For upgraded applications, or applications older than USoft Developer 5.2, you have to combine these statements:

1.	Open the Definer.

2.	From the menu, select Tools, Rules Engine Parameters.

3.	Query for the Parameter Setting with name COMBINE_QUERIES.

4.	Change the Value to True.

Example

Suppose the following SQL statement is issued in SQL Command:

UPDATE schedtour st

SET num_days =

(SELECT t.num_days

FROM tour t

WHERE t.destination = st.destination

AND t.tour_type = st.tour_type)

If the COMBINE_QUERIES parameter has been set to True, one single SQL statement retrieves the scheduled tours to be updated and the number of days of the corresponding tour. This SQL statement has reason OLD_NEWVALUES:

SELECT t2.DESTINATION

, t2.TOUR_TYPE

, t2.NUM_DAYS

, t1.NUM_DAYS

FROM OPS$CP1.TOUR t1

,OPS$CP1.SCHEDTOUR t2

WHERE (t1.DESTINATION = t2.DESTINATION

AND t1.TOUR_TYPE = t2.TOUR_TYPE

)

FOR UPDATE OF t2.DESTINATION

Database Behavior

On Oracle, Sybase, Open Client, and Microsoft SQL Server the behavior is as described in the example.

On Solid however, the behavior is slightly different. The OLD_NEWVALUES statement does not only lock the records to be updated but also locks the records with the new values. The reason being that on Solid you cannot specify the individual records to be locked. The result is that all records are locked.

On Informix, ODBC, and DB2, query combining is not supported. These databases do not allow for a SELECT_FOR_UPDATE clause with a join.