Fabiano,

> I had to insert 300.000 records in a table with no indices or triggers
> except the primary key, which is type "char(16), character set octets".
>
> With the primary key, it took 40 minutes to insert all records.
>
> Removing the primary key, it took 1m40s to insert all records.
>
> Both tests were done using a just restored database, forced-writes off,
> firebird 2.5, and the same machine.
>
> Is this difference expected? Maybe it is related to the type of the primary
> key?
>

If your primary key is a UUID (likely, given the type) and you're using
classic, increase the page cache by a lot. The problem is that UUIDs are
created in random order, so inserts occur at random places in the index. If
the particular index page needed has been dropped from the cache, it has to
be read again. Keys generated by sequences (aka generators) compress very
well as well as avoiding random access to index pages. Of course,
generators are not unique across machines, so if you require a cross-machine
unique value you must either use a compound key <generated value> <machine
id> or set the generators on different machines to different ranges.

Good luck,

Ann

A second problem with using UUIDs as index keys is that they compress much
less well than sequences.


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