When you design applications that use referential integrity features:
Do not create unnecessary referential constraints. The more referential constraints a table has, the slower a statement requiring referential integrity runs on that table.
Use as few self-referencing constraints on a table as possible.
Use check constraint rather than references constraint for applications that check a limited, specific range of values. Using check constraint eliminates the need for Adaptive Server to scan other tables to complete the query, since there are no references. Therefore, queries on such tables run faster than on tables using references.
For example, this table uses a check constraint to limit the authors to California:
create table cal_authors (au_id id not null, au_lname varchar(40) not null, au_fname varchar(20) not null, phone char(12) null, address varchar(40) null, city varchar(20) null, state char(2) null check(state = "CA"), country varchar(12) null, postalcode char(10) null)
Bind commonly scanned foreign-key indexes to their own caches, to optimize performance. Unique indexes are automatically created on primary-key columns. These indexes are usually selected to scan the referenced table when their corresponding foreign keys are updated or inserted.
Keep multirow updates of candidate keys at a minimum.
Put referential integrity queries into procedures that use constraint checks. Constraint checks are compiled into the execution plan; when a referential constraint is altered, the procedure that has the constraint compiled is automatically recompiled when that procedure is executed.
If you cannot embed referential integrity queries in a procedure and you must frequently recompile referential integrity queries in an ad hoc batch, bind the system catalog sysreferences to its own cache. This improves performance when Adaptive Server recompiles referential integrity queries.
To test a table that has referential constraints, use set showplan, noexec on before running a query using the table. The showplan output indicates the number of auxiliary scan descriptors required to run the query; scan descriptors manage the scan of a table whenever queries are run on it. If the number of auxiliary scan descriptors is very high, either redesign the table so it uses fewer scan descriptors, or increase the value of the number of auxiliary scan descriptors configuration parameter.
