Since revision 1.18 of the framework, a new SynDBDataset.pas
unit has been introduced, able to interface any DB.pas
based
library to our SynDB
classes, using TDataset
to
retrieve the results.
Due to the TDataset
design, performance is somewhat degraded in
respect to direct SynDB
connection (e.g. results for
SQLite3 or Oracle), but it also opens the potential database
access.
Some dedicated providers have been published in the
SynDBDataset
sub-folder of the mORMot source code
repository.
Up to now, FireDAC (formerly AnyDAC), UniDAC and
BDE libraries are interfaced, and a direct connection to the
NexusDB engine is available.
Since there are a lot of potential combinations here, feedback is welcome. Due to our Agile process, we will first stick to the providers we need and use. It is up to mORMot users to ask for additional features, and provide wrappers, if possible, or at least testing abilities. Of course, DBExpress would benefit to be integrated, even if Embarcadero just acquired AnyDAC and revamped/renamed it as FireDAC - to make it the new official platform.
Data access benchmark
On an recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed:
- You can persist up to 570,000 objects per second, or retrieve 870,000 objects per second (for our pure Delphi in-memory engine);
- When data is retrieved from server or client internal cache, you can read more than 900,000 objects per second, whatever the database back-end is;
- With a high-performance database like Oracle and our direct access classes, you can write 62,000 (via array binding) and read 92,000 objects per second, over a 100 MB network;
- When using alternate database access libraries (e.g. Zeos, or
DB.pas
based classes), speed is lower, but still enough for most work.
Those numbers include JSON serialization and object properties access via
RTTI.
Difficult to find a faster ORM, I suspect.
The following tables try to sum up all available possibilities, and give some benchmark (average objects/second for writing or read).
In these tables:
- 'SQLite3 (file full/off/exc)' indicates use of the internal
SQLite3 engine, with or without
Synchronous := smOff
and/orDB.LockingMode := lmExclusive
- see 60; - 'SQLite3 (mem)' stands for the internal SQLite3 engine running in memory;
- 'SQLite3 (ext ...)' is about access to a SQLite3 engine as external database, either as file or memory;
- '
TObjectList
' indicates aTSQLRestServerStaticInMemory
instance - either static (with no SQL support) or virtual (i.e. SQL featured via SQLite3 virtual table mechanism) which may persist the data on disk as JSON or compressed binary; - 'Oracle' shows the results of our direct OCI access layer
(
SynDBOracle.pas
); - 'Jet' stands for a MSAccess database engine, accessed via OleDB;
- 'NexusDB' is the free embedded edition, available from official site;
- 'ZEOS *' indicates that the database was accessed directly via the ZDBC layer;
- 'FireDAC *' stands for FireDAC library;
- 'UniDAC *' stands for UniDAC library;
- 'BDE *' when using a BDE connection;
- 'ODBC *' for a direct access to ODBC.
This list of database provider is to be extended in the future. Any feedback is welcome!
Numbers are expressed in rows/second (or objects/second).
Note that these tests are not about the relative speed of each database
engine, but reflect the current status of the integration of several DB
libraries within the mORMot database access.
Purpose here is not to say that one library is better or faster than another,
but publish a snapshot of mORMot persistence layer abilities.
In this timing, we do not benchmark only the "pure" SQL/DB layer access
(SynDB
units), but the whole Client-Server ORM of our framework:
process below includes read and write RTTI access of a TSQLRecord
,
JSON marshaling, CRUD/REST routing, virtual cross-database layer, SQL
on-the-fly translation. We just bypass the communication layer, since
TSQLRestClient
and TSQLRestServer
are run in-process,
in the same thread - as a TSQLRestServerDB
instance. So you have
here some raw performance testimony of our framework's ORM and RESTful
core.
You can compile the "15 - External DB performance
" supplied
sample code, and run the very same benchmark on your own configuration.
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 be described;
- '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.
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) | 503 | 399 | 96391 | 123064 |
SQLite3 (file off) | 923 | 930 | 99534 | 130907 |
SQLite3 (file off exc) | 31829 | 35798 | 101874 | 132752 |
SQLite3 (mem) | 85803 | 109641 | 103976 | 135332 |
TObjectList (static) | 321089 | 548365 | 312031 | 547105 |
TObjectList (virtual) | 314366 | 513136 | 316676 | 571232 |
SQLite3 (ext full) | 451 | 511 | 12092 | 137249 |
SQLite3 (ext off) | 971 | 909 | 108133 | 144475 |
SQLite3 (ext off exc) | 42805 | 51256 | 113155 | 150829 |
SQLite3 (ext mem) | 97344 | 121400 | 113229 | 153256 |
ZEOS SQlite3 | 487 | 455 | 16826 | 19680 |
FireDAC SQlite3 | 25182 | 49795 | 41962 | 114241 |
UniDAC SQlite3 | 473 | 412 | 27370 | 37962 |
ZEOS Firebird | 1835 | 2142 | 18734 | 22540 |
UniDAC Firebird | 7065 | 7637 | 9157 | 10399 |
Jet | 4197 | 4318 | 4789 | 4947 |
Oracle | 511 | 59455 | 948 | 59762 |
ODBC Oracle | 550 | 536 | 1024 | 1043 |
ZEOS Oracle | 343 | 362 | 1086 | 1087 |
FireDAC Oracle | 512 | 32328 | 980 | 34668 |
UniDAC Oracle | 465 | 496 | 915 | 879 |
BDE Oracle | 418 | 410 | 661 | 755 |
NexusDB | 6278 | 6749 | 7901 | 8801 |
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) | 26936 | 514456 | 531858 |
SQLite3 (file off) | 27116 | 538735 | 428302 |
SQLite3 (file off exc) | 122417 | 541125 | 541653 |
SQLite3 (mem) | 119314 | 539781 | 545494 |
TObjectList (static) | 303398 | 529661 | 799232 |
TObjectList (virtual) | 308109 | 403323 | 871080 |
SQLite3 (ext full) | 137525 | 264690 | 546806 |
SQLite3 (ext off) | 134807 | 262123 | 531011 |
SQLite3 (ext off exc) | 133936 | 261574 | 536941 |
SQLite3 (ext mem) | 136915 | 258732 | 544069 |
ZEOS SQlite3 | 3232 | 83243 | 95934 |
FireDAC SQlite3 | 7639 | 80261 | 108117 |
UniDAC SQlite3 | 1586 | 73142 | 96989 |
ZEOS Firebird | 3882 | 69974 | 85416 |
UniDAC Firebird | 2177 | 71858 | 89856 |
Jet | 2619 | 144801 | 222736 |
Oracle | 593 | 74312 | 66131 |
ODBC Oracle | 1134 | 33267 | 33049 |
ZEOS Oracle | 863 | 44207 | 53868 |
FireDAC Oracle | 896 | 33171 | 37912 |
UniDAC Oracle | 500 | 21918 | 23688 |
BDE Oracle | 689 | 3343 | 3426 |
NexusDB | 1419 | 121294 | 195687 |
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. Even
FireDAC, which is known to be very optimized for speed, is limited by
the TDataSet
structure, especially during read speed.
Our direct classes, or even ZEOS/ZDBC performs better.
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.
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!
In the above list, the MS SQL Server is not integrated, but may be
used instead of Oracle (minus the fact that BULK insert is not
implemented yet for it, whereas array binding boosts Oracle writing
BATCH process performance by 100 times). Any other OleDB or ODBC providers may
also be used, with direct access. Or any DB.pas
provider (e.g.
DBExpress / BDE), but with the additional layer introduced by
using a TDataSet
instance.
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 |
int. SQLite3 mem | :memory: |
Fast data handling with no persistence (e.g. for testing) |
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; 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.
Feedback is welcome on our forum, as usual!