Query Combining

Previous Next

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 behaviour

On Oracle and on Microsoft SQL Server, the behaviour is as described in the example.

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

 

See also

Change in Functionality With Query Combining