Overhead Costs of Standard Referential Integrity
By implementing standard referential integrity, you incur certain overhead costs that can have a negative effect on performance. The following table lists the various referential integrity overhead operations that affect performance.
|
Operation |
Description |
|||
|
Insert, update, or delete rows in a referencing table. |
Overhead is similar to that for USI subtable row handling. A redistributed spool for each reference is dispatched to the AMP containing the subtable entry. BYNET traffic incurs the majority of the cost of this operation. |
|||
|
Insert a row into a referencing table |
A referential integrity check is made against the Reference Index subtable. If it does, an entry with a count of 1 is added to the Reference Index subtable. |
|||
|
Delete a row from a referencing table |
A referential integrity check is made against the Reference Index subtable and its count for the referenced field is decremented. When the count decrements to 0, then the subtable entry for the Referenced field is deleted. |
|||
|
Update a referenced field in a referencing table |
Overhead is similar to that for changing the value of a USI column. A referential integrity check is made against the Reference Index subtable. Both the inserting-a-row and deleting-a-row operations execute on the Reference Index subtable, decrementing the count of the old Referenced column value and incrementing the count of the new Reference column value. |
|||
|
Delete a row from a referenced table |
The Reference Index subtable is checked to verify that the corresponding Referenced column does not exist. When nonexistence is confirmed, the row is deleted from the Referenced table. No BYNET traffic is involved because the Referenced column is the same value in the Referenced table and the Reference Index subtable. |
|||