#1 2019-05-20 14:37:17

xiwuping
Member
Registered: 2018-02-11
Posts: 32

TSQLRestServerFullMemory Batch mode?

Does TSQLRestServerFullMemory support BatchAdd/Update/Delete?

Offline

#2 2019-05-20 22:31:31

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: TSQLRestServerFullMemory Batch mode?

Yes, it does.

Offline

#3 2019-05-21 00:44:43

xiwuping
Member
Registered: 2018-02-11
Posts: 32

Re: TSQLRestServerFullMemory Batch mode?

I tried to compare the performance of mORMot In Memory server versus UniDAC TVirtualTable. 

Here is the code:

**** For UniDAC **********
FUniDacVirtualTable.Clear;
  FUniDacVirtualTable.Open;

  BeginTest(Sender);
 
  FUniDacVirtualTable.DisableControls;
  try
    Field1 := FUniDacVirtualTable.FieldByName('ID');
    Field2 := FUniDacVirtualTable.FieldByName('Status');
    Field3 := FUniDacVirtualTable.FieldByName('Created');
    Field4 := FUniDacVirtualTable.FieldByName('Volume');

    for I := 1 to 100000 do
    begin
        FUniDacVirtualTable.Insert;
        Field1.AsInteger := I;
        Field2.AsString := 'Code' + IntToStr(I);
        Field3.AsDateTime := Date();
        Field4.AsFloat := Random(10000);
        FUniDacVirtualTable.Post;
    end;
  finally
    FUniDacVirtualTable.EnableControls;
    EndTest(Sender);
  end;
end;

******* For mORMot ***********

procedure TForm1.btnmORMotTestClick(Sender: TObject);
var
  I: Integer;
  recData: TmORMotTestRecord;
  IDs: TIDDynArray;
  restClient: TSQLRestClientDB;
  model: TSQLModel;
begin
  model := TSQLModel.Create([TmORMotTestRecord]);
  model.VirtualTableRegister(TmORMotTestRecord, TSQLVirtualTableBinary);
  restClient := TSQLRestClientDB.Create(model, nil, 'mORMotTes.data', TSQLRestServer. False, '');
  restClient.Server.CreateMissingTables;
  recData := TmORMotTestRecord.Create;

  BeginTest(Sender);
  try
    restClient.BatchStart(TmORMotTable);
    for I := 1 to 100000 do
    begin
      with recData do
      begin
        SeqID := I;
        Status := 'Code' + IntToStr(I);
        CreatedDate := Now;
        Volume := Random(10000);
      end;

      restClient.BatchAdd(recData, True);
    end;
    restClient.BatchSend(IDs);
  finally
    EndTest(Sender);
    recData.Free;
  end;
end;

------------------
The result is
UniDac: 208 ms
mORMoT: 265 ms

What am I missing here?  I expect mORMot to be faster!  Why it is slower than UniDAC's virtual table?

Also - how can I Save the data to File?  restClient.Server.StaticVirtualTable[TmORMotTestRecord].UpdateToFile doesn't exist.
       - Again, how can I load the data from a file?  I am asking because I want to bench marking the data loading time versus UniDAC's virtual table.
       -  How to save to a stream? Load from a stream?

Last edited by xiwuping (2019-05-21 00:46:22)

Offline

#4 2019-05-21 04:06:22

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: TSQLRestServerFullMemory Batch mode?

Your mORMot testing code uses the ORM, while the UniDAC testing code access the db directly, it's not a fair comparison. Use ISQLDBStatement (from SynDb.pas) instead.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#5 2019-05-21 05:50:43

cybexr
Member
Registered: 2016-09-14
Posts: 78

Re: TSQLRestServerFullMemory Batch mode?

restClient.Server.DB.Synchronous := smOff;
      restClient.Server.DB.LockingMode := lmExclusive;

guess maybe helpful?

Offline

#6 2019-05-21 08:11:07

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: TSQLRestServerFullMemory Batch mode?

cybexr wrote:

restClient.Server.DB.Synchronous := smOff;
      restClient.Server.DB.LockingMode := lmExclusive;

guess maybe helpful?

I don't think so. The looking mode and synchronous setting are only affect on-disk db file, but not in-memory db.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#7 2019-05-21 14:37:49

xiwuping
Member
Registered: 2018-02-11
Posts: 32

Re: TSQLRestServerFullMemory Batch mode?

I set smOff and lmExclusive,  it made no difference as I saw.

Re: "Your mORMot testing code uses the ORM, while the UniDAC testing code access the db directly, it's not a fair comparison. Use ISQLDBStatement (from SynDb.pas) instead."

- I understand what you meant. But all I want is to test the performance of mORMot's in-memory virtual table,  as compared to UniDAC's virtual table.  To that end,  the ORM element is quite thin here, and I don't think it is an unfair comparison.  I just want to  compare the virtual table performance of both.

I also tested by accessing TSQLRestServerFullMemory directly, still not very happy.  I am not sure if my mORMot code is the right way to use mORMot virtual table.  Any one can give me some headsup/advice?

I guess I was somewhat disappointed since everybody is talking about mORMot and everybody is saying it is highly optimized for performance, and it happens that I need a high-performance in-memory virtual table, hence give mORMot a try.

Last edited by xiwuping (2019-05-21 14:42:40)

Offline

#8 2019-05-21 16:49:29

macfly
Member
From: Brasil
Registered: 2016-08-20
Posts: 374

Re: TSQLRestServerFullMemory Batch mode?

I agree it's not a paired test.

ORM needs to do all the serialization and deserialization when using a TSLRecord.

A Mem Table is not a persistence layer of objects.

You can do this with  TVirtualTable?

FUniDacVirtualTable.Status := 'Code' + IntToStr(I);
FUniDacVirtualTable.Created := Date();
FUniDacVirtualTable.Volume := Random(10000);

From docs:

11.2. In-process/stand-alone application

For a stand-alone application, create a TSQLRestClientDB. This particular class will initialize an internal TSQLRestServerDB instance, and you'll have full access to the SQLite3 database in the same process, with no speed penalty.

Content will still be converted to and from JSON, but there will be no delay due to the transmission of the data.

There is no delay in data transmission, but serialization is done in the same way.

Last edited by macfly (2019-05-21 17:01:52)

Offline

#9 2019-05-22 14:02:14

xiwuping
Member
Registered: 2018-02-11
Posts: 32

Re: TSQLRestServerFullMemory Batch mode?

Update:  previous testing results are 64bit.  I switched to 32 bit, and mORMot virtual table seems to have better performance.

To insert 100000 records

FDMemTable Rio 10.3 UP1   -  187 ms
UniDAC Virtual Table v7.4.12 - 195 ms
mORMot TSQLRestServerFullMemory - 178 ms
DevExpress MemDataTable - 155 ms

So not bad.  I am a bit surprise that DevExpress TMemData is so fast.

Offline

#10 2019-05-22 15:24:05

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: TSQLRestServerFullMemory Batch mode?

I insist that the ORM processing will slow your mORMot testing code down. Publish your testing code into a github repository.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#11 2019-05-26 21:48:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: TSQLRestServerFullMemory Batch mode?

You are comparing oranges and apples, for several reasons.

First, ORM is not direct DB access.
Second, using a mORMot virtual table as external is almost pointless, from performance point of view: use it directly - you add another layer for nothing.
Third, this later layer is a full SQLite3 engine, far more powerful than an in-memory table: you can run full SQL queries on it.
Fourth, no code to review - use a gist and not the forum as stated by https://synopse.info/forum/misc.php?action=rules

Offline

Board footer

Powered by FluxBB