2022-02-15

mORMot 2 ORM Performance

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. ;)

Continue reading

2015-09-25

ORM TNullable* fields for NULL storage

In Delphi code, NULLable types do not exist as such. There is no native int? type, as in C#.
But at SQL and JSON levels, the NULL value does exist and should be converted as expected by the ORM.

In SQLite3 itself, NULL is handled as stated in http://www.sqlite.org/lang_expr.html (see e.g. IS and IS NOT operators).
It is worth noting that NULL handling is not consistent among all existing database engines, e.g. when you are comparing NULL with non NULL values... so we recommend using it with care in any database statements, or only with proper (unit) testing, when you switch from one database engine to another.

By default, in the mORMot ORM/SQL code, NULL will appear only in case of a BLOB storage with a size of 0 bytes.
Otherwise, you should not see it as a value, in most kinds of ORM properties.

Null-oriented value types have been implemented in our framework, since the object pascal language does not allow defining a nullable type (yet)

We choose to store those values as variant, with a set of TNullable dedicated types, as defined in mORMot.pas:

type
  TNullableInteger = type variant;
  TNullableBoolean = type variant;
  TNullableFloat = type variant;
  TNullableCurrency = type variant;
  TNullableDateTime = type variant;
  TNullableTimeLog = type variant;
  TNullableUTF8Text = type variant;

Continue reading

2015-08-23

"SQL and NoSQL", not "SQL vs NoSQL"

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.

Continue reading

2015-05-18

CQRS Persistence Service of any DDD object with mORMot

We introduced DDD concepts some time ago, in a series of articles in this blog. At that time, we proposed a simple way of using mORMot types to implement DDD in your applications. But all Domain Entitities being tied to the framework TSQLRecord class did appear as a limitation, breaking the  […]

Continue reading

2014-11-28

ODM magic: complex queries over NoSQL / MongoDB

You know that our mORMot is able to access directly any MongoDB database engine, allowing its ORM to become an ODM, and using NoSQL instead of SQL for the query languages.

But at mORMot level, you could share the same code between your RDBMS and NoSQL databases.
The ORM/ODM is able to do all the conversions by itself!
Since we have just improved this feature, it is time to enlighten its current status.

Continue reading

2014-11-18

HTTP remote access for SynDB SQL execution

For mORMot, we developed a fully feature direct access layer to any RDBMS, implemented in the SynDB.pas unit.

You can use those SynDB classes to execute any SQL statement, without any link to the framework ORM.
At reading, the resulting performance is much higher than using the standard TDataSet component, which is in fact a true performance bottleneck.
It has genuine features, like column access via late-binding, an innovative ISQLDBRows interface, and ability to directly access the low-level binary buffers of the database clients.

We just added a nice feature to those classes: the ability to access remotely, via plain HTTP, to any SynDB supported database!

Continue reading

2014-11-14

BREAKING CHANGE - TSQLRecord.ID primary key changed to TID: Int64

Up to now, the TSQLRecord.ID property was defined in mORMot.pas as a plain PtrInt/NativeInt (i.e. Integer under Win32), since it was type-cast as pointer for TSQLRecord published properties.
We introduced a new TID type, so that the ORM primary key would now be defined as Int64.

All the framework ORM process relies on the TSQLRecord class.
This abstract TSQLRecord class features a lot of built-in methods, convenient to do most of the ORM process in a generic way, at record level.

It first defines a primary key field, defined as ID: TID, i.e. as Int64 in mORMot.pas:

type
  TID = type Int64;
  ...
  TSQLRecord = class(TObject)
  ...
    property ID: TID read GetID write fID;
  ...

In fact, our ORM relies now on a Int64 primary key, matching the SQLite3 ID/RowID primary key.
This primary key will be used as RESTful resource identifier, for all CRUD operations.

Continue reading

2014-09-12

Legacy code, mORMot, and database sharing

It is pretty much possible that you would have to maintain and evolve a legacy project, based on an existing database, with a lot of already written SQL statements - see Legacy code and existing projects.

For instance, you would like to use mORMot for new features, and/or add mobile or HTML clients - see Cross-Platform clients.
In this case, the ORM advanced features - like ORM Cache or BATCH process, see BATCH sequences for adding/updating/deleting records - may conflict with the legacy code, for the tables which may have to be shared.
Here are some guidelines when working on such a project.

To be exhaustive about your question, we need to consider each ORM CRUD operation.
We may have to divide them in three kinds: read queries, insertions, and modifications of existing data.

Continue reading

2014-06-27

Tip about Fossil ticket reports

It appears that version 1.25 of Fossil did change the ticket storage behavior:

Enhancements to ticket processing. There are now two tables: TICKET and TICKETCHNG. There is one row in TICKETCHNG for each ticket artifact. Fields from ticket artifacts go into either or both of TICKET and TICKETCHNG, whichever contain matching column names. Default ticket edit and viewing scripts are updated to use TICKETCHNG.

As stated by the official Fossil Change Log.

It appears that it just broke existing reports, so we had troubles with display of the ticket on our site.

Since we managed to find a workable solution, we would like to share it on our blog, to save other users time!

Continue reading

2014-06-22

Audit-trail for ORM change tracking

