At 10:24 AM 4/7/00 -0700, you wrote:

>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).
>
>While it's pure speculation, we did have quite a few of the RI checks that
>you're talking about (large tables doing RI to a small table).
>
>If I understand correctly from the discussion here, these RI relationships
>are maintained by 'sparse' indexes where the same value is kept in a chain.
>This would create terrible performance for us. For example:
>

Please forget everything you read about sparse/dense indexes. It
was gibberish that had nothing whatsoever to do with Interbase. It
especially had nothing to do with insert.

The problem, I believe, was insertions through BDE. There are at
least two known problems with BDE. First, BDE tends to do a commit
after every record, which is grossly inefficient. Secondly,
BDE leaks memory like a sieve. Neither can be addressed by Interbase.

For the record, an Interbase index node consists of the following:

1 byte of prefix length
1 byte of key length (excluding prefix)
4 bytes of record number
variable length key

Each node contains only that part of the key that differs from
the preceding node. This means that duplicate entries are
6 bytes long (prefix length == full key length).

Duplicate index nodes are inserted at the head list. There is
no degradation based on number of duplicates for either insert
or scan.

There is a problem with index delete in finding a specific entry
among a very large number of duplications. As Ann reminds me
about once a week, that could be remedied by propogating the
record number as part of the key in the upper level of the index.
This would reduce index fan out slightly, but eliminate the
duplication deletion problem. It would have no effect on either
insertion or scan.

This list exists for the purpose of discussing the internal
architecture of Interbase. It should be used to exchange
information. It should not be a place to state opinions and
guesses as fact. I tend to get cranky when people foist off
gibberish as fact (sparse/dense indexes, indeed!) and upset
when other people accept gibberish as fact. I'm perfectly
happy to explain how things work, why they were done that
way, alternatives considered and rejected, alternatives
overlooked, just plain alternatives, and how I would do
things differently in 2000. I don't particularly want to spend
a lot of time correcting misinformation invented by a (
Ann would say, "an uninformed person").

So if you think you understand something and would like confirmation
or correction just ask. If you think you have a better idea,
pipe up. Just don't proclaim ignorance as knowledge. And
be prepared to jumped on if you do.

Jim Starkey