Den 2011-08-23 09:58 skrev Vander Clock Stephane såhär:

> we have on table with 80 millions of reccords (growing). each records is
> assigned to a defined Contact ID. Only one contact ID can access read
> his record.
> i thing it's a pretty classical scenario ...
> we have 2 option :
>
> 1/ keep all the reccord in one unique table and add a column Contact_ID
> and filter all queries on this colum
> Good: easy, only one table to manage
> Bad: very big table and very heavy index (all index (mostly all) must
> have the field Contact_ID to be efficient)
>
> 2/ create for each contact his dedicated table xxx_ContactID.
> Good: performance because all table are little, index are little, etc.
> also posibility to split in several server to win in performance. (but i
> thing it's not really possible (i thing) by one trigger to update the
> table in another database)
> Bad: management of creating/destroying table by trigger is not really
> supported under firebird. and what happen when more contact than
> firebird max limit of allowed # of table ?
>
> So what do you advise to do in such case ?

I would strongly argue that option 2 goes against the basic idea of a
relational database, and because of that will probably generate a lot of
trouble along the way.

I have a database that's about 56 Gbyte and has 150 million or more
records (each) in two of the tables.

I find query performance to be very agreable, provided an index can be
used. Unindexed queries are, of course, no fun at all...

So, provided all your queries will be able to use the index on
Contact_ID, I think you will be fine. And if not, then there are
probably better ways to handle the situation than your option 2, e.g.
moving records to an archive table or "throwing hardware at it". Just to
mention, there are RAID controllers that use fast SSD disks for a very
large cache. You can also equip your server with RAM and make FB use a
large page cache and/or put FB's sort space on a ram disk.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64