isql -i c:\temp\dml-basic-benchmark.sqlThis script will create a database approximately 3.6Gb in size. Please delete it after the 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 |
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;