Fabiano wrote:
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" (UUID).
With the primary key, it took *40 minutes to insert all records.*
Removing the primary key, it took *1m 40s 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?
Is there something i can do to improve the performance?
Ann W. Harrison answers:
If your primary key is a UUID 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.