You may create a descendant index on time column to get a faster response on
the 'select
MAX(Time) from XYZ' query.



De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] En nombre de Hans
Enviado el: lunes, 03 de agosto de 2009 04:42 p.m.
Para: firebird-support@yahoogroups.com
Asunto: Re: [firebird-support] Large row count in tables





An insert trigger on the current table updating another table with a single
row of values (integer ID, Integer TIME) eliminates the startup 'select
MAX(Time) from XYZ' query. Just read the single row of the second table.

----- Original Message -----
From: "roar4711" <firebird99@... <mailto:firebird99%40ymail.com> >
To: <firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> >
Sent: Monday, August 03, 2009 2:21 PM
Subject: [firebird-support] Large row count in tables

>I got a database with 5 different table, each containing around 19 million
>records. Each five minutes between 1500 and 2000 records are added to each
>table. Right now the database is 12 GB but I expect it to grow to 80-90 GB
>for a whole year worth of data.
>
> A table looks like this:
>
> integer ID
> integer Time
> float Data
>
> I have an asc and desc index on ID/Time for (time) ranged queries. Cant
> have a primary index since there is no unique data.
>
> Default page size is set to 4096 bytes.
>
> I'm using Firebird 2.1 (Embedded) and the IBPP API.e
>
> Problem is that insert times are starting to take a long time, up to 4
> minutes. Also upon startup a query is done to determine the last added
> record (time wise, select MAX(Time) from XYZ), this also a length process
> and very memory consuming (just a hair short of using all of the 4 GB
> process space).
>
> To try to attack the problem above I'm thinking of splitting the database
> into several smaller databases. Trying to limit the size of each database
> to around 1 GB where non of the above problems is visible.
>
> Before I do that I just wanted to throw out a question here, any good
> advice to give ?, I rather have one database than splitting them, but
> right now I'm out of ideas.
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>





[Non-text portions of this message have been removed]