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.
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:
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:
Suppose the following SQL statement is issued in SQL Command:
UPDATE schedtour st
SET 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:
FROM OPS$CP1.TOUR t1
WHERE (t1.DESTINATION = t2.DESTINATION
AND t1.TOUR_TYPE = t2.TOUR_TYPE
FOR UPDATE OF t2.DESTINATION
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.