Hi Adriano,
>> any ideas why inserting 1 million records with a GUID primary key using
>> the built-in GEN_UUID() function takes WAY longer than with an integer
>> primary key. My finding:
>>
>> I ran a VERY simple test case on Firebird 2.5 SuperClassic on a normal
>> desktop, so no fancy IO etc. with a 8K page size database inserting one
>> million records into a two column table. The one database with a table
>> with CHAR(16) OCTSETS using the built-in GEN_UUID() function for
>> generating a GUID and in another database a table with a simple BIGINT
>> and a generator.
>>
>> After inserting: While it's not a big surprise that the GUID databases
>> has more pages allocated (GUID-DB: 12826 vs. INT-DB: 8392), an
>> interesting outcome is that the inserting process (a simple stored
>> procedures) finishes in the INT-DB in ~ 15 sec., whereas in the GUID-DB,
>> it takes ~ 50 minutes!
>>
> Please benchmark what times it takes to just call GEN_UUID 1 million of
> times.
The following is lightning fast (< 1 sec.), so GEN_UUID() isn't the culprit:
set term !!;
execute block
as
declare i integer;
declare guid char(16) character set octets;
begin
i = 1;
while (i <= 1000000) do
begin
guid = gen_uuid();
i = i + 1;
end
end
!!
set term ;!!
commit;
Currently, I re-run the test case mentioned in my initial email. Still
running. I then will also run a test case where inserting GUIDs into a
non-indexed column will happen. Perhaps adjusting the index tree for
GUIDs is slow.
Regards,
Thomas
------------------------------------------------------------------------------
Create and publish websites with WebMatrix
Use the most popular FREE web apps or write code yourself;
WebMatrix provides all the features you need to develop and
publish your website. http://p.sf.net/sfu/ms-webmatrix-sf
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