Since most CRUD operations are centered within the scope of our mORMot server, we implemented in the ORM an integrated mean of tracking changes (aka Audit Trail) of any TSQLRecord.
In short, our ORM is transformed into a time-machine, just like the good old DeLorean!

Keeping a track of the history of business objects is one very common need for software modeling, and a must-have for any accurate data modeling, like Domain-Driven Design.
By default, as expected by the OOP model, any change to an object will forget any previous state of this object. But thanks to mORMot's exclusive change-tracking feature, you can persist the history of your objects.

Enabling audit-trail

By default, change-tracking feature will be disabled, saving performance and disk use.
But you can enable change tracking for any class, by calling the following method, on server side:

 aServer.TrackChanges([TSQLInvoice]);

This single line will let aServer: TSQLRestServer monitor all CRUD operations, and store all changes of the TSQLInvoice table within a TSQLRecordHistory table.

Continue reading

2014-05-07

MongoDB + mORMot benchmark

Here are some benchmark charts about MongoDB integration in mORMot's ORM.

MongoDB appears as a serious competitor to SQL databases, with the potential benefit of horizontal scaling and installation/administration ease - performance is very high, and its document-based storage fits perfectly with mORMot's advanced ORM features like Shared nothing architecture (or sharding).

Continue reading

MongoDB + mORMot ORM = ODM

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 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.

This second article will focus on integration of MongoDB with our ORM.

Continue reading

Direct MongoDB database access

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.

Continue reading

2014-04-18

Introducing mORMot's architecture and design principles

We have just released a set of slides introducing  ORM, SOA, REST, JSON, MVC, MVVM, SOLID, Mocks/Stubs, Domain-Driven Design concepts with Delphi,  and showing some sample code using our Open Source mORMot framework. You can follow the public link on Google Drive! This is a great opportunity to  […]

Continue reading

2014-03-13

ORM mapping class fields to external table columns

When working with an ORM, you have mainly two possibilites:

  1. Start from scratch, i.e. write your classes and let the ORM creates all the database structure - it is also named "code-first";
  2. From an existing database, you define in your model how your classes map the existing database structure - this is "database-first".

We have just finalized ORM external table field mapping in mORMot, using e.g.
aModel.Props[aExternalClass].ExternalDB.MapField(..)
See this last commit.

So you can write e.g.

fProperties := TSQLDBSQLite3ConnectionProperties.Create(
  SQLITE_MEMORY_DATABASE_NAME,'','','');
VirtualTableExternalRegister(fExternalModel,
  TSQLRecordPeopleExt,fProperties,'PeopleExternal');
fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
  MapField('ID','Key').
  MapField('YearOfDeath','YOD');

Then you use your TSQLRecordPeopleExt table as usual from Delphi code, with ID and YearOfDeath fields:

  • The "internal" TSQLRecord class will be stored within the PeopleExternal external table;
  • The "internal" TSQLRecord.ID field will be an external "Key: INTEGER" column;
  • The "internal" TSQLRecord.YearOfDeath field will be an external "YOD: BIGINT" column;
  • Other internal published properties will be mapped by default with the same name to external column.

Continue reading

2014-03-07

Support of MySQL, DB2 and PostgreSQL

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.

Continue reading

2014-03-03

ORM enhanced for BATCH insert

We just committed some nice features to the ORM kernel, and SynDB* classes of our mORMot framework.

During BATCH insertion, the ORM is able to generate some optimized SQL statements, depending on the target database, to send several rows of data at once.
It induces a noticeable speed increase when saving several objects into an external database.

This feature is available for SQlite3 (3.7.11 and later), MySQL, PostgreSQL, MS SQL Server (2008 and up), Oracle, Firebird and NexusDB.
Since it is working at SQL level, it is available for all supported access libraries, e.g. ODBC, OleDB, Zeos/ZDBC, UniDAC, FireDAC.
It means that even properties not implementing array binding (like OleDB, Zeos or UniDAC) are able to have a huge boost at data insertion, ready to compete with the (until now) more optimized libraries.

Continue reading

2014-02-28

Are NoSQL databases ACID?

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).
Whatever document/key/column oriented they are, they all use some kind of document storage.
It may be schema-less, blob-stored, column-driven, but it is always some set of values bound together to be persisted.
This set of values define a particular state of one entity, in a given model.
Which we may call Aggregate.

Continue reading

2013-11-04

Updated mORMot database benchmark - including MS SQL and PostgreSQL

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.

Continue reading

2013-09-10

Thread-safety of mORMot

We tried to make mORMot at the same time fast and safe, and able to scale with the best possible performance on the hardware it runs on.
Multi-threading is the key to better usage of modern multi-core CPUs, and also client responsiveness.

As a result, on the Server side, our framework was designed to be thread-safe.

On typical production use, the mORMot HTTP server will run on its own optimized thread pool, then call the TSQLRestServer.URI method. This method is therefore expected to be thread-safe, e.g. from the TSQLHttpServer. Request method. Thanks to the RESTful approach of our framework, this method is the only one which is expected to be thread-safe, since it is the single entry point of the whole server. This KISS design ensure better test coverage.

Let us see now how this works, and publish some benchmarks to test how efficient it has been implemented.

Continue reading

- page 1 of 4