On 19-Aug-05, at 12:38 AM, Helen Borrie wrote:

> 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.
>

No, Firebird does not reject the statement above. But when an integer
literal that is larger than (2^31)-1 (signed integer) is specified,
then the command is rejected with a syntax or illegal value error (I
do not have the exact value here). Replace the second value in the
statement above with 549,755,813,887 (remove the commas) and see what
happens. I have tried replacing this with 0x7FFFFFFFFF, but that was
also rejected.

>
>
>> 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
>

Yes, I understand all of this. My point was that the size of the
resulting database was much larger than the size of the file created
by other database packages when the same data was loaded. I used the
raw data size as a base measurement. Since the only information being
stored for this table is the raw data and the primary key index, it
seems odd that the overall storage should be as high as 4 times the
raw data size when other databases have a lower factor. I am going to
be repeating this test with some of the other databases to see if I
can get a more accurate set of space metrics for them, but I recall
that at least one other resulted in a factor of less than 2x, which
make some sense, given that the primary key is a 64-bit integer.

-ken