Subject Re: Foreign Keys vs Performance
Author Adam
--- In, "Martijn Tonies"
<m.tonies@u...> wrote:
> > > What is recommended when using foreign keys (apply all fk's or apply
> > > fk where it's most needed?)?
> >
> > There are two sides pulling against each other. The purist would
> > demand each FK explicitly defined, and there is good argument for
> > this.
> Indeed there is.
> >If you do not define the FK, then you must write custom triggers
> > to emulate a FK, and risk inconsistent data if you do it wrong. At
> > least with FK's defined, you can not possibly store a child record
> > without a parent record to reference.
> Triggers won't work properly because of transaction isolation.

Good point Martijn. If you have multiple simultaneous transactions,
then your triggers may not realise that the parent record has been
deleted by a transaction that is outside your isolation visibility.

Of course a lot of systems that have a batch insert mode don't have
multiple concurrent transactions so it might still be an option in
those cases.

> > The other side of the argument (realist) is that the purist has his
> > head in the sand. You are dealing with the performance implications of
> > an implementation decision within Firebird, and in some cases, this
> > will slow it down enormously.
> Rubbish. If the implementation (in this case Firebird) can't bring
> performance
> because of a logical issue (the constraint), then the developer
should bark
> at the company/people who implement the system (Firebird developers) and
> demand better performance :-)
> Guess what happened -- Firebird 2 gives you much better performance on
> indices with a large number of duplicates. Problem solved.

Yes, but FB 2 is still beta, so problem will be solved sometime
hopefully very soon, but not yet unless you are prepared to do a
production release on a beta database.

> > A "compromise" would be to define the FKs, but to drop the constraints
> > during the batch insert if they were really causing an important
> > performance bottleneck, but then you would need to make sure the data
> > you import is "clean".
> >
> > I find it is good to be a purist at heart, but to accept that there
> > are times when you may have to be a realist about it.
> IMO, I would drop a bit of performance (not talking about bulk insert
> modes) for consistency every day...

ditto. The difference between waiting 5 minutes or 10 minutes for a
process is actually not as significant in most business cases.
Particularly if you then lose that 5 minutes later on when someone has
to manually fix the inconsistent data.