Fastest way to Copy one Database to another

[Paffen_Kurt1 (Christian Paffen)]

Hello there,

i want to copy a Database into a sqlite file to transfer a Snapshot of the Database to Clients.
So far i've got this procedure working, but it takes a long time (>20 mins) for a Database, smaller than 1 Gb.

procedure TfMain.CopyDB;
var
  Reparaturs:TList<TReparatur>;
  Ansprechpartners:TList<TAnsprechpartner>;
  // And 39 more Entities
  I:Integer;
begin
  //AureliusConnectionSource and  AureliusConnectionTarget are defined at Design-Time
  Manager:=TObjectManager.Create(AureliusConnectionSource.CreateConnection);
  ManagerLocal:=TObjectManager.Create(AureliusConnectionTarget.CreateConnection);
  try
    //First Entity/Table
    Reparaturs:=Manager.Find<TReparatur>.List;
    for I :=0  to Reparaturs.Count-1 do
        ManagerLocal.Replicate(Reparaturs.Items[I]);
    Reparaturs.Free;

    //Second Entity/Table
    Ansprechpartners:=Manager.Find<TAnsprechpartner>.List;
    for I :=0  to Ansprechpartners.Count-1 do
        ManagerLocal.Replicate(Ansprechpartners.Items[I]);
    Ansprechpartners.Free;
    // And 39 more Entities
    // ....
  finally
      Manager.free;
      Managerlocal.Free;
  end;
end;

I already split this loops into 4 Threads, so that i get 4 sqlite files, which i later add together to one on the client, but i still doubt, that this is the best and efficient Way to do so.
Do you have any ideas or is it possible to speed this up?

Kind regards,

[wlandgraf (Wagner Landgraf)]

Replicate is a very high level procedure, so I don't think it can be optimized too much. Indeed, it's very convenient to migrate data, but that comes with a cost, since it retrieves existing record in the database, check if it exists, if it does it updates, if not it saves, etc..

But since you are using SQLite, one thing that speeds up the insertion is to save all the data in a database transaction. Start a transaction, migrate data (or part of data), them commit, that speeds up significantly.

[Paffen_Kurt1 (Christian Paffen)]

Again a big thank you for the great work and support!
The change to Transaction changed the time from 22 mins down to ~5 mins, what is accaptable for our cause.