Subject Re: foreign keys ans slow insert
Author Svein Erling Tysvær
--- In, Tim Ledgerwood wrote:
> Helen,
> I have found it faster to use as few indices as possible. It speeds
> up query and SP execution time incredibly. Is this anything to do
> with this problem?

Not all too much, this problem is many duplicates within an index so
that Firebird get problems finding the record within the index. It is
a problem manifesting itself while only using one index (in this case,
automatically created by a foreign key), whereas having too many
indexes available only becomes a problem if Firebird selects two or
more of them for a table that is not the first to appear in your plan.

> The reason I ask is because, coming from a dBase/Paradox background,
> I thought (in the beginning) that I needed indices for just about
> everything.
> With Firebird, I've learned not to put in indices.

It is true (at least in Fb 1.0) that Firebird tend to use indexes a
bit too often (if it's already using a unique index for
customernumber, then it shouldn't be neccessary to also use an index
for country of residence), but it is not true that the solution to
that is to avoid having indexes altogether. Rather, think about why
you need an index before creating it and then - when writing queries -
take a look at the plan before asking Firebird to spend the next
fortnight answering your question.