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 the hardware (disk speed, CPU, RAM) 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 graph and the table with test results on the different disks, Firebird versions, and computers:
Test results INSERT/UPDATE/DELETE test
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
Intel Xeon E5-2420 SSD Intel SC2CW12 32Gb 2.5.9 9999 1000000 111166 1170 142943 2010 67845 80
Intel Xeon E5-2420 SSD Intel SC2CW12 32Gb 2.5.9 307200 1000000 108951 10170 138855  19630 55864 23140
Intel Xeon E5-2420 SSD Intel SC2CW12 32Gb 3.0.4 307200 1000000 124753  19938 155376 39848 37362 40742
Intel Xeon E5-2420 SSD Intel SC2CW12 32Gb 4.0B1 307200 1000000 108701 19235 136639 38600 36535 36545
Intel Xeon X5680  HDD SAS 144Gb 2.5.8 384 1000000 153927 3280 196969 1560 130480 2070
Intel Xeon E5-2680 v2 SSD 16Gb 3.0.5 65536 1000000 123494 22068 171777 21797 132542 23912
Intel Xeon E5-2680 v2 SSD 16Gb 3.0.5 98304 1000000 111294 31997 169874 31168 122760 33764
Intel Xeon E5-5620 HDD SAS10k VMware 8Gb 3.0.4 50000 1000000 297672 35396 310690 34649 121729 36125
Intel Xeon E5-2430L HDD VMWare 16Gb 3.0.4 50000 1000000 203574 6150 237487 5881 57394 6769
Intel Core i7-4790K SSD Samsung 850 Pro 16Gb 3.0.4 2048 1000000 55074 53 84465 52 64354 59
Intel Core i5-3210M SSD ADATA SP550 12Gb 3.0.5 2048 1000000 138109 381 324375 366 416114 398
Intel Core i3-7100 SSD SC2KW128G8  4Gb 2.5.8 9999 1000000 68359 3240 97531 3060 103234 1470
Intel Xeon e3 1226v3 SSD Kingston KC400 8Gb 3.0.4 2048 1000000 85101 186 152246 160 175890 196
Intel Core i7-6820HQ SSD Install Pro 5400s Series 16Gb 2.5.8 9999 1000000 95673 1310 119700 1700 48492 1120
Intel Core i5-8250 HDD 8Gb 2.5.7 2048 1000000 152508 450 225328 720 151115 60
KVM Virtual Machine SSD 25Gb 3.0.5 1048576 1000000 116680 77380 120241 153956 28749 154248
Intel Core i5-2310 SSD SanDisk 8Gb 2.5.8 2048 1000000 112516 3880 260949 5880 204656 6220
Intel Core i3-6320 SSD NVMe Samsung 960 Evo 8Gb 2.5.8 75 1000000 77901 390 92322 760 56340 74
Intel Core i7-8700K RAM Disk 64Gb 3.0.4 2048 1000000 49469 36 74376 35 42987 28
Raspberry Pi Sandisk Extreme 32Gb   2.5.8 2048 1000000 865815 3352 2669544 4261 3696870 4676
Intel Xeon SSD 64Gb 3.0.5 500009 1000000 159958 5342 167637 9091 29179 7070
Intel Core i7-3630qm SSD Plextor PX-256M7VC 16Gb 2.5.8 16384 1000000 83873 2010 108913 2410 50779 70
Intel Core i7-3630qm HDD 7200rpm 16Gb 2.5.8 16384 1000000 87646 4040 108655 9990 60564 4080
Intel Core i7-3630qm RAM Disk 16Gb 2.5.8 16384 1000000 83437 260 107262 340 45991 460
Intel Xeon HDD VMWare 32Gb 2.5.2 1024 1000000 117910 2870 181933 7470 121883 8080
 
​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;