INSERT/UPDATE/DELETE test

Below you can find a very simple test which measures the performance of INSERT/UPDATE/DELETE operations in Firebird (3.0 or 2.5). This test is single-threaded, and its result depends mostly on disk speed and Firebird cache size.
To run it, copy paste the text below into the text file and change the path to the database - in the example, the connection string is in Firebird 3 XNET format, but you can change it to TCP (localhost:Disk:\path\database.fdb) or embedded (disk:\path\database.fdb) connection string.
Please use the same firebird.conf as you use normally (if you need to tune your configuration, use one of these optimized configuration files).
This test creates a database, creates a table with several indices, and then performs the 1 million of INSERT, UPDATE, and DELETE operations.
If you have stored sql file into c:\temp\dml-basic-benchmark.sql, please run this script with the following command: 
isql -i c:\temp\dml-basic-benchmark.sql
This script will create a database approximately 3.6Gb in size. Please delete it after the test.

Please send to [email protected] the result of the script execution with details about your disk, CPU, and RAM, and we will add it to the table below.

Here is the table with test results on the different disks, Firebird versions, and computers:
CPU Disk RAM Firebird
Version		 Page
Buffers		 ##
​of rows		 INSERT, ms Commit, ms UPDATE, ms Commit, ms DELETE time. ms Commit,
ms
Intel Core i3-3220 SSD Crucial MX300 12Gb 3.0.5 2048 1000000 92699 1214 136096 2072 93232 11
Intel Xeon E5-2643 SSD Samsung PM863a 64Gb 3.0.4 256 1000000 320461 80 907881 100 1317092 76
AMD Ryzen 2700 SSD  32Gb 3.0.4 1048576 1000000 69383 10191 84234 25364 23201 20695
AMD Ryzen 2700 SSD 32Gb 3.0.4 102400 1000000 70323 8668 86418 13778 26141 9092
AMD Ryzen 2700 HDD 5400 32Gb 3.0.4 1048576 1000000 79515  28402 95141 60402 25472  57165
AMD Ryzen 2700 HDD 5400 32Gb 3.0.4 102400 1000000 84724 23632 108874 34303 64082 32228
AMD Ryzen 2700 SSD 32Gb 2.5.9 1024 1000000 76561 700 114704 1160 75043 1840
AMD Ryzen 2700 SSD 32Gb 2.5.9 2048 1000000 69169 630 101828 1340 65820 2110
AMD Ryzen 2700 SSD 32Gb 2.5.9 9999 1000000 65853 1040 84859 1840 38483 3280
AMD Ryzen 2700 SSD 32Gb 2.5.9 20000 1000000 65533 1200 84710 2090 37958 3680
AMD Ryzen 2700 SSD 32Gb 2.5.9 50000 1000000 65395 2810 84003 3580 34756 5160
AMD Ryzen 2700 SSD 32Gb 2.5.9 100000 1000000 64599 6170 82475 6350 37160 7650
Intel Pentium G850 2.9 GHz HDD 7200  8Gb  2.5.1  2048 1000000 104128  16570 142834  42220 99325  58170
Intel i7-5500U SSD 16Gb 3.0.4 50000 1000000 125055 5216 152995 5178 67123 5813
Intel i7-5500U HDD 16Gb 3.0.4 50000 1000000 186218 14153 231001 15584 80080 15458
Intel i7-5500U SSD 16Gb 3.0.4 75000 1000000 134811 9712 163249 8297 68074 13976
Intel Core i5-2500  SSD INTEL SSDSA2CW120G3 4Gb 2.5.8 9999 1000000 88692  2670 113835  3420  231538  3570
Intel Core i5-2500  SSD INTEL SSDSA2CW120G3 4Gb 2.5.8 384 1000000 124815  1700 192240  2390 295163  2010
Intel Core i3 SSD NVMe Samsung 860 EVO 16Gb 2.5.9 75 1000000 92618 0 112457 0 66142 0
Intel Core i3 SSD NVMe Samsung 860 EVO 16Gb 2.5.9 1024 1000000 78058 10 104130 10 60402 10
Intel Core i3 SSD NVMe Samsung 860 EVO 16Gb 2.5.9 2048 1000000 72798 10 97114 20 54991 10
Intel Core i3 SSD NVMe Samsung 860 EVO 16Gb 2.5.9 9999 1000000 70918 7 87512 8 41651 8
 
