>>> 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.
Inserting into a table with a GUID column as primary key takes here ~62 minutes. Inserting into table with an non-indexed GUID column takes ~10 seconds. Shall I open a ticket with a proper test case? 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
