mORMot Open Source

anouri

Member

Registered: 2024-02-11

Posts: 160

Slow sqlite insert with orm

I have rest server :
TPassakRest = class(TRestServerDB)

I add 2000 record using orm.
My hard drive is ssd.

this takes about 10 seconds to execute (slow):

  LFileName := MakePath([Executable.ProgramFilePath, Executable.ProgramName + '.db']);
  inherited Create(aModel, LFileName, aHandleUserAuthentication);

but witout file name takes about 2 seconds:

  inherited Create(aModel, aHandleUserAuthentication);

starttransaction and commit does not help.
where is the problem.

source:
https://gist.github.com/a-nouri/a55fa32 … cf8d2ab90f

Last edited by anouri (Yesterday 13:41:25)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,305

Website

Re: Slow sqlite insert with orm

Do you use external DB or just SQLite3?

For SQLite3, by default the engine is very safe, but also slow, since it blocks and waits for all the data to be actually written to the disk.
You need to set Synchronous := smOff and LockingMode := lmExclusive
https://synopse.info/files/html/Synopse … ml#TITL_60

This is not a mORMot limitation, this is a well known and documented SQLite3 feature.
https://synopse.info/files/html/Synopse … ml#TITL_59

And multiple insert, you should use a TRestBatch.
You will easily reach more than 500,000 inserts per second.

anouri

Member

Registered: 2024-02-11

Posts: 160

Re: Slow sqlite insert with orm

I have external DB but I use sqlite3 for cache some data. There is no relation or mapping between sqlite and external db.

  fPassakServer := TPassakRest.Create(MyModel, fDbConnection, False);
  fPassakServer.DB.Synchronous := smOff;
  fPassakServer.DB.LockingMode := lmExclusive;

I don't feel a significant change in speed after the suggested changes.
Of course, I must say that the first time the data is added to the sqlite db it takes a long time, but the second time the server runs faster.

I am using sqlite because I want query chached data using sql.

Last edited by anouri (Today 06:42:55)

anouri

Member

Registered: 2024-02-11

Posts: 160

Re: Slow sqlite insert with orm

Wait. I made a mistake in my code.
After editing, it started working very quickly (less than a second).

My mistake was that I loaded the data in the restserver.create method, but I had set smOff and lmExclusive after the create method.

Thanks

Last edited by anouri (Today 06:51:42)