Synopse Open Source

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!

Our latest mORMot feature is interface-based service implementation.

How does it compare with the reference of SOA implementation (at least in the Windows world) - aka WCF?

"Comparaison n'est pas raison", as we use to say in France.
But we will also speak about Event Sourcing, and why it is now on our official road map.
Comparing our implementation with WCF is the opportunity to make our framework always better.

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.

The currency type is the standard Delphi type to be used when storing and handling monetary values. It will avoid any rounding problems, with 4 decimals precision. It is able to safely store numbers in the range -922337203685477.5808 .. 922337203685477.5807. Should be enough for your pocket change.

As stated by the official Delphi documentation:

Currency is a fixed-point data type that minimizes rounding errors in monetary calculations. On the Win32 platform, it is stored as a scaled 64-bit integer with the four least significant digits implicitly representing decimal places. When mixed with other real types in assignments and expressions, Currency values are automatically divided or multiplied by 10000.

In fact, this type matches the corresponding OLE and .Net implementation of currency, and the one used by most database providers (when it comes to money, a dedicated type is worth the cost in a "rich man's world"). It is still implemented the same in the Win64 platform (since XE 2). The Int64 binary representation of the currency type (i.e. value*10000 as accessible via PInt64(aCurrencyValue)^) is a safe and fast implementation pattern.

In our framework, we tried to avoid any unnecessary conversion to float values when dealing with currency values. Some dedicated functions have been implemented for fast and secure access to currency published properties via RTTI, especially when converting values to or from JSON text. Using the Int64 binary representation can be not only faster, but also safer: you will avoid any rounding problem which may be introduced by the conversion to a float type. Rounding issues are a nightmare to track - it sounds safe to have a framework handling natively a currency type from the ground up.

We already shipped a sophisticated set of logging classes some month ago.

Since then, its features have been enhanced, and the system has been deeply interfaced with our main ORM framework. Now almost all low-level or high-level operations can be logged on request.

But since the log files tend to be huge (for instance, if you set the logging for our unitary tests, the 6,000,000 unitary tests creates a 280 MB log file), a log viewer was definitively in need.

Our ORM RESTful Framework is about to access any available database engine.

It will probably change its name (since it won't use only SQlite3 as database), to become mORMot - could be an acronym for "Manage Object Relational Mapping Over Tables", or whatever you may think of...

We'll still rely on SQLite3 on the server, but a dedicated mechanism will allow to access via OleDB any remote database, and mix those tables content with the native ORM tables of the framework. A flexible Virtual Tables and column mapping will allow any possible architecture: either a new project in pure ORM, either a project relying on an existing database with its own table layout.