--- In firebird-support@yahoogroups.com, "roar4711" <firebird99@...>
wrote:

>
> Hi,
>
> I am storing simple time series data into Firbird. In worst case I'm
> looking at 20 MB new data each day (~ 1.7 milion rows).
>
> Currently the data is divided into ~100 tables.
>
> As you might have guess by now, I have run into some query
> performance problems. Not in the sens that Firebird is slow, its just
> a allot of data.
>
> To improve the situation I'm considering reorganise the data into
> more tables, to get smaller data sets to search.
>
> So the question is, is there a practical limit to the number of
> tables in a database (In the sens that where table management will
> affect the general performance) ?. I'm considering splitting the data
> into as much as 2500 tables

The direct answer to your question is 64,000 tables, but I suggest
that your approach is using wrong. You are attempting to use a
band-aid solution to a design problem, and at some point in time,
either now or in the future, that will bite you.

The speed for querying a fixed number of records should be close to
independent of table size providing you have appropriate indexing. I
would recommend one of the larger page sizes (ie 16K) to keep the
index pages as densely packed as possible.

Now depending on who you believe, the maximum number of rows in a
Firebird table is 16G (firebirdfaq) or 37GB of compressed data for 1.5
and none for 2+(ibphoenix), so perhaps some clarification from more
knowledgable members as to Firebird's current limitation.

From a purely philosophical standpoint, like data should be stored in
the same table. I suppose you can make exceptions for historic data to
make it easier to purge, but you shouldn't need to split your tables
up for performance reasons.

Adam