Synopse Open Source

The official release of mORMot 2 is around the edge. It may be the occasion to show some data persistence performance numbers, in respect to mORMot 1.

For the version 2 of our framework, its ORM feature has been enhanced and tuned in several aspects: REST routing optimization, ORM/JSON serialization, and in-memory and SQL engines tuning. Numbers are talking. You could compare with any other solution, and compile and run the tests by yourself for both framework, and see how it goes on your own computer or server.
In a nutshell, we almost reach 1 million inserts per second on SQLite3, and are above the million inserts in our in-memory engine. Reading speed is 1.2 million and 1.7 million respectively. From the object to the storage, and back. And forcing AES-CTR encryption on disk almost don't change anything. Now we are talking.

About latest SQlite3 3.32.xxx there is a big problem with codecs.

Critical changes to the public SQLite code were introduced on Feb 7, 2020: “Simplify the code by removing the unsupported and undocumented SQLITE_HAS_CODEC compile-time option”. With the release of SQLite version 3.32.0 on May 22, 2020 these changes finally took officially effect, although they weren't officially announced.

As a sad and unexpected consequence, we are NOT ANY MORE able to compile the new SQlite3 amalgamation with our encryption patch.

A long-awaited feature was the ability to create stand-alone mORMot Win64 applications via Delphi, with no external sqlite3-64.dll required.

It is now available, with proper integration, and encryption is working!

In the last weeks/months, we worked a lot with FPC.
Delphi is still our main IDE, due to its better debugging experience under Windows, but we target to have premium support of FPC, on all platforms, especially Linux.

The new Delphi Linux compiler is out of scope, since it is heavily priced, its performance is not so good, and ARC broke memory management so would need a deep review/rewrite of our source code, which we can't afford - since we have FPC which is, from our opinion,  a much better compiler for Linux.
Of course, you can create clients for Delphi Linux and FMX, as usual, using the cross-platform client parts of mORMot. But for server side, this compiler is not supported, and will probably never be.

You know certainly that our mORMot Open Source framework is an ORM, i.e. mapping objects to a relational / SQL database (Object Relational Mapping).
You may have followed also that it is able to connect to a NoSQL database, like MongoDB, and that the objects are then mapped via an ODM (Object Document Mapping) - the original SQL SELECT are even translated on the fly to MongoDB queries.

But thanks to mORMot, it is not "SQL vs NoSQL" - but "SQL and NoSQL".
You are not required to make an exclusive choice.
You can share best of both worlds, depending on your application needs.

In fact, the framework is able to add NoSQL features to a regular relational / SQL database, by storing JSON documents in TEXT columns.

In your end-user code, you just define a variant field in the ORM, and store a TDocVariant document within.
We also added some dedicated functions at SQL level, so that SQLite3 could be used as embedded fast engine, and provide advanced WHERE clauses on this JSON content.

In a previous article, we presented how Master/Slave replication may be easily implemented in mORMot's RESTful ORM.
Do not forget to visit the corresponding paragraphs of our online documentation, which has been updated, and is more accurate!

Sometimes, the on-demand synchronization is not enough.
So we have just introduced real-time replication via WebSockets.
For instance, you may need to:

• Synchronize a short list of always evolving items which should be reflected as soon as possible;
• Involve some kind of ACID-like behavior (e.g. handle money!) in your replicated data;
• Replicate not from a GUI application, but from a service, so use of a TTimer is not an option;
• Combine REST requests (for ORM or services) and master/slave ORM replication on the same wire, e.g. in a multi-threaded application.

In this case, the framework is able to use WebSockets and asynchronous callbacks to let the master/slave replication - see Asynchronous callbacks - take place without the need to ask explicitly for pending data.
You would need to use TSQLRestServer.RecordVersionSynchronizeMasterStart, TSQLRestServer.RecordVersionSynchronizeSlaveStart and TSQLRestServer.RecordVersionSynchronizeSlaveStop methods over the proper kind of bidirectional connection.

You can use the FreePascal Compiler (FPC) to compile the mORMot framework source code, targetting Windows and Linux.

Linux is a premium target for cheap and efficient server Hosting. Since mORMot has no dependency, installing a new mORMot server is as easy as copying its executable on a blank Linux host, then run it. No need to install any framework nor runtime. You could even use diverse operating systems (several Linux or Windows Server versions) in your mORMot servers farm, with minimal system requirements, and updates.

We will now see how to write your software with Linux-compiling in mind, and also give some notes about how to install a Linux Virtual Machine with Lazarus on your Windows computer, compiling both FPC and Lazarus from their SVN latest sources!

The primary purpose of any software Backup is to recover data after its loss, be it by data deletion or corruption.
Data loss can be a common experience of computer users. A 2008 survey found that 66% of respondents had lost files on their home PC, as Wikipedia quotes.

As a consequence, for any professional use of data, like in our mORMot server, a backup policy is mandatory.

We just introduced officially the SQLite3 Backup API to our low-level SynSQLite3.pas unit, and wrote dedicated methods to make background backup of a running mORMot server easy and safe, without any noticeable performance penalty.

We just tested, benchmarked and validated Oracle MySQL, IBM DB2 and PostgreSQL support for our SynDB database classes and the mORMot's ORM core.
This article will also show all updated results, including our newly introduced multi-value INSERT statement generations, which speed up a lot BATCH insertion.

Stay tuned!

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of mORMot persistence layer abilities, depending on each access library.

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 all aspects of our ORM:

• Access via high level CRUD methods (Add/Update/Delete/Retrieve, either per-object or in BATCH mode);
• Read and write access of TSQLRecord instances, via optimized RTTI;
• JSON marshaling of all values (ready to be transmitted over a network);
• REST routing, with security, logging and statistic;
• Virtual cross-database layer using its SQLite3 kernel;
• SQL on-the-fly generation and translation (in virtual mode);
• Access to the database engines via several libraries or providers.

In those tests, we just bypassed 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, and may expect good scaling abilities when running on high-end hardware, over a network.

On a recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed, as will show the following benchmark:

• 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 38, 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 70,000 (via array binding) and read 160,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 (even if comparable for DB2, MS SQL, PostgreSQL, MySQL) but still enough for most work, due to some optimizations in the mORMot code (e.g. caching of prepared statements, SQL multi-values insertion, direct export to/from JSON, SQlite3 virtual mode design, avoid most temporary memory allocation...).

Difficult to find a faster ORM, I suspect.

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 more than 900,000 objects per second (for our pure Delphi in-memory engine);
• When data is retrieved from server or client 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 65,000 (via array binding) and read 160,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.

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/or DB.LockingMode := lmExclusive;
• '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 a TSQLRestServerStaticInMemory 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);
• '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;
• 'Jet' stands for a MSAccess database engine, accessed via OleDB;
• 'MSSQL local' for a local connection to a MS SQL Express 2008 R2 running instance (this was the version installed with Visual Studio 2010), accessed via OleDB.

This list of database providers is to be extended in the future. Any feedback is welcome!

Numbers are expressed in rows/second (or objects/second). This benchmark was compiled with Delphi 7, so newer compilers may give even better results, with in-lining and advanced optimizations.

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 or database is better or faster than another, but publish a snapshot of current 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.