SYS_MGR.PRE_MATCH

By default, Maintain first ensures a database row exists before it updates or deletes it and ensures a database row does NOT exist before including a new row. For example, when Maintain processes an INCLUDE, it first issues: 

SQL SELECT keyfld FROM tablename WHERE keyfld = keyvalue;

Then, it only proceeds with the SQL INSERT if the SELECT returned no rows. Many applications are structured so that the designer knows that the row does not exist, so the preliminary SELECT is not needed.

The same is true for DELETE and UPDATE. Only the SELECT must return a row before MAINTAIN continues with the SQL DELETE or SQL UPDATE.

When the application warrants it, you can turn off the preliminary SELECT against relational databases by changing the value of SYS_MGR.PRE_MATCH. For high volume transactions, this can positively affect performance.

Note:

  • Since you are not checking to see if the row exists or not, it is important to write code that catches errors by inspecting FOCERROR.
  • The PRE_MATCH setting is local to the current MAINTAIN procedure. Changing it does not change the value in the parent procedure or in subsequently called procedures.

Set PRE_MATCH

The syntax is 

SYS_MGR.SET_PRE_MATCH{0|1};

or

SYS_MGR.PRE_MATCH = {0|1}

where:

0

Disables prematching.

1

Turns on prematching.

To check the current setting for pre-match, use:

SYS_MGR.GET_PRE_MATCH();

or 

SYS_MGR.PRE_MATCH;

Setting PRE_MATCH Off

Suppose you have a Maintain procedure with the following code: 

SYS_MGR.PRE_MATCH = 0;  -* stop pre-selecting
FOR ALL INCLUDE PRODUCTS FROM PRODSTACK;
SYS_MGR.PRE_MATCH = 1;  -* restore

If PRODSTACK has 5000 rows, setting PRE_MATCH to 0 before the INCLUDE reduces the number of database engine interactions from 10,000 to 5,000.