Synopse Open Source

With upcoming revision 1.18 of the framework, our SynSQlite3.pas unit is able to access the SQLite3 engine in two ways:

• Either statically linked within the project executable;
• Or from an external sqlite3.dll library file.

The SQLite3 APIs and constants are defined in SynSQlite3.pas, and accessible via a TSQLite3Library class definition. It defines a global sqlite3 variable as such:

var
  sqlite3: TSQLite3Library;

To use the SQLite3 engine, an instance of TSQLite3Library class shall be assigned to this global variable. Then all mORMot's calls will be made through it, calling e.g. sqlite3.open() instead of sqlite3_open().

There are two implementation classes:

Referring to SynSQLite3Static.pas in the uses clause of your project is enough to link the .obj engine into your executable.

Warning - breaking change: before version 1.18 of the framework, link of static .obj was forced - so you must add a reference to SynSQLite3Static in your project uses clause to work as expected.

In order to use an external sqlite3.dll library, you have to set the global sqlite3 variable as such:

 FreeAndNil(sqlite3); // release any previous instance (e.g. static)
 sqlite3 := TSQLite3LibraryDynamic.Create;

Of course, FreeAndNil(sqlite3) is not mandatory, and should be necessary only to avoid any memory leak if another SQLite3 engine instance was allocated (may be the case if SynSQLite3Static is referred somewhere in your project's units).

Here are some benchmarks, compiled with Delphi XE3, run in a 32 bit project, using either the static bcc-compiled engine, or an external sqlite3.dll, compiled via MinGW or Microsoft Visual C++.

Up to now, our SynDB database classes were handling ODBC, OleDB providers and direct Oracle or SQLite3 connection.

We have added a DB.pas based layer, ready to be used with UniDAC, NexusDB, or the BDE.
Any other TDataset based component is ready to be interfaced, including UIB, AnyDAC or DBExpress.

The ZEOS library (in its latest 7.0.3 stable version, which works from Delphi 7 up to XE3) has also been interfaced, but without the TDataset/DB.pas layer: our SynDBZEOS.pas unit calls the ZDBC layer, which is not tied to DB.pas nor its RAD components, and is therefore faster. By the way, it will work also with the Starter edition of Delphi (which does not include the DB components) - just like the other "regular" SynDB classes.

This is a work in progress, any testing and feedback is welcome!
We had to circumvent some particularities of the libraries, but I guess we have something interesting.

A dedicated "SynDBDataset" sub-folder has been created in the repository, to contain all SynDBDataset.pas-based database providers.
SynDBNexusDB.pas unit has been moved within this sub-folder, as SynDBUniDAC.pas + SynDBBDE.pas units have been added.
SynDBZeos.pas has a direct access to the ZDBC layer, so is not part of the "SynDBDataset" sub-folder.

Here is some benchmark, mainly about Oracle and SQlite3 database access.
Of course, our direct SynDBOracle / SynDBSQLite3 layers are the fastest around, and we can see that ZDBC layer is sometimes more efficient than the TDataset components.

Even if mORMot will be more easily used in a project designed from scratch, it fits very well the purpose of evolving any existing Delphi project, or even creating the server side part of an AJAX application. 

One benefit of such a framework is to facilitate the transition from a Client-Server architecture to a N-Tier layered pattern.

With a refreshed hardware, and the latest code modifications of the framework code, I run again the benchmark sample.

The new PC has an Intel Core i7-2600 CPU, running on Windows Seven 64-bit, with anti-virus (Norton) fully enabled.
Oracle 11g database is remotely accessed over a corporate network, so latency and bandwidth is not optimal.
Still no SSD, but a standard 7200 rpm hard drive of 500 GB.

The results are impressive, when compared to the previous run using a Core 2 Duo CPU - mORMot's optimized code achieves amazing speed on this platform.
And I guess the 8MB of L3 cache of the Core i7 does wonders with our code.

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.

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!

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.

The framework documentation was just updated.

The general organization of the SAD document (which is the one to be read in all cases) has been refreshed, and is now separated in smaller chapters.

The new official name has been changed into "Synopse SQLite3/mORMot framework"...