> I have a database with 34 tables. I have applied foreign keys for all
> the relations between the tables (a total number of 48 FK). Now I'm
> starting to wonder about the performance for this database. (I use
> IBExpert which creates corresponding index/constraint to all the FK,
> so the number of indexes is almost 90.)
>
> Occasionally I insert/update 30.000 records in a batch operation. This
> operation mainly uses the table with the most FK's (7 FK). Will this
> operation be much slower with all the foreign keys?
>

There are two things that are potentially problems with Foreign keys
being defined as an index. Firebird does not perform well when the
index has lots of duplicates. The nature of some FK relationships
means that sometimes these indices will always have poor selectivity.
Firebird 2 resolves some of this problem with respect to garbage
collection.

The other problem is that each insert must maintain the index which is
more expensive than adding all the records, then building the index.

> 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. 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.

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.

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.

Adam