Subject Re: [IB-Architect] Foreign Key indexes
Author Markus Kemper

> Interesting. We too had an Interbase database where after inserting
> lots of records the inserts slowed to a crawl. We never found the
> problem (we shifted to MSSQL instead which didn't have the problem).

I do not believe the issue has to deal with RI constraints. However,
having them _does_ require additional overhead as one might expect.

I do not know what your above described method of INSERT was that
led you to MSSQL but, I can _almost_ guarantee that it was not an
InterBase problem.

If seen such behavior when developers load data with a TTable's
POST method with other TTable logic involved thus for every
record POSTed they were not knowingly fetching back to the client
the entire table. One might imagine how this might slow down
over time as a table's depth grew.

I have always seen the best data load results from using a
combination of appropriate transaction control (inserts per
transaction) with either a 'prepared' TQuery or a TStoredProc.
While others find them useful, I do not enjoy using TTables,
nor do I recommend them.

Another thing to look for are triggers firing behind the scenes
that are doing lookups on growing tables to perform their
operations. Posssible a (select *), subselect, or
select .. where exists() type of stuff.

Data load rates should be linear or at least predictable depending
on the design implementation. In the not so desireable cases I
have seen the load rates were predictable. Predictable in the
sense that I could guarantee to my customers that their load rates
would decline at a predictable rate should they choose their
current implementation which was usually born from legacy code
brought forward from older Paradox or xBase applications.

While I am sure it exists, I have yet to personally see an
application where InterBase could not exceed data load requirements
if implemented correctly.