How make benchmark?

[Andrzej 0]

I am plan make speed test of ZeosLib and FireDac. Is there any sources of standard benchmark with creation of about 50 MB database, with tests of several types of queries?

[Stano 143]

If you own a D professional and are considering a DB server, I wouldn't even try FireDAC

[Roger Cigol 103]

I think doing a benchmark test(s) and then taking an informed decision is always a wise approach. Letting other forum users see your results is always helpful.....

[miab 25]

There is such a test in mORMot 1.18:
mormot\SQLite3\Samples\15 - External DB performance\PerfTest.dpr

Here, for example, my results(ZEOS 8 svn7805):

Running tests using Synopse mORMot framework 1.18.6382, compiled with Delphi 11 Alexandria 32 bit, against SQLite 3.38.2, at 2022-04-28 19:45:52.

Insertion speed
     Direct    Batch    Trans    Batch Trans
SQLite3 (file full)    78    4947    47798    97382
SQLite3 (file off)    895    46449    56970    150902
SQLite3 (file off exc)    16290    152360    57546    180786
SQLite3 (mem)    46426    188964    58552    189897
ZEOS SQlite3    80    2271    48028    98382
ZEOS SQlite3 (ext full)    83    2309    46029    103982
ZEOS SQlite3 (ext off)    907    23439    55455    155424
ZEOS SQlite3 (ext off exc)    21101    135648    54687    161046
ZEOS SQlite3 (mem)    31066    156030    55969    163446
FireDAC SQlite3    11458    11450    35217    119967
UniDAC SQlite3    9192    21152    16356    41129
ODBC MSSQL2012    1551    14856    2457    18412
UniDAC MSSQL2012    1758    3637    2380    3724
ZEOS ODBC_W MSSQL2012    1937    6672    2622    40239
ZEOS ODBC_A MSSQL2012    1806    6215    2867    39693
ZEOS OleDB MSSQL2012    1971    6465    2607    51485
ZEOS Firebird    6119    50985    23009    50362
FireDAC Firebird    2796    51112    18134    51333
Oracle    956    55314    2433    82876
ZEOS Oracle    1540    60240    2852    70724
UniDAC Oracle    1084    2462    2026    2573
ZEOS PostgreSQL    1376    61229    3542    64189
FireDAC PostgreSQL    1234    18920    3415    20241
UniDAC PostgreSQL    1057    13510    1846    14945
ZEOS MySQL    2541    39168    2668    42597
FireDAC MySQL    1321    19768    1302    18890
UniDAC MySQL    2267    19860    2563    20004

Read speed
     By one    All Virtual    All Direct
SQLite3 (file full)    13286    400256    405383
SQLite3 (file off)    13083    381039    379737
SQLite3 (file off exc)    61208    383965    385297
SQLite3 (mem)    58449    389833    390289
ZEOS SQlite3    12813    218455    378845
ZEOS SQlite3 (ext full)    12501    216562    382614
ZEOS SQlite3 (ext off)    12124    206338    369248
ZEOS SQlite3 (ext off exc)    52040    211729    367484
ZEOS SQlite3 (mem)    51253    208707    370096
FireDAC SQlite3    5933    92874    126179
UniDAC SQlite3    6238    96185    136436
ODBC MSSQL2012    2497    113260    194348
UniDAC MSSQL2012    1872    133081    218722
ZEOS ODBC_W MSSQL2012    1344    159555    245712
ZEOS ODBC_A MSSQL2012    1303    141671    218856
ZEOS OleDB MSSQL2012    2497    186101    259040
ZEOS Firebird    23491    88052    126103
FireDAC Firebird    1860    62346    76218
Oracle    3111    56657    173798
ZEOS Oracle    3450    111358    156956
UniDAC Oracle    1096    51343    57296
ZEOS PostgreSQL    2801    141920    198412
FireDAC PostgreSQL    744    44931    47096
UniDAC PostgreSQL    401    90255    128710
ZEOS MySQL    2571    151860    237034
FireDAC MySQL    1063    69310    77087
UniDAC MySQL    1841    110270    158002

 

Michał

Edited May 11, 2022 by miab
ADD

[Arnaud Bouchez 407]

It will depend on the Database used behind FireDAC or Zeos, and the standard used (ODBC/OleDB/Direct...).

I would say that both are tuned - just ensure you got the latest version of Zeos, which is much more maintained and refined that FireDAC in the last years.

 

Note that FireDAC has some aggressive settings, e.g. for SQLite3 it changes the default safe write settings into faster access.

The main interrest of Zeos is that the ZDBC low-level layer does not use a TDataSet, so it is (much) faster if you retrieve a single object.

You will see those two behavior in the Michal numbers above, for instance.

 

Also note that mORMot has a direct DB layer, not based on TDataSet, which may be used with FireDAC or Zeos, or with its own direct ODBC/OleDB/Oracle/PostgreSQL/SQLite3 data access.

See https://synopse.info/files/html/Synopse mORMot Framework SAD 1.18.html#TITL_27

Note that its ORM is built on top on this unique DB layer, and add some unique features like multi-insert SQL generation, so a mORMot TRestBatch is usually much faster than direct naive INSERTs within a transaction. You can reach 1 million inserts per second with SQLite3 with mORMot 2 - https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Performance