At 08:29 PM 18/08/2005 -0400, you wrote:

>I have been experimenting with using Firebird from C#/.Net, and I
>have a number of questions/concerns.
>
>First, how does one specify large integer values for insertion into a
>BIGINT column via static SQL. The following statement works when the
>value of the BIGINT is with the signed 32-bit range, but fails when
>the value gets larger:
>
> INSERT INTO MyTable(PKey, BigIntValue) VALUES(32, 12345);

Firebird won't reject this. Something is happening in the interface. The
right place to ask about this is the firebird-net-provider forum. Make
sure you explain what "fails" means.

>Second, I have a potential application that will require an extremely
>large number of records in a table, and I have been performing the
>initial tests on 10,000,000 records in a table that contains only
>BIGINT, INT and TIMESTAMP columns. The 'raw data' size for each
>record is 48 bytes, and the only 'index' on the table was the primary
>key, but storing 5,000,000 records added more than 801MB to the size
>of the database file! The raw size for this many records should only
>have been about 200MB, so this is a 4x size factor! I have conducted
>a similar test with another database, and the resulting file seemed
>to be much smaller (but I need to complete my tests before proceeding
>with specific statements).

Yup. But it's worth knowing that Firebird doesn't lay down "records" in
physical files, so estimating something like "raw size" won't be predictive
of anything much, early in the life of the table. Fb requests disk space
from the OS in blocks, known as "pages". Inside the database, pages are
allocated for storing specific kinds of data - actual record data, indexes,
pages of pointers, blobs, and so forth. Index pages and pointer pages will
be pretty significant for a table of 5 million rows, quite apart from your
raw estimate of 200 Mb for data.

Each time the engine needs more space, it requests another page. The size
of the page is determined by the size defined when the database was created
(or last recreated by a restore). The default page size in Fb 1.5, if you
don't specify it, is 4Kb.

In the multi-generational architecture of Firebird, updating and deleting
rows from tables leaves behind old record versions that will be
garbage-collected eventually. Until the GC occurs, that space is not
vacated and the need for new page allocations remains high. Once the GC
has happened, the space is recycled and will be re-used in further insert
and update operations on that table. Firebird never gives space back to
the OS. To have that happen, you need to back up the database and recreate
it, using gbak.

Eventually, in a well-kept system, the growth of the database file settles
down, to be more in keeping with your "raw estimates" relative to the content.

./heLen