mORMot Open Source

maestro3

Member

Registered: 2015-01-24

Posts: 1

Newbie question on mORMot low level SQLITE access and speed

Hello, I am a hobby programmer and I compared the performance of FireDac in DXE7 with mORMot framework with low level access. I did not really use the ORM framework, but looked for a wrapper for SQLite which may also work with FP under Linux. For this test I created a table and later inserted records from an existing Microsoft Access database using ADO components in both cases.

With FireDac the insert process took about 2-86ms (measured the min & max values) per record with the FireDac statement object. Using a Prepare and later bind parameters to it.
In code

  FStmnt.Prepare('INSERT INTO "Attenuation Coefficients" (ID, En, EnComment, MuOverRho, MuEnOverRho)' +
                        ' VALUES (:ID, :En, :EnComment, :MOR, :MEOR)');
    for i := 1 to FStmnt.ParamDefsCount do 
      TSQLiteBind.Create(FStmnt.Params);
   while not(DM.ADOTbl.Eof) do begin
      FStmnt.Params[0].AsInteger:=DM.ADOTbl.FieldByName('ID').AsInteger;
      FStmnt.Params[1].AsFloat:=DM.ADOTbl.FieldByName('En').AsFloat;
      FStmnt.Params[2].AsString:=DM.ADOTbl.FieldByName('EnComment').AsString;
      FStmnt.Params[3].AsFloat:=DM.ADOTbl.FieldByName('MuOverRho').AsFloat;
      FStmnt.Params[4].AsFloat:=DM.ADOTbl.FieldByName('MuEnOverRho').AsFloat;
      FStmnt.Execute;
      FStmnt.Reset;
      DM.ADOTbl.Next;
    end;
 

With mORMot the insert process took between 80-180ms per record so substancially longer than FireDac. In both cases I used statically linked Sqlite3 (probably not the same version).
This is mORMot's code

  SynStmt.Prepare('INSERT INTO "Attenuation Coefficients" (ID, En, EnComment, MuOverRho, MuEnOverRho) ' +
                          'VALUES (:ID, :En, :EnComment, :MOR, :MEOR)');
  while not(DM.ADOTbl.Eof) do begin
    SynStmt.Bind(1, DM.ADOTbl.FieldByName('ID').AsInteger);
    SynStmt.BindVariant(2, DM.ADOTbl.FieldByName('En').AsFloat, False);
    SynStmt.BindTextS(3, DM.ADOTbl.FieldByName('EnComment').AsString);
    SynStmt.BindVariant(4, DM.ADOTbl.FieldByName('MuOverRho').AsFloat, False);
    SynStmt.BindVariant(5, DM.ADOTbl.FieldByName('MuEnOverRho').AsFloat, False);
    SynStmt.ExecutePrepared;
    SynStmt.Reset;
 end;
 

Now the questions are:
  1) Could this be a SQLITE version issue?
  2) Do I use the wrong objects in mORMmot for this simple task?
  3) Or is mORMmot not really designed for this kind of low level access and the loss in speed is due to the ORM framework overhead?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Newbie question on mORMot low level SQLITE access and speed

First of all, you should better nest your writings within a transaction.
SQLite3 is very sensitive about transactions.

Then, in your test you include a reading from ADO, which is a true bottleneck.
Using DM.ADOTbl.FieldByName() in a loop is dead slow.

Then, your code seems to use SynDBSQLite3.pas.
You are NOT using the ORM here!
The ORM is with mORMot.pas + mORMotSQLite3.pas.

Then, why are you using BindVariant(), whereas you have a Bind(Param: Integer; Value: double) method ?

FireDAC change the default SQlite3 writing settings.
It disables write synchronization, and force exclusive access to the file.

If you use the same settings with SynDBSQLite3, you will get the same speed - or I suspect even something better.
See http://synopse.info/files/html/Synopse% … ml#TITL_60

If you use the mORMot ORM, you will get even better speed, since our ORM is able to generate a better SQL than your plain INSERT.
Using a BATCH process like this:

TSQLAttenuationCoefficients = class(TSQLRecord)
...
published
  property En: double;
  property EnComment: string ...
  property MuOverRho: double ...
  property MuEnOverRho: double ...
end;

batch := TSQLBatch.Create(TSQLAttenuationCoefficients);
while not DB.ADOTbl.Eof do begin
  ac.ID := ...
  ac.En := ...
  ...
  batch.Add(ac);
end;
RestServer.BatchSend(batch);

About performance, see http://synopse.info/files/html/Synopse% … ml#TITL_59
If you use our ORM with direct SQLite3, with transaction and lock+exclusive settings, you insert more than 200,000 rows per seconds - see "SQLite3 (file off exc)" row in insertion speed.
Whereas if you use FireDAC as data layer, you get only 110,000 rows per seconds - see "FireDAC SQlite3" row.

FireDAC will be definitively much slower than our SynSQLite3 or SynDBSQLite3 units, or even than our ORM layer, when reading an individual row.
TDataSet has a huge overhead.