Declarative FOREIGN KEY constraint -- where's the index, and how to cascade?

Declarative FOREIGN KEY constraint -- where's the index, and how to cascade?

Post by Rob Rothkop » Fri, 08 Jan 1999 04:00:00



My colleagues and I have recently undergone significant testing
procedures to see how different design choices can impact the
performance of batch (high-performance requiring) operations.

During our work, we found that by using declared FOREIGN KEY constraints
to enforce referential integrity we got better performance than if we
created indexes and checked the constraints using triggers (we're using
ERWin to generate the trigger RI code automatically).  Because SQL
Server does not implement any cascading RI rules (e.g. PARENT DELETE
CASCADE), we realize that it may be necessary still to create and
maintain indexes on child tables -- this is undesirable as MANY INDEXES
= LOWER PERFORMANCE.

A few questions for those technically "in the know":

When a Foreign Key constraint is defined, SQL Server makes sure that the
parent data exists on INSERTs (or else it will fail the insert
operation).

1)      Since there is no evident/necessary index on the parent table,
what is SQL Server using to efficiently enforce the RI rule?  Is there
some sort of "hidden index"?  Can we access it, and if not, why is it
more efficient than a conventional index?

2)      If you attempt to delete the parent row and a child has a
Foreign-Key constraint defined, and there is data in the child table
referencing the row you want to delete, SQL Server will very quickly
give you an error.  How does it effectively check the child table?  Is
there another "hidden index" on the child to enforce this, or is some
other technique used like a flag on the parent row indicating the
existence of children?

3)      If "hidden indexes" are being created, can we use/rely on them
for our own purposes (e.g. query optimization).  Are they on the parent,
child, both, or neither?

The second topic question is:

Has anyone run into performance problems due to excessive indexes
created by ERWin's RI index/trigger generation?  How did you go about
resolving the problems?

Thanks in advance!

 
 
 

Declarative FOREIGN KEY constraint -- where's the index, and how to cascade?

Post by Ian Posne » Mon, 11 Jan 1999 04:00:00


See below for my answers

--

Ian Posner
MindQuest Solutions Ltd


Quote:>My colleagues and I have recently undergone significant testing
>procedures to see how different design choices can impact the
>performance of batch (high-performance requiring) operations.

>During our work, we found that by using declared FOREIGN KEY constraints
>to enforce referential integrity we got better performance than if we
>created indexes and checked the constraints using triggers (we're using
>ERWin to generate the trigger RI code automatically).  Because SQL
>Server does not implement any cascading RI rules (e.g. PARENT DELETE
>CASCADE), we realize that it may be necessary still to create and
>maintain indexes on child tables -- this is undesirable as MANY INDEXES
>= LOWER PERFORMANCE.

>A few questions for those technically "in the know":

>When a Foreign Key constraint is defined, SQL Server makes sure that the
>parent data exists on INSERTs (or else it will fail the insert
>operation).

>1) Since there is no evident/necessary index on the parent table,
>what is SQL Server using to efficiently enforce the RI rule?  Is there
>some sort of "hidden index"?  Can we access it, and if not, why is it
>more efficient than a conventional index?

A foreign key is created by referencing a Primary Key in the parent table. A
Primary Key is a constraint that implicitly creates an index. As a general
rule, I use non-clustered indexes for primary keys, since the operation
which checks whether the parent record exists needs to perform an index seek
(finding a one/a few primary key values in that index) on the primary key,
and therefore does not need to go down to the leaf level of the index,
therefore the query involved is completely covered by the non-clustered
index which will give performance beyond that using clustered index in this
particular case.

Quote:

>2) If you attempt to delete the parent row and a child has a
>Foreign-Key constraint defined, and there is data in the child table
>referencing the row you want to delete, SQL Server will very quickly
>give you an error.  How does it effectively check the child table?  Is
>there another "hidden index" on the child to enforce this, or is some
>other technique used like a flag on the parent row indicating the
>existence of children?

It may be that in defining relationships through ErWin, it automatically
builds indexes on the foreign key fields (which is generally good practice).
You don't have to define an index on the foreign key field, but it is
generally a good idea.

As you've guessed, when deleting the parent record, SQL searches the foreign
key index if one is available, otherwise performs a table scan on the child
table looking for the primary key values about to be deleted.

It is often the case, that it helps to define your MAIN foreign key as a
CLUSTERED index, which not only will help this type of operation, but
reduces lock contention if chosen wisely (e.g. with a Customers table and an
Orders table, cluster the Orders table on CustomerID so that all the Orders
for a particular Customer are physically located adjacent to one another).

Quote:

>3) If "hidden indexes" are being created, can we use/rely on them
>for our own purposes (e.g. query optimization).  Are they on the parent,
>child, both, or neither?

They are automatically used by the optimizer. Remember -- Primary Keys
always create an Index -- Foreign Keys don't automatically create one.

As a guide to ref. integ., generally use DRI as opposed to triggers. Note
that if you do this, you cannot have cascading updates of Primary keys! For
this reason use surrogate primary keys (identity integers are my choice) and
eliminate the problem entirely! Additionally, if you want to enable
cascading deletes, do so by encasing two delete statements inside a
transaction, inside of a stored proc, deleting from the child table first.

That's it in a nutshell!

- Show quoted text -

Quote:

>The second topic question is:

>Has anyone run into performance problems due to excessive indexes
>created by ERWin's RI index/trigger generation?  How did you go about
>resolving the problems?

>Thanks in advance!