Synopse Open Source

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.

For real applications, retrieving objects per ID is not enough.
Your project may have the need to retrieve objects by a textual field, e.g. a name or identifier.
In this case, you can specify a published property of the TSQLRecord as stored false, and it will be defined as an unique column in the underlying database.

For instance, in the latest version of our performance benchmark sample code, you can define the UNIK conditional to define both LastName and FirstName properties as unique:

type
  TSQLRecordSample = class(TSQLRecord)
  private
    fFirstName: RawUTF8;
    fLastName: RawUTF8;
    fAmount: currency;
    fBirthDate: TDateTime;
    fLastChange: TModTime;
    fCreatedAt: TCreateTime;
  published
    property FirstName: RawUTF8 index 40 read fFirstName write fFirstName
      {$ifdef UNIK}stored false{$endif};
    property LastName: RawUTF8 index 40 read fLastName write fLastName
      {$ifdef UNIK}stored false{$endif};
    property Amount: currency read fAmount write fAmount;
    property BirthDate: TDateTime read fBirthDate write fBirthDate;
    property LastChange: TModTime read fLastChange;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
  end;

During insertion or update of records, the database will have to check for uniqueness of those column values. It will have an additional performance cost, since a search of the new value is to be performed among existing values.
In order to speed-up the process, a so-called index is created at the database level.
As a consequence, further lookup using this property will benefit for this index, and will be much faster than a classic loop throughout all data.

In the mORMot core, we just made some modifications related to this feature:

• External tables are now able to create properly UNIQUE fields at database level, as expected;
• A hashed-based index feature has been added to the fast TSQLRestServerStaticInMemory internal engine, and insertion will have no speed penalty any more.

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.

A common issue with Client-Server databases is the latency introduced for each query.

For example, suppose you have a requirement to first collect some information from your application’s users and then insert that information into a table in Oracle Database.

The first obvious option is to insert these multiple rows into the table through a loop in your program. This loop iterates over the data to be inserted and does what is known as a single-row insert , because the application sends one single row of data to the database at a time. Due to the network latency (typically around 1 ms over a corporate network), it would achieve not more than 500-600 requests per second to let the work done, since for each INSERT, a so-called round-trip occurs: a message is sent to Oracle, then a response is sent back to the client.

You have another option for inserting multiple rows of data into the table— that reduces the number of round-trips and improves application performance, database performance, and network resource use. Rather than having the application send a single row of data to the database at a time, it can use array binding to send the data in batches of rows. Therefore, you reduce a lot the number of round-trips to be processed, and enhance performance by a factor of about 100.

Our SynDB unit has been enhanced to introduce new TSQLDBStatement.BindArray() methods, introducing array binding for faster database batch modifications (only implemented in SynDBOracle by now - but MS SQL has a similar feature called OleDB bulk insert).
It is available from the ORM side or mORMot, when working with external tables, in BATCH mode.

Thanks to this enhancement, inserting records within Oracle comes from 400-500 rows per second to more than 50000 rows per second!
It was also a good opportunity to speed up the BATCH process globally, and to benchmark our Oracle back-end against existing external databases, i.e. SQLite3 (as file or in-memory), and Jet / MS Access / .mdb engine.

Note that this article scope is only about virtual tables linked to external databases (i.e. TSQLRecordExternal). Plain TSQLRecord classes will access directly to the SQLite3 engine or in-memory TList, so speed will be even higher than the below values.

Featuring benchmark source code and nice performance charts.

In our documentation, and in all our code source, we avoid using the VCL DB.pas related units, and all the associated RAD components.

This is by design, since our experiment encouraged us to "think ORM, forget anything about RAD (and even SQL in most cases)" in mORMot.
And it introduced some nice border-side effect to Delphi users, e.g. that even a "Delphi Starter Edition" is able to use mORMot, have access to SQLite3, MS SQL or Oracle or any other DB, add interface-based RESTful JSON services over it, just for free...

But in the real world, you may need to upgrade some existing application, get rid of the BDE, or add a SOA layer over an existing business intelligence.
And mORMot is able to serve you well in those scenarios.
That's why we just added a first attempt to expose SynDB results and mORMOt TSQLTableJSON content into a TDataSet.

Our Open Source mORMot framework is now available in revision 1.16.

The main new features are the following:

• Interface-based services, i.e. comparable to WCF, but with Delphi strengths;
• ORM cache which purpose is to enhance server scaling and client responsiveness;
• Automatic JOIN query to unleash the underneath DB power;
• SQLite3 engine updated to latest revision 3.7.12.1;
• Major update of the associated documentation (now more than 800 pages);
• A lot of bug fixes and enhancements, mainly from users requests - thanks you all for your feedback, patches and ideas!

In a discussion with Henrick Hellström, in Embarcadero forums, I wrote some high-level information about mORMot.

It was clear to me that our little mORMot is now far away from a simple Client-Server solution.

The Henrick point was that with Real Thin Client (RTC), you are able to write any Client-Server solution, even a RESTful / JSON based one.

He is of course right, but it made clear to me all the work done in mORMot since its beginning.
From a Client-Server ORM, it is now a complete SOA framework, ready to serve Domain-Driven-Design solutions.

Here is the definition of "cache", as stated by Wikipedia:

In computer engineering, a cache is a component that transparently stores data so that future requests for that data can be served faster. The data that is stored within a cache might be values that have been computed earlier or duplicates of original values that are stored elsewhere. If requested data is contained in the cache (cache hit), this request can be served by simply reading the cache, which is comparatively faster. Otherwise (cache miss), the data has to be recomputed or fetched from its original storage location, which is comparatively slower. Hence, the greater the number of requests that can be served from the cache, the faster the overall system performance becomes.

To be cost efficient and to enable an efficient use of data, caches are relatively small. Nevertheless, caches have proven themselves in many areas of computing because access patterns in typical computer applications have locality of reference. References exhibit temporal locality if data is requested again that has been recently requested already. References exhibit spatial locality if data is requested that is physically stored close to data that has been requested already.

In our ORM framework, since performance was one goal since the beginning, cache has been implemented at four levels:

• Statement cache for reuse of SQL prepared statements, and bound parameters on the fly - note that this cache is available not only for the SQlite3 database engine, but also for any external engine; 
• Global JSON result cache at the database level, which is flushed globally on any INSERT / UPDATE; 
• Tuned record cache at the CRUD/RESTful level for specified tables or records on the server side; 
• Tuned record cache at the CRUD/RESTful level for specified tables or records on the client side.

In mORMot, all the methods available to handle many-to-many relationship (ManySelect, DestGetJoined...) are used to retrieve the relations between tables from the pivot table point of view. This saves bandwidth, and can be used in most simple cases, but it is not the only way to perform requests on many-to-many relationships. And you may have several TSQLRecordMany instances in the same main record - in this case, those methods won't help you.

It is very common, in the SQL world, to create a JOINed request at the main "Source" table level, and combine records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two or more tables by using values common to each. Writing such JOINed statements is not so easy by hand, especially because you'll have to work with several tables, and have to specify the exact fields to be retrieved; if you have several pivot tables, it may start to be a nightmare.

Let's see how our ORM will handle it.