batch delete

cypriotcalm · 2016-02-11 14:08:46

Hello!

I want to delete data from a MySQL table in a batch mode by ID. There are about 300 000 (three hundred thousand) I have the following code:

procedure TDataAccessService<T>.DeleteListByID(const AList: IList<T>);
var
  ClientDB: TSQLRest;
  Batch: TSQLRestBatch;
  DataObj: IDataAccess;
begin
  ClientDB := GetClientDB;
  Batch := TSQLRestBatch.Create(ClientDB, GetSQLRecordClass);
  try
    for DataObj in AList do
    begin
      Batch.Delete(DataObj.ID);
    end;
    ClientDB.BatchSend(Batch);
  finally
    FreeAndNil(Batch);
  end;
end;

1. Only 4096 data records are sent in one batch. How can I change this?
2. After BatchSend for each data record to be deleted one sql statement is executed, i.e. "delete from <table> where <ID> = 1" and the same 300 000 times. This is very slow. How can I avoid this?

I tried with a custom SQL statement like "delete from <table> where <ID> in (1,2,3,4,5,6, ...)"; this approach is more efficient. But I don't want to use sql directly.

How can I delete the data fast using the mORMot?

Thank you for your help!

ab · 2016-02-11 15:57:44

Use TSQLRest.Delete() with a WHERE clause having "in (....)":

function TSQLRest.Delete(Table: TSQLRecordClass; const SQLWhere: RawUTF8): boolean;

A simple way to create the WHERE clause may be, from aID: TIDDynArray:

Int64DynArrayToCSV(aIDs,length(aIDs),'ID in (',')')

This would work on all database engines, including MongoDB, I think.

cypriotcalm · 2016-02-11 16:52:53

ab wrote:

Use TSQLRest.Delete() with a WHERE clause having "in (....)":
function TSQLRest.Delete(Table: TSQLRecordClass; const SQLWhere: RawUTF8): boolean;

I tried

   ...
   GetClientDB.Delete(GetSQLRecordClass, GetPrimaryKeyFieldname + ' IN (1,2,3)');
   ...

I have an error like "no such column IdMyTablename ..."

What could it be?

ab · 2016-02-11 16:57:59

Use the internal TSQLRecord property name, which may be just ID in your case.
The framework should map it to the expected external column name.

cypriotcalm · 2016-02-12 08:58:56

ab wrote:

Use the internal TSQLRecord property name, which may be just ID in your case.
The framework should map it to the expected external column name.

Yes, I map the ID filed. And I tried both:

  // 1.
  GetClientDB.Delete(GetSQLRecordClass, GetPrimaryKeyFieldname + ' IN (1,2,3)');
  or the same
  GetClientDB.Delete(GetSQLRecordClass, GetClientDB.Model.Props[GetSQLRecordClass].ExternalDB.RowIDFieldName + ' IN (1,2,3)'); --------> error Error SQLITE_ERROR using 3.10.0 no such column: MyTableID
  // 2.
  GetClientDB.Delete(GetSQLRecordClass,  'ID IN (1,2,3)'); -----> error [FireDAC][Phys][MySQL] Unknwon column ID in where clause

What kind of problem could it be!?

ab · 2016-02-12 11:40:19

What is you GetClientDB class type?

cypriotcalm · 2016-02-15 08:22:47

ab wrote:

What is you GetClientDB class type?

TSQLRestClientDB

mORMot Open Source

#1 2016-02-11 14:08:46

batch delete

#2 2016-02-11 15:57:44

Re: batch delete

#3 2016-02-11 16:52:53

Re: batch delete

#4 2016-02-11 16:57:59

Re: batch delete

#5 2016-02-12 08:58:56

Re: batch delete

#6 2016-02-12 11:40:19

Re: batch delete

#7 2016-02-15 08:22:47

Re: batch delete

Board footer