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
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).
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
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
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.
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
Retrievemethod over an ID);
- All rows at once (using a
FillPreparemethod), 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
SynDBclasses: 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
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,
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)!