Synopse Open Source

Data definition

The ORM record used for this test is defined as such:

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;
    property LastName: RawUTF8 index 40 read fLastName write fLastName;
    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;

So this is something you may have to use in your application, not a blank table with one row.
Note that we have specified index 40 for FirstName / LastName properties, to ensure that it would be sized to 40 widechars (without this index attribute, it would have created a NCLOB, which is much slower than a NVARCHAR2).

Insertion speed

The process consisted in inserting 5000 rows, with or without a BATCH mode, nested with or without a transaction.

The benchmark code is similar to this:

    Props := PropsClass.Create(Server,aDatabaseName,aUserID,aPassWord);
    try
      VirtualTableExternalRegister(Model,TSQLRecordSample,Props,'SampleRecord');
      Client := TSQLRestClientDB.Create(Model,nil,':memory:',TSQLRestServerDB,false,'');
      try
        Client.Server.CreateMissingTables;
        if aUseTransactions then
          Client.TransactionBegin(TSQLRecordSample);
        if not aUseBatch then
          SetLength(Res,Stat.fNumberOfElements);
        Value.BirthDate := EncodeDate(1900,1,1);
        for i := 1 to Stat.fNumberOfElements do begin
          Value.Amount := i*0.01;
          Value.LastName := Int32ToUtf8(i);
          Value.FirstName := 'Nam'#$c3#$a9' '+Value.LastName; // 'Namé' in UTF-8
          if aUseBatch then begin
            if Client.BatchCount=0 then
              Client.BatchStart(TSQLRecordSample);
            Client.BatchAdd(Value,true);
          end else
            Res[i-1] := Client.Add(Value,true);
          Value.BirthDate := Value.BirthDate+1;
        end;
        if aUseBatch then
          Client.BatchSend(Res);
        if aUseTransactions then
          Client.Commit;

For SQlite3 without transactions, it inserted only 30 rows instead of 5000, since SQLite3 is by design very slow outside a transaction, due to its ACID behavior which expects the data to be flushed to the hard-drive after every COMMIT, so after every INSERT if no transaction is defined.

Similarly, the Jet engine was tested only on 500 rows, since it was identified to be slow (no OleDB BULK mode is handled by now in SynOleDB).

Now, some data.

Benchmark was run on a good old Core 2 Duo workstation (no SSD), with anti-virus and background applications, over a 100 Mb corporate network, linked to a shared Oracle 11g database.
So it was a development environment, very similar to production site, not dedicated to give best performance. As a results, rates and timing may vary depending on network and server load, but you get results similar to what could be expected on customer side.

For Oracle database, insertion was speed up from 476 to 53478 rows per second!
More than a 100 x speed factor, with no code change on the client side - good plain BatchAdd() method used, as stated above.

You can note that Jet is slow, when compared to SQlite3 or Oracle, when it deals with insertion. And it consumes much more memory and disk space than SQLite3.
It is also not surprising that SQlite3 in-memory tables - identified as "SQLite3 (memory)" above - are pretty fast, but that due to its asynchronous remote work, Oracle is even faster than this in-memory database! Does make sense for the amount of money to be paid for its license. :)

Read speed

The code for reading is similar to the following (we did not include the internal assertions to enhance readability in this blog article):

          // one by one retrieve values from server
          for i := 0 to Stat.fNumberOfElements-1 do
            VClient.Retrieve(Res[i],Value);
          // retrieve all rows using SQLite3 virtual module (via ORDER clause)
          Value.FillPrepare(Client,'order by RowId');
          while Value.FillOne do begin
          end;
          // retrieve all rows by-passing SQLite3 virtual module (SELECT * from DB)
          Value.FillPrepare(Client);
          while Value.FillOne do begin
          end;

Data is therefore retrieved:

• One row per one row (using a Retrieve method over an ID);
• All rows at once (using a FillPrepare method), through the virtual table engine: the 'order by RowID' clause ensures it will pass via the SQLite3 virtual table module;
• All rows at once (also via FillPrepare), directly accessing to the SynDB classes: in this release, we have added a direct request detection for a SELECT * statement: it is up to 30-50% faster (see "All Virtual" / "All Direct" values below), for instance when you call TSQLRecord.FillPrepare(aClient) with no where clause, to retrieve all rows at once.

Here are some speed results, run on the same hardware, over data just inserted:

If you want to use a map/reduce algorithm in your application, in addition to ORM data access, all those enhancements may speed up a lot your process. Reading and writing huge amount of data has never been so fast and easy: you may even be tempted to replace stored-procedure process by high-level code implemented in your Domain service. N-tier separation would benefit from it.

Note that all those tests were performed locally and in-process, via a TSQLRestClientDB instance. For both insertion and reading, a Client-Server architecture (e.g. using HTTP/1.1 for mORMot clients) will give even better results for BATCH and retrieve all modes.
Remember also that those values refer to virtual-table external databases: for classic TSQLRecord instance without a previous call to VirtualTableExternalRegister (i.e. internal SQlite3 tables), it will be even faster.

Sounds like we have gone far away into optimizations for external tables, here.
IMHO it would be pretty hard to find a faster ORM around.
And if you compare with classic TQuery process, you could be amazed by the speed achieved for free with mORMot, even with a Delphi Starter edition (the framework does not need a Professional nor Architect version)!

Feedback is welcome on our forum.
And since you have the benchmark program at hand, so you can publish and share your own data and graphs if you like to.