Library

How to quickly fill table in Firebird with millions of records

If you need to fill the table in Firebird with millions of test records, see example below:

recreate table t( s1 varchar(36) unique, s2 varchar(36) unique, s3 varchar(36) unique); commit;

insert into t(s1,s2,s3)
select
uuid_to_char( gen_uuid( ) ) , uuid_to_char( gen_uuid( ) ), uuid_to_char( gen_uuid( ) )
from rdb$types a , rdb$types b, (select 1 i from rdb$types rows 3) c 
rows 1000000;

How fast it will insert 1 million of records to the database? Let's run it:

SQL> insert into t(s1,s2,s3)
CON> select
CON> uuid_to_char( gen_uuid( ) ) , uuid_to_char( gen_uuid( ) ), uuid_to_char( gen_uuid( ) )
CON> from rdb$types a , rdb$types b, (select 1 i from rdb$types rows 3) c
CON> rows 1000000;
Current memory = 903160672
Delta memory = 418688
Max memory = 903270176
Elapsed time= 5.539 sec
Buffers = 102400
Reads = 3
Writes = 11903
Fetches = 2870578

As you can see, it inserts 1 million of records less than 6 seconds.