Synopse mORMot benchmark
After having tested and enhanced the external database speed (including BATCH mode), we are now able to benchmark all database engines available in mORMot.
In fact, the ORM part of our framework has several potential database
backends, in addition to the default SQLite3 file-based engine.
Each engine may have its own purpose, according to the application expectation.
The following tables try to sum up all available possibilities, and give some benchmark (average rows/seconds for writing or read).
In these tables:
- 'internal' means use of the internal SQLite3 engine;
- 'external' stands for an external access via
TObjectList' indicates a
TSQLRestServerStaticInMemoryinstance 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;
- 'trans' stands for Transaction, i.e. when the write process is nested
BeginTransaction / Commitcalls;
- 'batch' mode will be described in this article;
- 'read one' states that one object is read per call (ORM generates a
SELECT * FROM table WHERE ID=?);
- 'read all' is when all 5000 objects are read in a single call (i.e. running
SELECT * FROM table);
ACIDis an acronym for "Atomicity Consistency Isolation Durability" properties, which guarantee that database transactions are processed reliably: for instance, in case of a power loss or hardware failure, the data will be saved on disk in a consistent way, with no potential loss of data.
With a high-performance database like Oracle and our direct access classes, you write 53,000 and read 72,000 objects per second.
Difficult to find a faster ORM, I suspect.
|Database||ACID||Persist||Write one||Write trans||Write batch||Read one||Read all|
|internal SQLite3 file||Yes||Yes||8||15k||16k||10k||170k|
|internal SQLite3 mem||No||No||30k||35k||45k||44k||170k|
|external SQLite3 file||Yes||Yes||8||13k||15k||45k||160k|
|external SQLite3 mem||No||No||26k||32k||40k||47k||160k|
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 so slow (less than 10 objects per second) 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 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 up to 400 objects per
Therefore, the typical use may be the following:
|int. SQLite3 file||default||General safe data handling|
|int. SQLite3 mem||
||Fast data handling with no persistence (e.g. for testing)|
||Best possible performance for small amount of data, without ACID nor SQL|
||Best possible performance for small amount of data, if ACID is not required nor complex SQL (since no index is available but for IDs, search may be slow)|
|ext. SQLite3 file||
||External back-end, e.g. for disk spanning|
|ext. SQLite3 mem||
||Fast external back-end (e.g. for testing)|
||Fast, secure and industry standard; can be shared outside mORMot|
||Could be used as a data exchange format (e.g. with Office applications)|
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).
Benchmark was run on a good old Core 2 Duo workstation (no SSD), with
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 production site, not dedicated to give best performance.
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.
Results were created with the same software sample as used for Oracle Array
So you can compile the provided
15 - External DB performance
sample code, and check by your own the framework speed.
For both writing and reading,
/ TSQLRestServerStaticInMemory engine gives impressive
results, but have the weakness of being in-memory: you can write its content on
disk either as JSON or as compressed binary, but every transaction is not ACID
in case of power loss. With the latest version of the framework,
TSQLRestServerStaticInMemory does also feature very efficient hashed indexes for
very fast lookup of values. 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
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 450,000 read requests per second, whatever database is used.
Since all the results above are on an average desktop computer, you may find out incredible speed achieved with a Server hardware, e.g. using SSD or a NAS storage. Just imagine how fast your server side may be, when using mORMot and Delphi instead of other technologies! And more clients on the same hardware often allow increasing ROI.
Feedback is welcome on our forum, as usual.