Synopse Open Source

For several units of our framework, we allow late-binding of data values, using a variant and direct named access to properties:
- In SynCommons, we defined our TDocVariant custom variant type, able to store any JSON/BSON document-based content;
- In SynBigTable, we use the TSynTableVariantType custom variant type, as defined in SynCommons;
- In SynDB, we defined a TSQLDBRowVariantType, ready to access any column of a RDBMS data result set row;
- In mORMot, we allow access to TSQLTableRowVariantType column values.

It's a very convenient way of accessing result rows values. Code is still very readable, and safe at the same time.

For instance, we can write:

var V: variant;
 ...
  TDocVariant.New(V); // or slightly slower V := TDocVariant.New;
  V.name := 'John';
  V.year := 1972;
  // now V contains {"name":"john","year":1982}

This is just another implementation of KISS design in our framework.

Since Delphi XE2, some modifications were introduced to the official DispInvoke() RTL implementation:

1. A new varUStrArg kind of parameter has been defined, which will allow to transmit UnicodeString property values;
2. All text property values would be transmitted as BSTR / WideString / varOleStr variants to the invoked variant type;
3. All textual property names were normalized to be in UPPERCASE.

Those modifications are worth considering...
And we may have discovered two regressions: one about speed, and the other about an unexpected logic bug...

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.

One of the main features you may miss when discovering NoSQL ("Not-Only SQL"?) databases, coming from a RDBMS background, is ACID.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction. (Wikipedia)

But are there any ACID NoSQL database?

Please ensure you read the Martin Fowler introduction about NoSQL databases.
And the corresponding video.

First of all, we can distinguish two types of NoSQL databases:

1. Aggregate-oriented databases;
2. Graph-oriented databases (e.g. Neo4J).

By design, most Graph-oriented databases are ACID!
This is a first good point.

Then, what about the other type?
In Aggregate-oriented databases, we can identify three sub-types:

• Document-based NoSQL databases (e.g. MongoDB, CouchDB);
• Key/Value NoSQL databases (e.g. Redis);
• Column family NoSQL databases (e.g. Cassandra).