mORMot Open Source

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 506

Very Slow BatchAdd

Hi Arnaud, today i write a little program to copy records from client to Server with BatchAdd

I think the Speed is very bad...
500 Records/s
the most time is used on the server to insert the records. The Server stores the record in SQLite Database.
(Does BatchAdd not use transactions on Server ?)

---

Rad Studio 12.1 Santorini

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

Re: Very Slow BatchAdd

Use the AutomaticTransactionPerRow parameter, as stated by the documentation.

See http://synopse.info/files/html/Synopse% … l#NDX_1482
and http://synopse.info/files/html/Synopse% … ml#TITL_60

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 506

Re: Very Slow BatchAdd

TYVM - now it works faster !

What do you think Arnaud.

Actually i have to Initialise a SQLRecord - Table with 80000 Records.
there is a key named Mandant to which the Records belong.

Cause i'm initialising i have to delete all Records of mandant first.

Therefore i created this Loop (is there a better/faster way ?)

              Lrec := TSQLDFRecord.CreateAndFillPrepare(Database, 'Mandant = ?', [Mandant], 'ID');
              try
                Database.BatchStart(TSQLDFRecord, 2500);
                counter := 0;
                while Lrec.FillOne do begin
                  inc(counter);
                  Database.BatchDelete(Lrec.ID);
                  if (counter mod 2500 = 0) then begin
                    Database.BatchSend(BatchResult);
                    Database.BatchStart(TSQLDFRecord, 2500);
                  end;
                end;
                Database.BatchSend(BatchResult);
              finally
                FreeAndNil(Lrec);
              end;

the easier way would be this, but i get Timeout in BatchSend.
The Problem: I think Batchsend's timeout has to increase with the Number of BatchDelete a added to the Batch.

              Lrec := TSQLDFRecord.CreateAndFillPrepare(Database, 'Mandant = ?', [Mandant], 'ID');
              try
                Database.BatchStart(TSQLDFRecord, 2500);
                while Lrec.FillOne do
                  Database.BatchDelete(Lrec.ID);
                Database.BatchSend(BatchResult);
              finally
                FreeAndNil(Lrec);
              end;

The same happens if i add 80000 Records in one BatchSend - Call.
I think that BatchSend's timeout calculation is wrong.

---

Rad Studio 12.1 Santorini

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

Re: Very Slow BatchAdd

Why not just set a "where clause" to the delete process, directly?

  Database.Delete(TSQLDFRecord,'Mandant = ?',[Mandant]);

See:

    /// delete a member with a WHERE clause
    // - implements REST DELETE collection
    // - return true on success
    // - for better server speed, the WHERE clause should use bound parameters
    // identified as '?' in the FormatSQLWhere statement, which is expected to
    // follow the order of values supplied in BoundsSQLWhere open array - use
    // DateToSQL/DateTimeToSQL for TDateTime, or directly any integer / double /
    // currency / RawUTF8 values to be bound to the request as parameters
    function Delete(Table: TSQLRecordClass; const FormatSQLWhere: RawUTF8;
      const BoundsSQLWhere: array of const): boolean; overload;

Also ensure there is an index on the Mandant field.

And/or run the process on the server side directly, via a service, to avoid the slow communication over the network.