#101 mORMot 1 » All SQLite3 library API calls » 2021-02-25 09:44:23

okoba
Replies: 20

I needed to get sqlite3_normalized_sql and sqlite3_expanded_sql of a statement, but I could not find it in the TSqlite3Library.
It seems TSqlite3Library has not all the SQLite3 API, although it has a comment stating that "wrapper around all SQLite3 library API calls".
ab, do you like to have more of the API in the wrapper or just the ones used by mORMot? If all, I will be happy to give it a try or at least add more.

#102 Re: mORMot 1 » OpenSSL 1.1 wrapper » 2021-02-22 21:31:23

Well written post. Just one question I like to know more. Should I use OpenSSL (Linux and Windows) for ultimate security or let mORMot choose the best as default?
I understood the speed part, mORMot is smart in that, my question is about the security part in both client and sever usage in both OS.

#103 Re: mORMot 1 » mORMot 2 in Good Shape » 2021-02-21 12:42:04

Can you add Lazarus package for mORMot 2?

#106 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-15 09:16:01

Done.
What you explained is necessary for real life tasks, but difficult to find changes in small methods and tests.
The idea was, mORMot has a big test suit, maybe storing it in a DB gives a useful development analytics tool.
Thanks for the feedback.

#107 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-14 17:20:25

Because of our recent tests, I was thinking about how to have better comparison and benchmarking changes, and as D. Richard Hipp likes it for many things, including Fossil; I wanted to log the Tests in a DB. This way, I can recognize a leap/drop of speed and have a better way to query additional info about progress, and also, it is more fun to test and log code this way, and more future opportunities.
I started from your mormot2tests, first I wanted to inherit it, but it is tangled with the console log, so as a test, I did public some protected fields and made a test that stores a simple log.
Full implementation should use an improved TSynTest and more analytics, but as you said before, you like code more than talk, so here is sloppy code to showcase the idea.
Please let me know what you think.
https://gist.github.com/OkobaPatino/7db … a1d422572a

#108 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-14 14:54:44

BTW I am confirming with another FPC too, fixes branch (3.2.1).

#109 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-14 14:51:18

My results for your exact test:

585us 17094017
192us 52083333

#110 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-14 14:48:50

Yes, in an isolated test. But In the context of EngineBatchSend function it acts slower for me. I tried this code:

  //case IdemPCharArray(Method, 'POPUDESI') of
        case IdemPCharArray(Method,['POST','PUT','DELETE','SIMPLE']) of       

Just replacing this line will increase speed.
But in an isolated test, like below, new IdemPCharArray acts 3X faster as you say.

 program project1;

uses
  SysUtils,
  mormot.core.base,
  mormot.core.perf,
  mormot.core.unicode;

var
  T: ILocalPrecisionTimer;
  I, C: integer;
  MethodValue: RawUtf8;
  Method: PUtf8Char;
