Synopse Open Source

Usually, in Delphi application (like in most high-level languages), errors are handled via exceptions. By default, any Exception raised on the server side, within an interface-based service method, will be intercepted, and transmitted as an error to the client side, then a safe but somewhat obfuscated EInterfaceFactoryException will be raised on the client side, containing additional information serialized as JSON.

You may wonder why exceptions are not transmitted and raised directly on the client side, with our mORMot framework interface-based services, as if they were executed locally.

We will now detail some arguments, and patterns to be followed.

For pre-Unicode versions of Delphi, the unique way of having UTF-16 native type is to use the WideString type.
This type, under Windows, matched the BSTR managed type, as used by OLE and COM components.

In Delphi, WideString implementation calls directly the corresponding Windows API, and do not use the main Delphi heap manager.
Even if since Vista, this API did have a huge speed-up, it is still in practice much slower than the regular string type. Problems is not about UTF-16 encoding, but about the memory allocation, which is shared among processes, using the Windows global heap, and is much slower than our beloved FastMM4.
Newer versions of Delphi (since Delphi 2009) feature a refactored string = UnicodeString type, which relies on FastMM4 and not the Windows API, and is much faster than WideString.

Within our mORMot framework, we by-passed this limitation by using our RawUTF8 type, which is UTF-8 encoded, so as Unicode ready as the new UnicodeString type, and pretty fast.
In a recent internal project, we had to use a lot of WideString instances, to support UTF-16 encoding in Delphi 7/2007, involving a lot of text.

It sounded to be very slow, so we had to do something!

This is where our new SynFastWideString unit comes in.

Purpose of this unit is to patch the system.pas unit for older versions of Delphi, so that WideString memory allocation would use FastMM4 instead of the slow BSTR Windows API.
It will speed up the WideString process a lot, especially when a lot of content is allocated, since FastMM4 is much more aggressive than Windows' global heap and the BSTR slow API. It could be more than 50 times faster, especially when releasing the used memory.
The WideString implementation pattern does NOT feature Copy-On-Write, so is still slower than the string UnicodeString type as implemented since Delphi 2009. This is the reason why this unit won't do anything on Unicode versions of the compiler, since the new string type is to be preferred there.

MongoDB (from "humongous") is a cross-platform document-oriented database system, and certainly the best known NoSQL database.
According to http://db-engines.com in April 2014, MongoDB is in 5th place of the most popular types of database management systems, and first place for NoSQL database management systems.
Our mORMot framework gives premium access to this database, featuring full NoSQL and Object-Document Mapping (ODM) abilities to the framework.

Integration is made at two levels:

• Direct low-level access to the MongoDB server, in the SynMongoDB.pas unit;
• Close integration with our ORM (which becomes defacto an ODM), in the mORMotMongoDB.pas unit.

MongoDB eschews the traditional table-based relational database structure in favor of JSON-like documents with dynamic schemas (MongoDB calls the format BSON), which matches perfectly mORMot's RESTful approach.

In this first article, we will detail direct low-level access to the MongoDB server, via the SynMongoDB.pas unit.

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.

You will find in the SQLite3\Sample\16 - Execute SQL via services folder of mORMot source code a Client-Server sample able to access any external database via JSON and HTTP.
It is a good demonstration of how to use an interface-based service between a client and a server.
It will also show how our SynDB classes have a quite abstract design, and are easy to work with, whatever database provider you need to use.

The corresponding service contract has been defined:

  TRemoteSQLEngine = (rseOleDB, rseODBC, rseOracle, rseSQlite3, rseJet, rseMSSQL);

  IRemoteSQL = interface(IInvokable)
    ['{9A60C8ED-CEB2-4E09-87D4-4A16F496E5FE}']
    procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName,
      aUserID, aPassWord: RawUTF8);
    function GetTableNames: TRawUTF8DynArray;
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;