​The test script is below: 
create database "xnet://e:\inserttest4.fdb" user "SYSDBA" password "masterkey" page_size 16384;

set term ^;
execute block as
begin
    -- ######################################################
    rdb$set_context('USER_SESSION', 'ROWS_TO_HANDLE', 1000000);
    -- ######################################################
    execute statement 'drop sequence g';
when any do 
    begin 
    end
end
^
set term ;^
commit;

create sequence g;

recreate table test(
     id int
    ,grp smallint
    ,pid int
    ,dts timestamp
    ,code_sml varchar(15)
    ,code_med varchar(150)
    ,code_lrg varchar(1500)
    ,code_unq char(16) character set octets
    ,constraint test_pk primary key(id)
    ,constraint test_unq unique( code_unq )
);

create index test_pid on test(pid);
create descending index test_dts on test(dts);
create index test_dml on test(code_sml);
create index test_med on test(code_med);
create index test_lrg on test(code_lrg);
commit;
----------------------------------------------

set bail on;
set list on;
set stat on;

set term ^;
execute block returns( inserted_rows int, elap_ms int )
as
    declare i int = 0;
    declare t timestamp;
begin
    inserted_rows = rdb$get_context('USER_SESSION', 'ROWS_TO_HANDLE');
    t = 'now';
    while ( i < inserted_rows ) do
    begin
        insert into test(id, grp, pid, dts, code_sml, code_med, code_lrg, code_unq)
        values(
             gen_id(g,1)
            ,rand() * 10
            ,rand() * 1000
            ,dateadd( rand()*1000000 second to timestamp '01.01.2019 00:00:00' )
            ,lpad('', 15, 'QWERTY' )
            ,lpad('', 150, 'QWERTY' )
            ,lpad('', 1500, 'QWERTY' )
            ,gen_uuid()
        );
        i = i + 1;
    end
    elap_ms = datediff(millisecond from t to cast('now' as timestamp));
    suspend;
end
^
commit^
-----------------------------------------------

execute block returns( updated_rows int, elap_ms int )
as
    declare i int = 0;
    declare t timestamp;
begin
    updated_rows = rdb$get_context('USER_SESSION', 'ROWS_TO_HANDLE');
    t = 'now';
    while ( i < updated_rows ) do
    begin
        update test set 
            grp = rand() * 10
           ,pid = rand() * 1000
           ,dts = dateadd( rand()*100000 second to timestamp '01.01.2019 00:00:00'  )
           ,code_sml = lpad('', 15, 'ASDFGH' )
           ,code_med = lpad('', 150, 'ASDFGH' )
           ,code_lrg = lpad('', 1500, 'ASDFGH' )
           ,code_unq = gen_uuid()
        where id = :i+1 ;
        i = i + 1;
    end
    elap_ms = datediff(millisecond from t to cast('now' as timestamp));
    suspend;
end
^

commit^
-----------------------------------------------

execute block returns( deleted_rows int, elap_ms int )
as
    declare i int = 0;
    declare t timestamp;
begin
    deleted_rows = rdb$get_context('USER_SESSION', 'ROWS_TO_HANDLE');
    t = 'now';
    while ( i < deleted_rows ) do
    begin
        delete from test where id = :i+1 ;
        i = i + 1;
    end
    elap_ms = datediff(millisecond from t to cast('now' as timestamp));
    suspend;
end
^

set term ;^
commit;