TSQLRestServerFullMemory Batch mode?

xiwuping · 2019-05-20 14:37:17

Does TSQLRestServerFullMemory support BatchAdd/Update/Delete?

ab · 2019-05-20 22:31:31

Yes, it does.

xiwuping · 2019-05-21 00:44:43

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)

edwinsn · 2019-05-21 04:06:22

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.

cybexr · 2019-05-21 05:50:43

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

guess maybe helpful?

edwinsn · 2019-05-21 08:11:07

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.

xiwuping · 2019-05-21 14:37:49

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)

macfly · 2019-05-21 16:49:29

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)

xiwuping · 2019-05-22 14:02:14

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.

edwinsn · 2019-05-22 15:24:05

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

ab · 2019-05-26 21:48:41

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

mORMot Open Source

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

TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

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

Re: TSQLRestServerFullMemory Batch mode?

Board footer