mORMot Open Source

Kirill

Member

Registered: 2019-09-08

Posts: 1

Batch Insert and Duplicates

Hello,

I need help to properly implement the batch insert with possible duplicates in the indexed field.

Initialization:

    Props := TSQLDBZEOSConnectionProperties.Create(
      Format(
        'zdbc:mysql://%s/%s?username=%s;password=%s',
        [Server.Host, Server.DataBase, Server.User, Server.Password]),
      '', '', ''); 

    Model := CreateKeywordModel;
    VirtualTableExternalRegister(Model, [TSQLKeyword], Props);
    Client := TSQLRestClientDB.Create(Model, nil, 'SomeTableName', TSQLRestServerDB);
    Client.Server.CreateMissingTables;  

Then the following:

      Client.BatchStart(TSQLKeyword, List.Count, [boInsertOrIgnore]);
      for I := 0 to List.Count - 1 do begin
        Item := TSQLKeyword(List.Items[I]);
        Client.BatchAdd(Item, True);
      end;

      R := Client.BatchSend(IDs);

R = 500, IDs array has become empty after I tried to insert 10 duplicates with 90 new items. I need a way to count "new" and "duplicates" items. Please advise.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,224

Website

Re: Batch Insert and Duplicates

One clean solution I see is first check for duplicates, then add a clean set of data (with no duplicate) in the batch.
And do it on the server side, not on the client side, within a service, using the TSQLRestServer.WriteLock/WriteUnlock methods.

It will be fast and efficient (with the proper index on the duplicated fields).
If you monitor that checking the duplicates via a SELECT is slow on MySQL, then you may maintain an array of used values in memory on the server side.

keinn

Member

Registered: 2014-10-20

Posts: 100

Re: Batch Insert and Duplicates

similar question here:
when doing such Batch Insert (already checked duplicates), or Single Insert many times （eg: insert 1000 rows of data ）,
we need to hnow if the data already exsist in DB,
if we use

TSQLRecord*.Create(aRest,'id=?',[i])

  to check first ,the insert process will be very very slow, the already exist data may or maynot need update...

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Re: Batch Insert and Duplicates

Why not assign cross-node unique  IDs using TSynUniqueIdentifierGenerator on the client side?

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.