Purpose of this service is:
- To Connect() to external databases, given the parameters of a standard TSQLDBConnectionProperties. Create() constructor;
- Retrieve all table names of this external database as a list;
- Execute any SQL statement, returning the content as JSON array, ready to be consumed by AJAX applications (if aExpanded is true), or a Delphi client (e.g. via a TSQLTableJSON and the mORMotUI unit).

Of course, this service will be define as sicClientDriven mode, that is, the framework will be able to manage a client-driven TSQLDBProperties instance life time.

Benefit of this service is that no database connection is required on the client side: a regular HTTP connection is enough.
No need to install nor configure any database provider, and full SQL access to the remote databases.

Due to our optimized JSON serialization, it will probably be faster to work with such plain HTTP / JSON services, instead of a database connection through a VPN. In fact, database connections are made to work on a local network, and do not like high-latency connections, which are typical on the Internet.

Some major speed improvements have been made to our SynDB* units, and how they are used within the mORMot persistence layer.
It results in an amazing speed increase, in some cases.

Here are some of the optimizations how took place in the source code trunk:

• SQL statement client-side cache in ODBC and OleDB;
• SQL statement server-side cache for Oracle;
• When inserting individual data rows in an external table, the last inserted IDs are maintained in memory instead of executing "select max(id)" -  we added a new property EngineAddUseSelectMaxID to unset this optimization - we noted that this modification circumvented a known limitation of Firebird very efficiently.

Overall, I observed from x2 to x10 performance boost with simple Add() operations, using ODBC, OleDB and direct Oracle access, when compare to previous benchmarks (which were already impressive).
BATCH mode performance is less impacted, since it by-passed some of those limitations, but even in this operation mode, there is some benefits (especially with ODBC and OleDB).

Here are some results, directly generated by the supplied "15 - External DB performance" sample.

We got some interesting feedback in reddit.

I looked at your website and it is a bit confusing to be honest. After browsing for five minutes I still can't figure out what it is that this framework is supposed to do. It seems like a strange mish mash of different unrelated libraries. You've got some client-server stuff. Some SQLite stuff, which doesn't really fit with client server stuff as it is not a server database. Then there is some PDF stuff.
What is the big picture? What does this actually do?

Such confusion does make sense. Our web site is split into forum, blog, source-code repository and tickets, some wiki pages.

Here are some points of orientation.

I like very much user participation (SCRUM / Agile is my moto) - I never believe to be always right nor write perfect code, and I'm convinced Open Source projects are also about sharing ideas among people of good will.

So when an active member of the forum reported his confusion / concern about some of the ORM methods of our framework, it appeared that some re-factoring was necessary.

There was a breaking change about the TSQLRecord.Create / FillPrepare / CreateAndFillPrepare and TSQLRest.OneFieldValue / MultiFieldValues methods: for historical reasons, they expected parameters to be marked as % in the SQL WHERE clause, and inlined via :(...):.
Since revision 1.17 of the framework, those methods expect parameters marked as ? and with no :(...):.

For instance, instead of writing:

 aRec.CreateAndFillPrepare(Client,'Datum=?',[],[DateToSQL(EncodeDate(2012,5,4))]);

 aRec.CreateAndFillPrepare(Client,'Datum=?',[DateToSQL(EncodeDate(2012,5,4))]);

The void [], array (used for replacing % characters) is not to be written any more, since the default is to use bound parameters via ? and not textual replacement via %.

Due to this breaking change, user code review is necessary if you want to upgrade the engine from 1.16 or previous.

In all cases, using ? is less confusing for new users, and more close to the usual way of preparing database queries - e.g. as used in SynDB.pas units.

Both TSQLRestClient.EngineExecuteFmt / ListFmt methods are not affected by this change, since they are just wrappers to the FormatUTF8() function.

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 SynDB;
• '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;
• 'trans' stands for Transaction, i.e. when the write process is nested within BeginTransaction / Commit calls;
• '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);
• ACID is 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.