mORMot Open Source

I rerun my test with the latest commit - quite contrary, it seems external SQLite3 layer is still much faster than direct SQLite3 layer for writes.  I am using Delphi 10.4.2, win64.

I noticed that in your sample code, https://github.com/synopse/mORMot2/blob … tCases.pas, TRestClientDB class is used, like below:

 
 TRestClientDB.Client.BatchStart
....
 TRestClientDB.Client.BatchAdd 
...
 TRestClientDB.Client.BatchSend

But in my testing code,  I use TRestServerDB class directly, together with TSQLRestBatch class:

 LBatch := TSQLRestBatch.Create
 ...
 LBatch.Add
 ...
 TRestServerDB.BatchSend(LBatch...

My question is - is the latest performance improvements only for TRestClientDB?  It seems TRestServerDB.BatchSend performance is not improved with the latest commits.

HI @ab, @ttomas

Thank you for the advice and feedback.

I built with the latest commit - still it seems SQLite External File provides the best BatchAdd performance (~350k records/second).

There got to be some (nice) reason for such a stunning performance for an SQL DB. Regardless,  my goal is to find out which settings provides the best WRITE performance- given the testing results,  the answer appears to be SQLite External File .

Note - SQLite External File also gives very good READ performance (slightly worse than SQLite in Memory). So overall,  SQLite External File appears to be the best option?

Any comments? Insights or advice?

I continued with my performance testing - this time, I compared mORMot2  SQLite in memory vs mORMot2 External SQLite File DB.  I am very surprised that the External SQLite File DB is much faster than the SQLite in MEM DB - 25% faster!   

Note:
- For External SQLite File DB, I used TSQLDBSQLite3ConnectionProperties, and SynchronousMode := Off, and LockingMode := Exclusive
- Delphi 10.4.2,  Win64 Build

Details below - but does that make sense?  I expect the In-MEM DB faster than External File DB.

- Single Table,  Multi-Threaded, BatchAdd via a background worker thread,  the SQLite In MEM DB Results:

20210928 18564227  " debug Test_BatchAdd_STable_MThread_MemDB::thr_1 - Orm.Server.BatchSend 6000 records takes 22.54ms (266098 rec/sec), code 200
20210928 18564229  " debug Test_BatchAdd_STable_MThread_MemDB::thr_2 - Orm.Server.BatchSend 6000 records takes 23.09ms (259830 rec/sec), code 200
20210928 18564230  " debug Test_BatchAdd_STable_MThread_MemDB::thr_3 - Orm.Server.BatchSend 6000 records takes 23.42ms (256191 rec/sec), code 200

- Single Table,  Multi-Threaded, BatchAdd via a background worker thread, External SQLite File DB,  SynchronousMode := Off, LockingModel := Exclusive

20210928 18564257  " debug Test_BatchAdd_STable_MThread_SQLite::thr_1 - Orm.Server.BatchSend 6000 records takes 17.34ms (345861 rec/sec), code 200
20210928 18564258  " debug Test_BatchAdd_STable_MThread_SQLite::thr_3 - Orm.Server.BatchSend 6000 records takes 18.15ms (330578 rec/sec), code 200
20210928 18564259  " debug Test_BatchAdd_STable_MThread_SQLite::thr_2 - Orm.Server.BatchSend 6000 records takes 17.71ms (338676 rec/sec), code 200

Thank you for the advice.  I'll keep this in mind. But for now - I'll stick to ZEOS, which seems has faster performance for my use case.

Note - all the insertion cases are targeting ONE single table.  For each test case, the table will be emptied in advance.

----------------------------
1. What if you insert 150,000 records from three concurrent threads?
It doesn't change the overall average insert rate

2. What if you insert 50,000 records from one thread, but 3 times in a row?
Similar (slightly slower 1~2%) insertion rate,  compared to one-time 150,000 records.

3. What if you use our direct PostgreSQL layer instead of Zeos?
mORMot PostgreSQL layer IMPROVES the multi-threaded (3 threads) insertion rate by x6 times,  increasing from Zeo's 5000 records/sec to a much higher 30000 records/sec
But,  PostgreSQL layer DROPS the single-threaded insertion rate from Zeo's 100,000 record/sec to about 50,000 records/sec

4. What if you use SQLite3 instead of PostgreSQL?
If I use mORMOt internal in-mem SQLite3,  the multi-threaded insertions is about 4~6% FASTER then the single-thread insertion,  around 220,000 records/sec
If I use mORMot static object list,  the multi-threaded insertions is about 4~6% FASTER then the singled-thread insertion, around 500,000 records/sec
---------------------------

IN SUMMARY:
-  mORMot in-mem SQLite, and static object list:   multi-threaded batch insertion rate is FASTER than single-threaded insertion
-  for PostgreSQL,  Zeos has the best single-threaded batch insertion rate (100,000 records/sec),  mORMot Postgresql Layer has the best multi-threaded batch insertion rate (50,000 records/sec).

Are the above making sense?

Thank you ab for the heads up.

I use Delphi 10.4.2, and FastMM4-AVX memory manager (https://github.com/maximmasiutin/FastMM4-AVX), Release build.  But my previous test mistakenly enabled FullDebugMode for the RELEASE build.

Now that with your reminder,  I disabled FullDebugMode.  And re-run the test (using the Static Object List DB)

x86
Single Thread                  : 440842 records/sec inserts
Multi Thread (2 threads)    : 474982 records/sec inserts

x64:
Single Thread                  : 471051 records/sec inserts
Multi Thread (2 threads)    : 486679 records/sec inserts

So, in my case,  using Delphi default MM,
Single Thread,   x64 is 7.0% FASTER than 32bit
Multi   Thread,   x64 is 2.5% FASTER than 32 bit.

So you are right, x64 is indeed somewhat faster than x86.

Question - do you have any recommendation for memory manager to use?  Thank you again.

I changed back to FastMM4-AVX , and things seem to be working normal again.

Thank you very much.

Is it allowed to change SQL Model (e.g., adding more tables), and call VirtualTableExternalRegister again,  after TRestServerDB instance has been created?

For example:

FSQLModel := TSQLModel.Create([FTable1, FTable2]);
FServer := TRestServerDB.Create(FSQLModel, SQLITE_MEMORY_DATABASE_NAME, False, '');

Then, I need to add more tables FTable3, FTables4 to FSQLModel, LATER after FServer is created.  Can I used the following code?

FSQLModel.AddTable(FTable3);
FSQLModel.AddTable(FTable4);

Thank you very much @ab. 

It works!

The whole story is here  https://en.delphipraxis.net/topic/5092- … violation/

Basically,  I had a multi-threaded application (mORMot2), which also uses TSynLog for logging purpose.  I have TSynLogPerThreadMode = ptIdentifiedInOnFile

Then,  the application after started, would run normal for a couple of hours, then it crashed.  By "a couple of hours" -  it is really random, sometimes it is 2~3 hours, sometimes more.

The crash is only logged by Windows Event Viewer, as Access Violation, with the fault offset.   TSynLog itself didn't log anything.  madExcept (if enabled) didn't log anything either.  FastMM5 (if enabled) didn't log anything, either.

From the fault offset, I can trace into somewhere in EIOError code.

If I turn off TSynLog completely,  then the access violation would be gone, and never happened.  Therefore, I suspect it is something related to TSynLog under multi-threaded environment. 

Right now, I had to turn of TSynLog and the application seems running all right without any problem.

Yes. I meant TDateTimeMS.

The problem of ObjectLoadJSON ended up in this part of code: mormot.core.rtti, LINE 3305-3309

function TRttiProp.SetValue(Instance: TObject; const Value: variant): boolean;
var
  k: TRttiKind;
  v64: Int64;
  f64: double;
  u: RawUtf8;
begin
  result := false; // invalid or unsupported type
  k := TypeInfo.Kind;
  if k in rkOrdinalTypes then
    if VariantToInt64(Value, v64) then
      SetInt64Value(Instance, v64)
    else
      exit
  else if k in rkStringTypes then
    if VarIsEmptyOrNull(Value) then // otherwise would set 'null' text
      SetAsString(Instance, '')
    else if VariantToUtf8(Value, u) then
      SetAsString(Instance, u)
    else
      exit
  else if k = rkFloat then
    if VariantToDouble(Value, f64) then   // <<--- the value is a DateTime string "2021-05-20T21:21:20.819", but the code is trying to convert it to a Double.
      SetFloatProp(Instance, f64)
    else
      exit
  else if k = rkVariant then
    SetVariantProp(Instance, Value)
  else
    exit;
  result := true;
end;

I am trying to look for EventArchiveSynLZ  in mORMot2, but it seems missing.  Is this purposeful?

I am suffering a similar/related issue (mORMot2).

To use ObjectTOJSON, I have to explicitly specify ObjectToJSON(myObject, [woStoreStoredFalse]), otherwise, the object won't be correctly serialized (i.e., those AS_UNIQUE fields will be missing).

I use Postgresql connection class.  It seems that mormot.db.sql.zeos, the following  method doesn't work properly?

 procedure TSqlDBZeosConnectionProperties.GetFields(const aTableName: RawUtf8;
  out Fields: TSqlDBColumnDefineDynArray);

Line 696

res := meta.GetColumns('', sSchema,
      meta.AddEscapeCharToWildcards(sTableName), '');

always returns EMPTY, thus the returned

out Fields: TSqlDBColumnDefineDynArray 

is always empty.  On the other hand,  however, from the backend PostgreSQL server log, I can see that the server is queried with correctly generated SQL, which indeed returned the list of fields.

Because returned Fields is always empty, CreateMissingTables will always try to recreate existing tables because of the empty Fields returned.

Is this a bug of mORMot2, or I am doing something wrong somewhere?