mORMot Open Source

DigDiver

Member

Registered: 2013-04-29

Posts: 137

[590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

The new version of BATCH adding is significantly slower than the previous version. And BATCH fails when inserting a duplicate record if the field contains the unique index. (the generated SQL should be insert or ignore into tablename / insert or replace into tablename)

From timeline: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB will now perform SQLite3 multi-INSERT statements: performance boost is from 2x (memory with transaction) to 60x (full locking, w/out transaction)

In my case inserting 1M records takes more than 3 minutes. In the previous version it took about 43 sec!

Client Code:

  dm.GroupClient.BatchStart(TEmails, 5000);
...
   dm.GroupClient.BatchAdd(FEmails, True);
   inc(FInsertCount);

   if FInsertCount >= 5000  then
     begin
       FInsertCount := 0;

      if dm.GroupClient.BatchSend(FArray) <> HTML_SUCCESS then
       begin
         ShowLastClientError(dm.GroupClient);
         ModalResult := mrNone;
         dm.GroupClient.BatchAbort;
         if NeedToCreateTable then
           dm.SettingsClient.Delete(TGroups, FGroupID);
         exit;
       end;
   end;

 ...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

On our side, before:

 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	450	460	76661	100575
SQLite3 (file off)	2107	2007	75088	105203
SQLite3 (file off exc)	28105	31666	81621	106874
SQLite3 (mem)		67944	88528	86743	106748

after:

 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	418	28556	77432	168856
SQLite3 (file off)	1810	76847	69029	186741
SQLite3 (file off exc)	28131	180988	82991	190774
SQLite3 (mem)		69033	212422	83503	212811

This was for 5000 records.

Inserting a duplicate record was never supported, for several reasons:
- we use the CRUD paradgim, which has diverse Create and Update verbs (not fully RESTful, but IMHO easier to work with, especially with our automatic ID generation);
- some SQL databases do not support the INSERT OR UPDATE statement.

I will retry with other settings, to reproduce your remark.
Perhaps the auto-transaction feature has an issue.
Did you try with an external SQLite3 table?
Could you try with BatchStart(TEmails,10000) instead of 5000?

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

I think that the error is in the implementation of the function TSQLRestServer.EngineBatchSend.

First TransactionBegin is called, then adding records:

 ID := EngineAdd(RunTableIndex,Value);

after RowCountForCurrentTransaction reaches AutomaticTransactionPerRow, the Commit transaction is called.

And at the end of EngineBatchSend function, the RunningBatchRest.InternalBatchStop is called, which makes the insertion of the same data through insert sql.

About inserting duplicates:

When we use BatchSend(FArray) we expect that FArray contains IDs of inserted records or 0 if insert fails (duplicates for example).

   ID := EngineAdd(RunTableIndex,Value);
   Results[Count] := ID;

If we use the new implementation with batch inserting record through INSERT INTO sql, we cannot skip adding duplicates, the exception will raise and the BatchSend will fail at all.

Did you try with an external SQLite3 table?

No, there is no need to use an external SQLite3 table in my project.

Could you try with BatchStart(TEmails,10000) instead of 5000?

I tried. It makes no big difference.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

I suspect there was an issue not with multi-insert, but with the AutomaticTransactionPerRow feature.

Some numbers with 500,000 records added, and BatchStart(TSQLRecordSample,100000).
Before the fix:

                        Direct  Batch   Trans   Batch Trans 
SQLite3 (file full)     476     23076   57964   175017 
SQLite3 (file off)      2145    77137   58506   122808 
SQLite3 (file off exc)  27463   171755  75665   180652 
SQLite3 (mem)           63954   193165  78431   197658 
TObjectList (static)    282645  389493  243546  396340 
TObjectList (virtual)   277315  382404  231160  383441 

After the fix:

                        Direct  Batch   Trans   Batch Trans 
SQLite3 (file full)  498    175724  59283  177969 

Now, "Batch" and "Batch Trans" have the same numbers, with AutomaticTransactionPerRow = 100000.

See http://synopse.info/fossil/info/7335db42cb

FYI with 1,000,000 rows, we got:

                        Direct  Batch   Trans   Batch Trans 
SQLite3 (file full)     416     153095  46451   169445 
SQLite3 (file off exc)  26848   174058  73746   172854 
TObjectList (static)    281531  347183  224820  342213 

Read speed
                       By one  All Virtual  All Direct 
SQLite3 (file full)    21049   349562       349148 
SQLite3 (file off exc) 86291   354590       345916 
TObjectList (static)   247273  707809       709820 

It takes around 6 seconds to insert 1,000,000 rows.
But this is without HTTP communication here: you have object to JSON serialization, BATCH process handling, JSON to (multi-insert) SQL statements process, and SQlite3 execution.

For 1,000,000 rows, and Client.BatchStart(TSQLRecordSample,100000):

1. With multi-insert:

                        Direct Batch   Trans  Batch Trans 
SQLite3 (file off exc)  21369  176067  72621  169774 

2. Without multi-insert:

                        Direct Batch   Trans  Batch Trans 
SQLite3 (file off exc)  10802  100473  74805  96761 

By design, read speed is the same.

Sounds like if we have indeed a 1.5 x performance boost thanks to multi-insertion in BATCH mode, and AutomaticTransactionPerRow.
Even with 1,000,000 rows.

Thanks for the feedback!

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

The version from: http://synopse.info/fossil/info/7335db42cb does not instert the record. The generated SQL contains RowID field, I think there must be no RowID.

SQL:

INSERT INTO Emails (RowID,Email,First_Name,Last_Name,Recipient_Name,Subscribed,Subscribe_Date,GroupID,Fields) VALUES (:(1):,:(''email@domain.com''):,:(''''):,:(''''):,:(''''):,:(1):,:(''2014-08-27T14:26:35''):,:(302):,:(''[]''):);

procedure TSQLRestServerDB.InternalBatchStop;
...
    if fBatchValuesCount=1 then begin // handle single record insertion as usual
      Decode.Decode(fBatchValues[0],nil,pInlined,fBatchFirstID);
      SQL := 'INSERT INTO '+Props.SQLTableName+Decode.EncodeAsSQL(False)+';';
      InternalExecute(SQL);
      exit;
    end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

The SQL contains the RowID on purpose: it is computed ahead-of-time in TSQLRestServerDB.MainEngineAdd, storing max(RowID) in fBatchFirstID. This is the same exact algorithm than with external tables, in mORMotDB.pas.
The record is inserted as expected.

I've also fixed another issue when AutomaticTransactionPerRow was not creating transactions in synch with InternalBatchStart/InsternalBatchStop.
See http://synopse.info/fossil/info/c3f63dd4ee

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

procedure TSQLRestServerDB.InternalBatchStop;
...
repeat until Statement^.Step<>SQLITE_ROW; <-------------- EXCEPTION 'Error SQLITE_CONSTRAINT (19) - "UNIQUE constraint failed: Emails.ID"'

Generated SQL:

insert into Emails (RowID,Email,First_Name,Last_Name,Recipient_Name,Subscribed,Subscribe_Date,GroupID,Fields) VALUES (?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?);

VALUES:

('1', 'email1@domain.com', 'First', 'Last', '', '0', '2014-08-27T14:49:31', '306', '[]', '2', 'email2@domain.com', 'Gary', 'Booher', '', '0', '2014-08-27T14:49:31', '306', '[]')

the DB already contains this IDs

Last edited by DigDiver (2014-08-27 12:01:04)

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

Steps to reproduce:

1. Start the server with an empty DB
2. Add data (BatchStart, BatchSend...) - all OK
3. Stop the server
4. Start the server again (DB contains data from Step 2)
5. Adding data (BatchStart, BatchSend...) - FAILURE problem in MainEngineAdd.

function TSQLRestServerDB.MainEngineAdd
...
if fBatchFirstID=0 then begin
SQL := 'select max(rowid) from '+SQL;
if InternalExecute(SQL,nil,nil,nil,@LastID) then <------------------------------------ LastID = 0 ???
fBatchFirstID := LastID+1 else begin
fBatchFirstID := -1; // will force error for whole BATCH block
exit;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

Indeed.

Fixed by http://synopse.info/fossil/info/a3289826a9

Sorry for the issue.

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

I think, that should be: if InternalExecute(SQL,@LastID, nil,nil,nil)  instead of if InternalExecute(SQL,nil,nil,nil,@LastID):

      if fBatchFirstID=0 then begin
        SQL := 'select max(rowid) from '+SQL;

        if InternalExecute(SQL,@LastID, nil,nil,nil) then
          fBatchFirstID := LastID+1 else begin
          fBatchFirstID := -1; // will force error for whole BATCH block
          exit;
        end;

The same problem may be in the function SQLRestServerDB.MainEngineUpdateField:

        if not InternalExecute(FormatUTF8('select RowID from % where %=:(%):',
           [SQLTableName,WhereFieldName,WhereValue]),nil,nil,@ID) then
          exit else
          if ID=nil then begin
            result := true; // nothing to update, but return success
            exit;
          end;

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

ups, a little late

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

In my program users import hundreds or thousands of emails in the database.
Naturally there are duplicates in the email lists.
I need the import NOT to break when it detects a duplicate like it was in the previous version.
I would like to be able to do it automatically.
SQLITE has the ability to ignore duplicates automatically using insert or ignore Into SQL.

For example:

Add new Occasion :

  TSQLOccasion = (
    soSelect,
    soInsert,
    soUpdate,
    soDelete,
    soInsertIgnore);

Little modification in function TJSONObjectDecoder.EncodeAsSQLPrepared

function TJSONObjectDecoder.EncodeAsSQLPrepared(const TableName: RawUTF8;
  Occasion: TSQLOccasion; const UpdateIDFieldName: RawUTF8;
  MultipleInsertCount: integer): RawUTF8;
const SQL: array[Boolean] of PUTF8Char = (
   'insert into %%','update % set % where %=?');
var F: integer;
    P: PUTF8Char;
    tmp: RawUTF8;
begin
  result := '';
  if FieldCount<>0 then begin
    if ((Occasion<>soInsert) and (Occasion<>soInsertIgnore)) or (MultipleInsertCount<=0) then
      MultipleInsertCount := 1;
    SetLength(tmp,FieldNameLen+4*FieldCount*MultipleInsertCount+24); // max length
    P := pointer(tmp);
    case Occasion of
    soUpdate: begin
      // returns 'COL1=?,COL2=?' (UPDATE SET format)
      for F := 0 to FieldCount-1 do begin
        P := AppendRawUTF8ToBuffer(P,DecodedFieldNames[F]);
        PInteger(P)^ := Ord('=')+Ord('?')shl 8+Ord(',')shl 16;
        inc(P,3);
      end;
      dec(P);
    end;
    soInsert, soInsertIgnore: begin
      // returns ' (COL1,COL2) VALUES (?,?)' (INSERT format)
      PWord(P)^ := Ord(' ')+ord('(')shl 8;
      inc(P,2);
      for F := 0 to FieldCount-1 do begin
        P := AppendRawUTF8ToBuffer(P,DecodedFieldNames[F]);
        P^ := ',';
        inc(P);
      end;
      P := AppendRawUTF8ToBuffer(P-1,') VALUES (');
      repeat
        for F := 1 to FieldCount do begin
          PWord(P)^ := Ord('?')+Ord(',')shl 8;
          inc(P,2);
        end;
        P[-1] := ')';
        dec(MultipleInsertCount);
        if MultipleInsertCount=0 then
          break;
        PWord(P)^ := Ord(',')+Ord('(')shl 8;
        inc(P,2);
      until false;
    end;
    else
      raise EORMException.Create('Invalid EncodeAsSQLPrepared() call');
    end;
    assert(P-pointer(tmp)<length(tmp));
    SetLength(tmp,P-pointer(tmp));
  end else
    if Occasion=soUpdate then
      exit else
      tmp := ' default values';
 if Occasion = soInsertIgnore then
  Result :=  FormatUTF8('insert or ignore into %%',[TableName,tmp,UpdateIDFieldName])
 else
  result := FormatUTF8(SQL[Occasion=soUpdate],[TableName,tmp,UpdateIDFieldName]);
end;

in procedure TSQLRestServerDB.InternalBatchStop(IgnoreDuplicates: boolean= false);

 if IgnoreDuplicates then
  SQL := Decode.EncodeAsSQLPrepared(Props.SQLTableName, soInsertIgnore ,'') 
else 
 SQL := Decode.EncodeAsSQLPrepared(Props.SQLTableName,soInsert,'');

end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

Does BATCH process work as expected, now?

How do you transmit the "IgnoreDuplicates" parameter to TSQLRestServerDB.InternalBatchStop ?

All this won't work with some external databases, which do not support a similar syntax - I think mainly about Oracle.

So I'm not sure we could implement it as such...
It may work for your particular case, but not for external DB.

Furthermore, I still don't understand your point about duplicated emails.
The ORM works with ID/RowID integer as its primary key (as Sqlite3 itself) - how would the BATCH mode know about an existing email?
If the record does not exists, you use Add/BatchAdd, and the ID is computed and returned.
If the record does exist, you know its ID, so you use Update/BatchUpdate.
No such way as identifying "duplicates in the email lists" unless you first read the data, and search for the email.
I'm confused, here...

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

The program allows to have several groups of email addresses.

Email addresses of all groups are stored in one database which is managed by FGroupServer.
The group cannot contain duplicate email addresses. For this purpose I built the index by 'Email','GroupID'.

FGroupServer := TGroupServer.Create(GroupsModel, Format(DefaultWorkPlace + '%d\groups.db3' ,[FWorkPlaceID]), true);
FGroupServer.CreateSQLMultiIndex(TExclusionList, ['Email','GroupID'], True);

For the quick import of an email list I use BatchStart, BatchSend.

No such way as identifying "duplicates in the email lists" unless you first read the data, and search for the email.

Before when EngineBatchSend was working at the RestServer level, there was no problem with duplicate emails.

   ID := EngineAdd(RunTableIndex,Value);
   Results[Count] := ID;

If the email address was already in the group, then ID = 0 (function TSQLRestServerDB.InternalExecute doesn't raise exception), and there was no problem with the import.

Now if the email address is already in the database, then when inserting data in the TSQLRestServerDB.InternalBatchStop function, exception is raised at:
repeat until Statement^.Step<>SQLITE_ROW; after that RollBack(CONST_AUTHENTICATION_NOT_USED) is performed and the entire batch in the quantity AutomaticTransactionPerRow will be ignored.

I understand that when using my code Results: TIntegerDynArray will not always contain correct IDs. In my situation it's not important.

In order changes are not applied to external databases, I added an additional parameter to the function BatchStart.

How I transmit the "IgnoreDuplicates" parameter to TSQLRestServerDB.InternalBatchStop:

If I need to ignore duplicate emails in BatchSend, I transmit the additional parameter SkipDuplicates

const
  AUTOMATICTRANSACTIONPERROW_PATTERN = '"AUTOMATICTRANSACTIONPERROW":';
  AUTOMATICTRANSACTIONPERROW_IGNORE  = '"AUTOMATICTRANSACTIONDUPIGNORE":';

...
function TSQLRest.BatchStart(aTable: TSQLRecordClass;
  AutomaticTransactionPerRow: cardinal; SkipDuplicates: boolean): boolean;

...

  if SkipDuplicates then
   begin
    fBatch.AddShort(AUTOMATICTRANSACTIONPERROW_IGNORE);
    fBatch.Add(Integer(SkipDuplicates));
    fBatch.Add(',');
   end;

On the server I check if the parameter SkipDuplicates: exists

function TSQLRestServer.EngineBatchSend(Table: TSQLRecordClass;
  const Data: RawUTF8; var Results: TIntegerDynArray): integer;

...

  if IdemPChar(Sent,AUTOMATICTRANSACTIONPERROW_IGNORE) then begin
    inc(Sent,Length(AUTOMATICTRANSACTIONPERROW_IGNORE));
    SkipDuplicates :=  GetNextItemInteger(Sent,',');
  end
  else
   SkipDuplicates := 0;

...

RunningBatchRest.InternalBatchStop(SkipDuplicates=1);

In the function InternalBatchStop the existence of SkipDuplicates is checked and the according SQL text is generated:

procedure TSQLRestServerDB.InternalBatchStop(SkipDuplicates: Boolean= false);

...

          if SkipDuplicates then
            SQL := Decode.EncodeAsSQLPrepared(Props.SQLTableName, soInsertIgnore ,'')
          else
           SQL := Decode.EncodeAsSQLPrepared(Props.SQLTableName,soInsert,'');

Now if I need to quickly import data and not to worry about duplicate addresses, I simply transmit SkipDuplicates into the BatchStart function.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

No I got it!

I've added a new BatchOptions parameter to TSQLRest.BatchStart().
By now, there is only boInsertIgnore, and it will work only for SQLite3 internal engine (i.e. mORMotSQLite3), not external DB, since other databases have a very diverse syntax.

This implementation should be a little more open to extension than an explicit "skip duplicate" boolean.

See http://synopse.info/fossil/info/1a2240656a

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

SUPER! Now 1M records via HTTP on a local computer is inserted in 29 sec! instead of 43 sec before.

Thanks ab!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

I find 29 seconds is still a bit high.
With direct access, it takes around 6 seconds to insert 1,000,000 rows.
23 seconds for HTTP communication is very much...
How much does it take on the server side, for only the locked process in TSQLRestServer.URI?
On our side we use a SSD, which is very SQLite3 friendly. Perhaps are you using a regular HD? For performance, a SSD is a good idea - and since SQLite3 uses very little space (e.g. half the space of Firebird) when storing, you should benefit of even a small SSD, just to contain the db file.

BTW, how did you tune the ACID behavior of SQLite3?
Did you use the SQLite3 DB to use exclusive locking?
See "ACID and speed" in the SAD 1.18 pdf.

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

In the test computer I use SSD.
Th import process is more complex than simple generating values and sending it via batch.
It reads CSV file, performs fields mapping from CSV record to TSQLRecord.  (takes about 4 sec (with call BatchAdd) for 1M record)

 Type
  TFieldItem = class(TCollectionItem)
  private
   FFieldName   : String;
   FFieldValue  : Variant;
  public
  published
   property Name   : String  read FFieldName  write FFieldName;
   property Value  : Variant  read FFieldValue  write FFieldValue;
  end;

 Type
   TFields = Class(TCollection)
   private
    function  GetItem(index: integer): TFieldItem;
    procedure SetItem(index: integer; value: TFieldItem);

    function GetItemByName(name: string): TFieldItem;
    procedure SetItemByName(name: string; value: TFieldItem);
   public
    constructor Create;
    function Add(_FFieldName: String; _FFieldValue: Variant): TFieldItem;

    property Items[index: integer]: TFieldItem read GetItem write SetItem; default;
    property Item[name: string]: TFieldItem read GetItemByName write SetItemByName;
   end;

 Type
   TEmails = class(TSQLRecord)
   private
     FGroupID : Integer;
     FEmail: String;
     FFirst_Name: String;
     FLast_Name: String;
     FRecipientName : String;
     FSubscribed: Integer;
     FSubscribe_Date: TDateTime;
     FFields: TFields;
   public
    constructor Create; override;
    destructor Destroy; override;

   published
     property Email: String read FEmail Write FEmail;
     property First_Name: String read FFirst_Name Write FFirst_Name;
     property Last_Name: String read FLast_Name Write FLast_Name;
     property Recipient_Name : String read FRecipientName write FRecipientName;
     property Subscribed: Integer read FSubscribed Write FSubscribed;
     property Subscribe_Date: TDateTime read FSubscribe_Date  Write FSubscribe_Date;
     property GroupID : Integer read FGroupID write FGroupID;
     property Fields: TFields read FFields write FFields;
   end;

Plus in db several indexes are created:

 FGroupServer.CreateSQLMultiIndex(TEmails, ['Email','GroupID'], True);
 FGroupServer.CreateSQLIndex(TEmails, 'Subscribed', False);
 FGroupServer.CreateSQLIndex(TEmails, 'Email', False);
 FGroupServer.CreateSQLIndex(TEmails, 'GroupID', False);

I try to use exclusive locking mode, but speed is not grown significantly (about 25 Sec).
Plus HTTP connection uses SynAes encryption.

In any case the inserting speed is very high for my purpose and none of competitor's software has the import speed close to mine.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

AFAIK, if you defined:

FGroupServer.CreateSQLMultiIndex(TEmails, ['Email','GroupID'], True);

Then the following index is not mandatory for SQLite3:

 FGroupServer.CreateSQLIndex(TEmails, 'Email', False);

See "1.6 Multi-Column Indices" in http://www.sqlite.org/queryplanner.html:
"Hence, a good rule of thumb is that your database schema should never contain two indices where one index is a prefix of the other. Drop the index with fewer columns. SQLite will still be able to do efficient lookups with the longer index."

In any case the inserting speed is very high for my purpose and none of competitor's software has the import speed close to mine.

This is making my day!

Your software sounds indeed great!

BTW, why are you using a collection like TFields, and not a "TEmails.Fields: variant" published field, containing a TDocVariant?
I suspect it could be more versatile, since you would be able to use any level of nested records, and late-binding if needed.

edismo

Member

From: Brazil

Registered: 2013-10-04

Posts: 34

Re: [590567d3f0] Leaf: BATCH adding in TSQLRestServerDB

This post saved my day and a sprint.
Thank you