Insertion speed
Here we insert 5,000 rows of data, with diverse scenarios:
- 'Direct' stands for a individual
Client.Add()
insertion; - 'Batch' mode, has already been described in this blog;
- 'Trans' indicates that all insertion is nested within a transaction - which makes a great difference, e.g. with a SQlite3 database.
Benchmark was run on a Core i7 notebook, with standard SSD, including anti-virus and background applications, over a 100 Mb corporate network, linked to a shared Oracle 11g database. A local instance of MSSQLExpress 2008 R2 was running locally. So it was a development environment, very similar to low-cost production site, not dedicated to give best performance. During the process, CPU was noticeable used only for SQLite3 in-memory and TObjectList - most of the time, the bottleneck is not the CPU, but the storage or network. As a result, rates and timing may vary depending on network and server load, but you get results similar to what could be expected on customer side, with an average hardware configuration.
Direct | Batch | Trans | Batch Trans | |
SQLite3 (file full) | 483 | 473 | 78206 | 121592 |
SQLite3 (file off) | 879 | 843 | 100851 | 111167 |
SQLite3 (file off exc) | 31703 | 35022 | 100276 | 133109 |
SQLite3 (mem) | 86961 | 114678 | 104177 | 138354 |
TObjectList (static) | 336700 | 562239 | 330425 | 547405 |
TObjectList (virtual) | 324296 | 565995 | 319795 | 554754 |
SQLite3 (ext full) | 503 | 467 | 104574 | 136892 |
SQLite3 (ext off) | 890 | 335 | 96420 | 135744 |
SQLite3 (ext off exc) | 44634 | 50908 | 114064 | 154221 |
SQLite3 (ext mem) | 101578 | 137540 | 116563 | 155322 |
FireDAC SQlite3 | 26660 | 53033 | 53320 | 151754 |
UniDAC SQlite3 | 345 | 365 | 27390 | 37913 |
ZEOS SQlite3 | 499 | 493 | 56790 | 66956 |
ZEOS Firebird | 6535 | 13545 | 26531 | 30136 |
FireDAC Firebird | 25177 | 51917 | 24522 | 51880 |
UniDAC Firebird | 6081 | 7061 | 6338 | 7096 |
Jet | 4224 | 4390 | 4844 | 4984 |
NexusDB | 5998 | 6549 | 7668 | 8491 |
Oracle | 566 | 65183 | 1223 | 64413 |
ODBC Oracle | 589 | 482 | 1085 | 1328 |
BDE Oracle | 489 | 511 | 1024 | 1003 |
ZEOS Oracle | 599 | 657 | 1330 | 1464 |
FireDAC Oracle | 570 | 45405 | 1287 | 48686 |
UniDAC Oracle | 391 | 533 | 1135 | 1159 |
MSSQL local | 4619 | 5472 | 13373 | 14364 |
ODBC MSSQL | 5067 | 5200 | 11752 | 12258 |
FireDAC MSSQL | 4979 | 7521 | 11925 | 51100 |
UniDAC MSSQL | 4407 | 4467 | 8867 | 9936 |
ZEOS PostgreSQL | 2982 | 2994 | 6223 | 6028 |
FireDAC PostgreSQL | 2796 | 27638 | 1466 | 27171 |
Due to its ACID implementation, SQLite3 process on file waits for the hard-disk to have finished flushing its data, therefore it is the reason why it is slower than other engines at individual row insertion (less than 10 objects per second with a mechanical hardrive instead of a SDD) outside the scope of a transaction.
So if you want to reach the best writing performance in your application
with the default engine, you should better use transactions and regroup all
writing into services or a BATCH process. Another possibility could be to
execute DB.Synchronous := smOff
and/or DB.LockingMode :=
lmExclusive
at SQLite3 engine level before process: in case of
power loss at wrong time it may corrupt the database file, but it will increase
the rate by a factor of 50 (with hard drive), as stated by the "off"
and "off exc" rows of the table. Note that by default, the
FireDAC library set both options, so results above are to be compared
with "SQLite3 off exc" rows.
For both our direct Oracle access SynDBOracle.pas
library and
FireDAC, Batch process benefit of the array binding feature a lot
(known as Array DML in FireDAC/AnyDAC).
Reading speed
Now the same data is retrieved via the ORM layer:
- 'By one' states that one object is read per call (ORM generates a
SELECT * FROM table WHERE ID=?
forClient.Retrieve()
method); - 'All *' is when all 5000 objects are read in a single call (i.e. running
SELECT * FROM table
from aFillPrepare()
method call), either forced to use the virtual table layer, or with direct static call.
Here are some reading speed values, in objects/second:
By one | All Virtual | All Direct | |
SQLite3 (file full) | 27403 | 551571 | 559346 |
SQLite3 (file off) | 26840 | 555062 | 557537 |
SQLite3 (file off exc) | 122537 | 555185 | 541418 |
SQLite3 (mem) | 124750 | 551328 | 564206 |
TObjectList (static) | 299868 | 945537 | 935803 |
TObjectList (virtual) | 302114 | 433463 | 891583 |
SQLite3 (ext full) | 131960 | 262660 | 541887 |
SQLite3 (ext off) | 132268 | 264200 | 548666 |
SQLite3 (ext off exc) | 132886 | 265561 | 504693 |
SQLite3 (ext mem) | 133911 | 255036 | 534302 |
FireDAC SQlite3 | 7749 | 79058 | 110338 |
UniDAC SQlite3 | 2581 | 74839 | 98460 |
ZEOS SQlite3 | 42336 | 208489 | 312617 |
ZEOS Firebird | 20626 | 95496 | 129954 |
FireDAC Firebird | 2370 | 48066 | 57449 |
UniDAC Firebird | 2229 | 69845 | 90064 |
Jet | 2643 | 159576 | 258678 |
NexusDB | 1413 | 120845 | 208246 |
Oracle | 1464 | 123031 | 159459 |
ODBC Oracle | 1269 | 42528 | 49029 |
BDE Oracle | 860 | 3870 | 4036 |
ZEOS Oracle | 913 | 33167 | 34806 |
FireDAC Oracle | 1195 | 44039 | 53335 |
UniDAC Oracle | 657 | 26212 | 25770 |
MSSQL local | 10398 | 218818 | 425785 |
ODBC MSSQL | 13062 | 137241 | 256581 |
FireDAC MSSQL | 4119 | 76747 | 102984 |
UniDAC MSSQL | 2770 | 91431 | 135394 |
ZEOS PostgreSQL | 8005 | 6896 | 159933 |
FireDAC PostgreSQL | 2222 | 61156 | 81284 |
The SQLite3 layer gives amazing reading results, which makes it a
perfect fit for most typical ORM use. When running with DB.LockingMode :=
lmExclusive
defined (i.e. "off exc" rows), reading speed is very high,
and benefits from exclusive access to the database file. External database
access is only required when data is expected to be shared with other
processes.
In the above table, it appears that all libraries based on
DB.pas
are slower than the others for reading speed. In fact,
TDataSet
sounds to be a real bottleneck, due to its internal data
marshalling. Even FireDAC, which is known to be very optimized for
speed, is limited by the TDataSet
structure. Our direct classes,
or even ZEOS/ZDBC performs better, since they are able to output JSON content
with no additional marshalling.
For both writing and reading, TObjectList
/
TSQLRestServerStaticInMemory
engine gives impressive results, but
has the weakness of being in-memory, so it is not ACID by design, and the data
has to fit in memory. Note that indexes are available for IDs and stored
AS_UNIQUE
properties.
As a consequence, search of non-unique values may be slow: the engine has to
loop through all rows of data. But for unique values (defined as stored
AS_UNIQUE
), both insertion and search speed is awesome, due to its
optimized O(1) hash algorithm - see the following benchmark, especially the
"By name" row for "TObjectList" columns, which correspond to
a search of an unique RawUTF8
property value via this hashing
method.
SQLite3 (file full) | SQLite3 (file off) | SQLite3 (mem) | TObjectList (static) | TObjectList (virt.) | SQLite3 (ext file full) | SQLite3 (ext file off) | SQLite3 (ext mem) | Oracle | Jet | |
By one | 10461 | 10549 | 44737 | 103577 | 103553 | 43367 | 44099 | 45220 | 901 | 1074 |
By name | 9694 | 9651 | 32350 | 70534 | 60153 | 22785 | 22240 | 23055 | 889 | 1071 |
All Virt. | 167095 | 162956 | 168651 | 253292 | 118203 | 97083 | 90592 | 94688 | 56639 | 52764 |
All Direct | 167123 | 144250 | 168577 | 254284 | 256383 | 170794 | 165601 | 168856 | 88342 | 75999 |
Above table results were run on a Core 2 duo laptop, so numbers are lower than with the previous tables.
Analysis and use case proposal
When declared as virtual table (via a VirtualTableRegister
call), you have the full power of SQL (including JOINs) at hand, with
incredibly fast CRUD operations: 100,000 requests per second for objects read
and write, including serialization and Client-Server communication!
Some providers are first-class citizens to mORMot, like
SQLite3, Oracle, or MS SQL. You can connect to them
without the bottleneck of the DB.pas
unit, nor any restriction of
your Delphi license (a Starter edition is enough). For instance,
SQLite3 could be used as main database engine for a client-server
application with heavy concurrent access - if you have doubts about its scaling
abilities, see this blog
article. Direct access to Oracle is also available, with
impressive results in BATCH mode (aka array binding). MS SQL Server,
directly accessed via OleDB (or ODBC) gives pretty good
timing, and a MS SQL Server 2008 R2 Express instance is a convincing
option, for a very offerdable price (i.e. for free) - the LocalDB (MSI
installer) edition is enough to start with. Any other OleDB, ODBC or ZDBC
providers may also be used, with direct access. For instance, Firebird
embedded gives pretty consistent timing, when accessed via Zeos/ZDBC.
But mORMot is very open-minded: you can use any DB.pas
provider, e.g. FireDAC, UniDAC, DBExpress,
NexusDB or even the BDE, but with the additional layer
introduced by using a TDataSet
instance, at reading.
Note that all those tests were performed locally and in-process, via a
TSQLRestClientDB
instance. For both insertion and reading, a
Client-Server architecture (e.g. using HTTP/1.1 for mORMot clients)
will give even better results for BATCH and retrieve all modes.
During the tests, internal caching was disabled, so you may expect speed enhancements for real applications, when data is more read than written: for instance, when an object is retrieved from the cache, you achieve more than 700,000 read requests per second, whatever database is used.
Therefore, the typical use may be the following:
Database | Created by | Use |
int. SQLite3 file | default | General safe data handling, with amazing speed in "off exc" mode |
int. SQLite3 mem | :memory: |
Fast data handling with no persistence (e.g. for testing or temporary storage) |
TObjectList static |
StaticDataCreate |
Best possible performance for small amount of data, without ACID nor SQL |
TObjectList virtual |
VirtualTableRegister |
Best possible performance for small amount of data, if ACID is not required nor complex SQL |
ext. SQLite3 file | VirtualTableExternalRegister |
External back-end, e.g. for disk spanning |
ext. SQLite3 mem | VirtualTableExternalRegister |
Fast external back-end (e.g. for testing) |
ext. Oracle / MS SQL / Firebird | VirtualTableExternalRegister |
Fast, secure and industry standard; data can be shared outside mORMot |
ext. NexusDB | VirtualTableExternalRegister |
The free embedded version let the whole engine be included within your executable, and insertion speed is higher than SQLite3, so it may be a good alternative if your project mostly insert individual objects - using a batch within a transaction let SQlite3 be the faster engine |
ext. Jet | VirtualTableExternalRegister |
Could be used as a data exchange format (e.g. with Office applications) |
ext. Zeos/FireDAC/UniDAC | VirtualTableExternalRegister |
Allow access to several external engines, with some advantages for Zeos,
since direct ZDBC access will by-pass the DB.pas unit and its
TDataSet bottleneck - and we will also prefer an active Open
Source project! |
Whatever database back-end is used, don't forget that mORMot design
will allow you to switch from one library to another, just by changing a
TSQLDBConnectionProperties
class type. And note that you can
mix external engines, on purpose: you are not tied to one single
engine, but the database access can be tuned for each ORM table, according to
your project needs.