Re: Re: performance when inserting into child tables

One:
As you insert into the child, a parent ID has to be
checked. If the parent index is large and the child
inserts are randomly ordered, you may be losing a
lot of time because recently checked parent IDs
have been flushed and have to be re-read.

Option 1: can you order by the parent id before you insert
Option 2: can you move the parent index into a KEEP
pool that has been sized to hold the entire parent.

Two:
I've never checked this, but when you do the insert/select on
the child, it is possible that the normal array insert optimization
(which reduces the volume of undo and redo significantly) cannot
take place because of the intervening integrity test on each row.
In this case, it isn't the child insert that is slow, it's the parent
insert that's quick. (I'll check this some time, and try to remember
to report back).

Of course, there's always the possibility that the child insert is
relatively
slower because there are more indexes on the child than there were on
the parent; or because the child table is larger, and the 'cyclic flushing'
that I mentioned for the parent index is actually happening on the child
index as it is updated.

As Jared said - run a 10046 trace at level 8: this will tell you which
blocks are being waited on: most likely to be parent index leaf blocks
(thought 1) or leaf blocks from other child indexes (note above).