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:

We have a million plus 'price' records that have a price type (1-8).
The 'price type' is controlled by an RI relationship to a price type
table.
Probably half the records are price type 1, the rest are type 2-8.

So, if I understand sparse indexes correctly, then whenever I add another
price record, with price type 1, it's going to have to traverse a list of
half a million price type 1 records in the index before getting to the
correct place to insert the record?

If this is the case, I'm not keen about removing the RI constraint.

Other databases I've dealt with (Paradox for example), deal with this by
(transparently) adding the 'primary key' of the record to the index value.
This would mean that I'd never have a half million entries for price type 1;
instead I'd have one entry for price type 1, record 1, and one for price
type 1, record 3, and one for... (you get the idea). This makes inserting
and deleting the records much faster. And since the index is rarely used
for anything except enforcing RI, and Price Types are (almost) never
deleted, this gets the 'correct' performance trade-off.

Am I understanding all this, or am I missing something? (And since this is
an Interbase Architectural discussion forum, what should Interbase do about
it?)



-----Original Message-----
From: Joseph Alba [mailto:jalba@...]
Sent: Friday, April 07, 2000 8:08 AM
To: IB-Architect@egroups.com
Subject: Re: [IB-Architect] Foreign Key indexes

>I don't believe that are any dense/sparse index scan issues with
>the current scheme. I realize that you do,

No. I don't. It's been fifteen years since my school days when I dabbled
into language/compiler design programming, file
structures and B-Trees on Apple II, CP/M, and Turbo Pascal (and a bit later,
IBM PC/XT),

These days, its mostly database design and staid accounting applications for
different clients. So sorry about those primitive dense/non-dense labels.I'm
sure you'll recognize this as textbook amateurism. Honestly, that's about
all I remember from school. So, I'm sorry if I sound annoying ignorant.

>but you don't seem to know how the current scheme works.

Yes, I don't. I'm actually grasping at straws. I have a vague idea about IB
indexes to avoid rattling of those disk heads (joke only, okay).

>it is rather difficult to know if you have a serious suggestion or are just

blowing smoke.

Okay, here's the smoke.

Several months back, I migrated an XBase setup to Interbase. (the database
contained millions of billing/collection records of more
than five years of an electric company that bills and collects 80,000
clients per month),The first few minutes processed thousands of records per
minute. But as the record count reached the hundred thousand records it
ground to a very slow 1 record per minute insertion, so that the computer
had been running for two days, but still it was not finished, after four
days, the computer ground to a halt of one record insertion every hour..

I tried going down to the API level, but still the same. Finally, through
the patient guidance of Ann and Bill, I got wind of selectivity issues of
indexes, and I realized that I had a foreign key referencial integrity
constraint on these millions of billing records, because the AREACODE field
of these records was referencing an AREA table which had only 15 tuples.


I cancelled the referential integrity constraint, and disabled all indexes
and the transfer got through in a few hours. Without any change in the API
program. I tried my FIB version and it finished at about the same time as
the API program. The BDE version was a bit slower but still the transfer was
finished within the day.


So, with indexes / referential integrity constraint on, the transfer went
though a very bad graph which reminded me of a graph that zoomed upwards
infinitely as it approached its upper limit.

So, is there a problem with just one type of indexes or what?

Is it alright to dream about the day when IB would have an indexing
structure that won't have selectivity problems, and database designers can
naturally design referential integrity constraints of foreign keys, without
being concerned with selectivity issues?

So, you see, I'm not suggesting an alternative. I'm pointing out a problem I
experienced it with IB. (for which I do have a workaround using primary key
suffixes, and implementing referential integrity through triggers instead of
the natural FOREIGN key syntax.

>Disks haven't changed as much as you might think.

When I was in school, our microNova minicomputer had removable 10 megabyte
disks, and it was such a big deal.
The database operations which took days to finish, now only takes a few
minutes.
Now, it is ordinary to have 9 gigabyte hard disk on a PC.

Joseph Alba
jalba@...





------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/955119710/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com