Bookshelf Home | Contents | Index | Search | PDF

Siebel Tools Reference > Performance Improvement > Multi-Value Group Performance >

Multi-Value Links and the CheckNoMatch Property

---

When a multi-value link has been configured with a primary join (the typical situation) there are circumstances in which the foreign key used by this join to identify the primary record is unable to find the primary record. For example, when the primary record has been deleted from the multi-value group, or the multi-value group is newly created and has no records, the multi-value link may be configured to update the primary foreign key to a value of NULL or to a special value of NoMatchRowId (depending on your requirements). This behavior is configured through the CheckNoMatch property of the Multi Value Link object type, and may have negative performance consequences.

The purpose of this special NoMatchRowId value is to prevent secondary queries on foreign key values that are known to have failed, thereby improving performance, much in the same way that using a primary join improves performance.

The NoMatchRowId generating and testing behavior is activated by setting CheckNoMatch to FALSE for the MVL. This setting has the following results:

A query encounters a master record in which the primary foreign key is NULL or invalid. A secondary query is performed to determine if there are detail records in the multi-value group. If the secondary query finds no detail records, the primary ID field is set to the special value NoMatchRowId.
A query encounters a master record in which the primary foreign key has the value NoMatchRowId. This indicates that there are no detail records in the multi-value group and the secondary query is not performed.

If you set CheckNoMatch to TRUE, the Siebel application will perform a secondary query whenever one of the following is true:

There is no outer join on the primary record
The outer join on the primary record is set to NULL
The outer join on the primary record is set to NoMatchRowId

If the secondary query finds a matching detail record, and the MVL has an Auto Primary property setting of DEFAULT, the foreign key is updated with that record's row ID. If no matching child record is found, or Auto Primary is set to NONE, the application leaves the existing value intact.

A CheckNoMatch setting of TRUE can have serious negative performance consequences. If a multi-value group is sparsely populated (that is, most master records do not have any detail records in the multi-value group) and has CheckNoMatch set to TRUE, it will be almost as slow as not having a primary join at all.

The following cases illustrate the consequences of CheckNoMatch settings.

CheckNoMatch = TRUE and the AutoPrimary = Default

The Siebel application tries to update the Primary foreign key when a 'NoMatchRowId' is encountered. In this case, the Siebel application issues a secondary query, and if it find a valid child record, will update the foreign key column with the valid ID. If it does not find any child records, it does not change the foreign key value.

CheckNoMatch = TRUE and the AutoPrimary = None

The Siebel application does not try to update the Primary foreign key when a 'NoMatchRowId' is encountered. In this case, the Siebel application issues the secondary query for the child records but does not update the Primary ID field. In this case, you set a Primary for the MVG by explicitly clicking the MVG ellipsis (...) button to invoke the MVG applet. Depending on the value of the AutoPrimary property or what the user selects, a Primary is set.

The only type of foreign key that is set to NULL or NoMatchRowId is a Cascade Delete that is set to Clear on a Link object. If you have an All type view and child records do not exist on the database, the primary foreign key for MVLs only is set to NoMatchRowId. For example, you may have remote users with unknown foreign key references on their mobile databases. In this case, giving an All type of view will not set foreign keys on the Server to NULL during synchronization.

However, giving All views to mobile users yields the same negative performance consequences as setting the MVG Primary foreign keys to NoMatchRowId. There are other reasons to restrict remote users from accessing All views:

Even if using them does not cause data corruption, it would still be misleading, since both parent and child data in applets might not be displayed, even though the data exists on the server.
Depending on how the application is configured, they might be able to navigate from the All view to another view that does indeed have MVGs.
There may be MVGs being checked or populated even though they are not displayed on the view itself.

In summary, know your requirements and be aware of the performance implications of the CheckNoMatch property before setting it to TRUE.

The default settings for a multi-value link are:

AutoPrimary, CheckNoMatch = FALSE
Primary ID field specified
UsePrimaryJoin = TRUE

These default settings cause a query for child records. If no child records are found, the PrimaryId field is set to NoMatchRowId. When there is a NoMatchRowId for a Primary foreign key and the CheckNoMatch property for the MVL is set to FALSE, a secondary query is not issued at that time. If child records are added at a later time (for example, through EIM), the PrimaryId field is not updated until the user checks on the actual MVG (clicking the ellipsis (...) button) and explicitly selects a primary. In addition, if the user adds the first entry through the MVG, it will set the Primary ID foreign key to the ID for that record without requiring the explicit setting of the primary. For example, when you add the first address in the Address MVG on Accounts, this first record automatically becomes the Primary.

Bookshelf Home | Contents | Index | Search | PDF