begin
  T := TLocalPrecisionTimer.Create;
  C := 1000000;
  MethodValue := 'SIMPLE';
  Method := Pointer(MethodValue);
  T.Start;
  for I := 1 to C do
    IdemPCharArray(Method, 'POPUDESI');
  WriteLn('1: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));
  T.Start;
  for I := 1 to C do
    IdemPCharArray(Method, ['POST', 'PUT', 'DELETE', 'SIMPLE']);
  WriteLn('2: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));
  for I := 1 to C do
    PosChar('OUEI', Method[1]);
  WriteLn('3: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));
  ReadLn;
end.                                

To be sure, I am using Lazarus Stable in a Win64 i9 machine.

#111 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-14 10:41:09

I was a little bothered about why V2 is not faster, as it seems you optimized many parts, including JSON parts.
Investigating more, and find out that the new use of overloaded IdemPCharArray(Method, 'POPUDESI') will slow down the code.
I replaced it with the old IdemPCharArray(Method,['POST','PUT','DELETE','SIMPLE']), and it speeded up the "Batch" test more than 10% for TSQLRestServerDB and 25% for TSQLRestServerFullMemory.
For more info, doing old IdemPCharArray for 10M time takes 60ms, compared to 3000ms for V2.
If I wanted to go for a more aggressive path, I would use PosExChar(Method[1],'OUEI') or PosChar('OUEI',Method[1])^ as they are 2X faster than old IdemPCharArray, but in the context of "Batch" test, it will only improve near 5%.

#112 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-14 05:16:36

I wanted to both code look alike so I can make sure that I'm testing the same code. For future work I will use V2.

#114 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-13 16:54:22

Checked it and I can confirm that it is working fast now. On more than 10 tests, it was 95% speed of the V1 in the Batch test for TSQLRestServerDB and 120% for TSQLRestServerFullMemory.
I tried the two Pascal versions and they will be slower in the context of V2.
Thanks for the update.

#115 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-13 09:47:18

Insert PerSec results for 100K, Batch, TSQLRestServerDB and SQLITE_MEMORY_DATABASE_NAME, lmNormal, smOff:
V1:
Old Pos(Inline):
-O3: 662405

Old Pos(Not Inline):
-O3: 663574


New Pos(Inline):
-O3: 541996

New Pos(Not Inline):
-O3: 670591


V2:
Old Pos(Inline):
-O1: 517357
-O2: 533991
-O3: 535920

Old Pos(Not Inline):
-O1: 632255
-O2: 651177
-O3: 652622


New Pos(Inline):
-O1: 520540
-O2: 531053
-O3: 532257

New Pos(Not Inline):
-O1: 631524
-O2: 646981
-O3: 650474

#116 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-13 09:28:15

-O3 (Slow optimization), This is the default for the Release mode of Lazarus/FPC.

#117 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-12 21:28:45

I think you may like to add a TSQLRestStorageInMemory as a compatibility for V1 like the other classes.

Maybe you missed my question so I will ask again:
If I should not call TransactionBegin manually, why it speeds up the inserts in the "Transaction & Batch" test?

For the slowness, all the codes were the same, so it made me to look into the Batch code.
Everything look the same, although I recognizes changes in the JSON handling but anything I found had a sign of improvement in performance not slowness (Great!).
After a while I found out that this line looks weird:

MethodTable := PosChar(Method, '@');

I double checked it, and it seems disabling inline for PosChar, will fix the issue. At least in the test I sent because MethodTable will always be nil.
As of the reason, I do not know why exactly but for V1, FPC will warn that the function will not be inlined, but it will not warn for the V2, and it seems it tried to inline it but can not or made it worse. I couldn't investigate Asm code as the EngineBatchSend function is huge.

#118 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-12 15:37:55

Thanks for the note about problem one. I did not know that and thought that because of posts Like https://blog.synopse.info/?post/2013/06 … cking-mode ("Batch Trans" column)
If I should not call TransactionBegin manually, why it speeds up the inserts in the "Transaction & Batch" test?

For the second problem, yes, that was the problem, and now both speeds are the same as TRestServerFullMemory is 2X slower.
Is TSQLRestStorageInMemory removed form V2? I could not find any note about it in the V2 code.

One question is still unresolved, why "Batch" test is slower in V2 even in TSQLRestServerDB?

Thanks.

#119 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-12 14:16:29

I am trying to discover V2 in attempting to match your samples in the blog and documents. That is the reason I can not make sure my code is correct. Maybe I am doing something wrong. I am running that exact code I shared before, maybe you can run them.
Also, testing in a PC with an i9 with configurated on full performance and trying the test at least five times before sharing with you. Both projects are in Release (-o3 and no debug) mode, and the V1 package is on -o3 optimization and no debug info. And of course, I am using the latest version pulled again now to be sure.
Here is the result for running only the Memory servers with much more (2M) inserts.

V1:

One: 2.03s, 1us, 982155
Transaction: 2.17s, 1us, 918703
Batch: 2.68s, 1us, 744059
Transaction & Batch: 2.58s, 1us, 773406

V2:

One: 2.01s, 1us, 993000
Transaction: 2.14s, 1us, 930859
Batch: 5.12s, 2us, 390090
Transaction & Batch: 2.86s, 1us, 698332

As you can see the big difference is in the Batch mode.
I checked the ram usage too, the V1 uses 550MB but V2 uses 1100MB.

Also please not that in my previous tests, even SQLite tests in V2 and Batch are slower, it is less than Memory engine difference as I am inserting 2K records.

#120 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-12 07:09:24

I was trying to compare them and find out that V2 is slower in some cases.
Simple and transactional inserts in the SQLite database are the same, but it is slower when arrays come to play when using batch or memory databases.  I tried a bigger number (1M) of records for the memory engine, and the results are the same.
Both tested with stable Lazarus on Win64, on release mode, and ran independently.
Can you please give them a check that if my results are valid?

V1 code:

program project1;

uses
  SysUtils,
  SynCommons,
  mORMot,
  SynSQLite3,
  mORMotSQLite3,
  SynSQLite3Static;

type

  { TTest }

  TTest = class(TSQLRecord)
  private
    FName: RawUtf8;
  published
    property Name: RawUtf8 read FName write FName;
  end;

var
  Server: TSQLRest;
  Test: TTest;

  procedure TestServer;
  var
    T: ILocalPrecisionTimer;
    I, C: Integer;
    Batch: TSQLRestBatch;
  begin
    T := TLocalPrecisionTimer.Create;
    C := 2000;
    //One
    T.Start;
    for I := 1 to C do
      Server.Add(Test, True);
    WriteLn('One: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

    //Transaction
    T.Start;
    Server.TransactionBegin(TTest, 1);
    for I := 1 to C do
      Server.Add(Test, True);
    Server.Commit(1);
    WriteLn('Transaction: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

    //Batch
    T.Start;
    Batch := TSQLRestBatch.Create(Server, TTest);
    for I := 1 to C do
      Batch.Add(Test, True);
    Server.BatchSend(Batch);
    Batch.Free;
    WriteLn('Batch: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

    //Transaction & Batch
    T.Start;
    Server.TransactionBegin(TTest, 1);
    Batch := TSQLRestBatch.Create(Server, TTest);
    for I := 1 to C do
      Batch.Add(Test, True);
    Server.BatchSend(Batch);
    Batch.Free;
    Server.Commit(1);
    WriteLn('Transaction & Batch: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

  end;

var
  Servers: array of TSQLRest;
  LM: TSQLLockingMode;
  Sy: TSQLSynchronousMode;
begin
  Servers := nil;
  Insert(TSQLRestServerDB.CreateWithOwnModel([TTest], ChangeFileExt(ParamStr(0), '.db')), Servers, Length(Servers));
  Insert(TSQLRestServerDB.CreateWithOwnModel([TTest], SQLITE_MEMORY_DATABASE_NAME), Servers, Length(Servers));
  Insert(TSQLRestStorageInMemory.Create(TTest, nil), Servers, Length(Servers));
  Test := TTest.Create;
  Test.Name := 'Name';

  for Server in Servers do
  begin
    WriteLn(Server.ClassName);
    if Server is TSQLRestServerDB then
      with TSQLRestServerDB(Server) do
      begin
        CreateMissingTables;
        for LM in [lmNormal, lmExclusive] do
          for Sy in [smOff, smNormal, smFull] do
          begin
            DB.LockingMode := LM;
            DB.Synchronous := Sy;
            WriteLn(GetEnumName(TypeInfo(TSQLLockingMode), Integer(LM))^, ', ', GetEnumName(TypeInfo(TSQLSynchronousMode), Integer(Sy))^);
            TestServer;
            WriteLn;
          end;
      end
    else
      TestServer;
    Server.Free;
  end;

  Test.Free;
  ReadLn;
end.

V2 Code:

program project1;

uses
  SysUtils,
  mormot.core.base,
  mormot.core.perf,
  mormot.core.rtti,
  mormot.orm.core,
  mormot.orm.sql,
  mormot.orm.sqlite3,
  mormot.rest.core,
  mormot.rest.memserver,
  mormot.rest.sqlite3,
  mormot.db.raw.sqlite3,
  mormot.db.raw.sqlite3.static;

type

  { TTest }

  TTest = class(TSQLRecord)
  private
    FName: RawUtf8;
  published
    property Name: RawUtf8 read FName write FName;
  end;

var
  Server: TSQLRest;
  Test: TTest;

  procedure TestServer;
  var
    T: ILocalPrecisionTimer;
    I, C: Integer;
    Batch: TSQLRestBatch;
  begin
    T := TLocalPrecisionTimer.Create;
    C := 2000;
    //One
    T.Start;
    for I := 1 to C do
      Server.Add(Test, True);
    WriteLn('One: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

    //Transaction
    T.Start;
    Server.TransactionBegin(TTest, 1);
    for I := 1 to C do
      Server.Add(Test, True);
    Server.Commit(1);
    WriteLn('Transaction: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

    //Batch
    T.Start;
    Batch := TSQLRestBatch.Create(Server.Orm, TTest);
    for I := 1 to C do
      Batch.Add(Test, True);
    Server.BatchSend(Batch);
    Batch.Free;
    WriteLn('Batch: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

    //Transaction & Batch
    T.Start;
    Server.TransactionBegin(TTest, 1);
    Batch := TSQLRestBatch.Create(Server.Orm, TTest);
    for I := 1 to C do
      Batch.Add(Test, True);
    Server.BatchSend(Batch);
    Batch.Free;
    Server.Commit(1);
    WriteLn('Transaction & Batch: ', T.Stop, ', ', T.ByCount(C), ', ', T.PerSec(C));

  end;

var
  Servers: array of TSQLRest;
  LM: TSQLLockingMode;
  Sy: TSQLSynchronousMode;
begin
  Servers := nil;
  Insert(TSQLRestServerDB.CreateWithOwnModel([TTest], ChangeFileExt(ParamStr(0), '.db')), Servers, Length(Servers));
  Insert(TSQLRestServerDB.CreateWithOwnModel([TTest], SQLITE_MEMORY_DATABASE_NAME), Servers, Length(Servers));
  Insert(TRestServerFullMemory.CreateWithOwnModel([TTest]), Servers, Length(Servers));
  Test := TTest.Create;
  Test.Name := 'Name';

  for Server in Servers do
  begin
    WriteLn(Server.ClassName);
    if Server is TSQLRestServerDB then
      with TSQLRestServerDB(Server) do
      begin
        CreateMissingTables;
        for LM in [lmNormal, lmExclusive] do
          for Sy in [smOff, smNormal, smFull] do
          begin
            DB.LockingMode := LM;
            DB.Synchronous := Sy;
            WriteLn(GetEnumName(TypeInfo(TSQLLockingMode), Integer(LM))^, ', ', GetEnumName(TypeInfo(TSQLSynchronousMode), Integer(Sy))^);
            TestServer;
            WriteLn;
          end;
      end
    else
      TestServer;
    Server.Free;
  end;

  Test.Free;
  ReadLn;
end.

V1 Results:

TSQLRestServerDB
lmNormal, smOff
One: 590.84ms, 295us, 3384
Transaction: 6.98ms, 3us, 286450
Batch: 5.82ms, 2us, 343288
Transaction & Batch: 3.44ms, 1us, 580551

lmNormal, smNormal
One: 5.77s, 2.88ms, 346
Transaction: 9.57ms, 4us, 208986
Batch: 26.32ms, 13us, 75973
Transaction & Batch: 5.72ms, 2us, 349101

lmNormal, smFull
One: 8.86s, 4.43ms, 225
Transaction: 10.74ms, 5us, 186098
Batch: 31.87ms, 15us, 62749
Transaction & Batch: 7.08ms, 3us, 282366

lmExclusive, smOff
One: 35.09ms, 17us, 56991
Transaction: 6.74ms, 3us, 296559
Batch: 3.24ms, 1us, 616712
Transaction & Batch: 3.07ms, 1us, 650829

lmExclusive, smNormal
One: 6.42s, 3.21ms, 311
Transaction: 10.60ms, 5us, 188643
Batch: 28.30ms, 14us, 70671
Transaction & Batch: 6.10ms, 3us, 327385

lmExclusive, smFull
One: 8.36s, 4.18ms, 239
Transaction: 11.20ms, 5us, 178491
Batch: 37.02ms, 18us, 54014
Transaction & Batch: 7.88ms, 3us, 253742

TSQLRestServerDB
lmNormal, smOff
One: 9.11ms, 4us, 219466
Transaction: 6.57ms, 3us, 304043
Batch: 3.12ms, 1us, 639181
Transaction & Batch: 2.96ms, 1us, 673854

lmNormal, smNormal
One: 9.41ms, 4us, 212336
Transaction: 6.66ms, 3us, 300165
Batch: 2.99ms, 1us, 668002
Transaction & Batch: 2.98ms, 1us, 670690

lmNormal, smFull
One: 9.55ms, 4us, 209314
Transaction: 6.68ms, 3us, 299132
Batch: 3.01ms, 1us, 664451
Transaction & Batch: 3.01ms, 1us, 663790

lmExclusive, smOff
One: 9.57ms, 4us, 208877
Transaction: 6.70ms, 3us, 298107
Batch: 3.02ms, 1us, 661157
Transaction & Batch: 3ms, 1us, 666000

lmExclusive, smNormal
One: 9.49ms, 4us, 210548
Transaction: 6.69ms, 3us, 298641
Batch: 3.01ms, 1us, 663349
Transaction & Batch: 3ms, 1us, 664893

lmExclusive, smFull
One: 9.47ms, 4us, 211104
Transaction: 6.56ms, 3us, 304878
Batch: 2.93ms, 1us, 682128
Transaction & Batch: 3.05ms, 1us, 655093

TSQLRestStorageInMemory
One: 1.54ms, 0us, 1296176
Transaction: 1.51ms, 0us, 1321877
Batch: 145us, 0us, 13793103
Transaction & Batch: 101us, 0us, 19801980

V2 Results:

TSqlRestServerDB
lmNormal, smOff
One: 592.18ms, 296us, 3377
Transaction: 6.87ms, 3us, 291078
Batch: 6.65ms, 3us, 300480
Transaction & Batch: 4.06ms, 2us, 492004

lmNormal, smNormal
One: 5.93s, 2.96ms, 336
Transaction: 9.13ms, 4us, 218842
Batch: 23.47ms, 11us, 85215
Transaction & Batch: 6.18ms, 3us, 323153

lmNormal, smFull
One: 8.67s, 4.33ms, 230
Transaction: 10.70ms, 5us, 186776
Batch: 33.59ms, 16us, 59530
Transaction & Batch: 7.96ms, 3us, 251067

lmExclusive, smOff
One: 35.45ms, 17us, 56404
Transaction: 6.71ms, 3us, 297885
Batch: 3.92ms, 1us, 509813
Transaction & Batch: 3.73ms, 1us, 536049

lmExclusive, smNormal
One: 6.67s, 3.33ms, 299
Transaction: 9.88ms, 4us, 202347
Batch: 26.87ms, 13us, 74410
Transaction & Batch: 6.62ms, 3us, 301841

lmExclusive, smFull
One: 8.46s, 4.23ms, 236
Transaction: 10.68ms, 5us, 187143
Batch: 34.14ms, 17us, 58575
Transaction & Batch: 7.91ms, 3us, 252684

TSqlRestServerDB
lmNormal, smOff
One: 9.20ms, 4us, 217296
Transaction: 6.42ms, 3us, 311332
Batch: 3.79ms, 1us, 526592
Transaction & Batch: 3.58ms, 1us, 558035

lmNormal, smNormal
One: 9.15ms, 4us, 218483
Transaction: 6.48ms, 3us, 308641
Batch: 3.61ms, 1us, 553556
Transaction & Batch: 3.59ms, 1us, 556173

lmNormal, smFull
One: 9.35ms, 4us, 213766
Transaction: 6.48ms, 3us, 308261
Batch: 3.65ms, 1us, 547495
Transaction & Batch: 3.62ms, 1us, 551724

lmExclusive, smOff
One: 9.29ms, 4us, 215215
Transaction: 6.52ms, 3us, 306560
Batch: 3.64ms, 1us, 548546
Transaction & Batch: 3.61ms, 1us, 552638

lmExclusive, smNormal
One: 9.35ms, 4us, 213857
Transaction: 6.53ms, 3us, 306278
Batch: 3.62ms, 1us, 551419
Transaction & Batch: 3.61ms, 1us, 553709

lmExclusive, smFull
One: 9.37ms, 4us, 213265
Transaction: 6.49ms, 3us, 307787
Batch: 3.62ms, 1us, 551571
Transaction & Batch: 3.63ms, 1us, 550964

TRestServerFullMemory
One: 1.46ms, 0us, 1363326
Transaction: 1.51ms, 0us, 1319261
Batch: 1.78ms, 0us, 1119194
Transaction & Batch: 1.79ms, 0us, 1115448

#121 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-11 12:21:17

Just tested, it works fine, Thank you.
Now I can try my test package. Although it has some problems with mormot.core.text being recompiled and lead to checksum changes.

#122 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-11 12:05:36

Thanks for the clarification about formatting.
I've got some problem with Trunk so I was asking the exact version.
There is no Lazarus 2.10 branch that I could find, do you mean 2.0.10 or 2.1.0 (Trunk)? If it is the second one, as it is the trunk can you give me the exact revision so I can debug?
BTW I am using FPCUPdelux.

#123 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-11 10:25:33

What is the preferred version of Lazarus and FPC to work with version 2? Also can you please add Lazarus packages?

#124 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-11 08:10:40

I understand, formatting the code is a matter of preference. Just adding my opinion I think, this is a good way for long properties, but for simple ones, and they are a lot, it makes the code somehow more complicated and scary. For example, TSynConnectionDefinition is a simple class, but with this new format, it looks like it has something tricky going on. Maybe, I am not used to it, but I thought it is good to share it with you.

#125 Re: mORMot 1 » Revision 2.x of the framework » 2021-01-10 12:20:18

@ab I noticed you broke the propertied to two lines, can I ask why? Is it a readably improvement or something else?

#126 Re: mORMot 1 » Compability with FPC Trunk 3.3.1 » 2019-09-30 12:01:20

ab, defining FPC_PROVIDE_ATTR_TABLE does not solve the problem.
"SynFPCTypInfo.pas(109,3) Fatal: Syntax error, "BEGIN" expected but "[" found"

AOG, it will be very useful to have it as Trunk has more new options.

#127 mORMot 1 » Compability with FPC Trunk 3.3.1 » 2019-09-29 13:42:21

okoba
Replies: 4

Hi,

mORMot is not compiling with last version of FPC because of error in the SynFPCTypInfo unit.
Do anyone know a workaround?

Board footer

Powered by FluxBB