mORMot Open Source

noobies

Member

Registered: 2011-09-13

Posts: 139

Best way insert big data (600000 record) from MSSQL to sqlite3?

procedure Fill_RMIS_REG_PN;
var
  i: Integer;
  batch: TSQLRestBatch;
  r: TSQLReg;
  s: string;
begin
  qry := database_oledb.MainConnection.NewStatementPrepared(sql_pn, True);
  qry.Bind(1, 1); //T_REGISTRATION.Status = 1
  qry.Bind(2, 2); //T_REGISTRATION_CANCEL.Status <> 2
  qry.Bind(3, 0); //T_REGISTRATION.AttachmentStatus > 0
  qry.Bind(4, 1); //T_REGISTRATION.Profile in (1, 2)
  qry.Bind(5, 2); //T_REGISTRATION.Profile in (1, 2)
  qry.Bind(6, 101); //T_STATE.StateNumber <> 101
  qry.ExecutePreparedAndFetchAllAsJSON(false, buf);
  tblTemp := TSQLTableJSON.Create('', buf);
  batch := TSQLRestBatch.CreateTSQLRestBatch.Create(ServerDB, TSQLReg, 10000);
  for i := 0 to tblTemp.RowCount - 1 do begin
    s := tblTemp.getU(i, 0); //GUID
    r := TSQLReg.Create;
    try
      r.PatientId       := s;
      r.TFomsCode       := tblTemp.getU(i, 1);
      r.SNILS           := tblTemp.getU(i, 2);
      r.PolicyOmsNumber := tblTemp.GetU(i, 3);
      r.LastName        := tblTemp.GetU(i, 4);
      r.FirstName       := tblTemp.GetU(i, 5);
      r.FatherName      := tblTemp.GetU(i, 6);
      r.Sex             := tblTemp.GetU(i, 7);
      r.Birthday        := DateOf(tblTemp.GetAsDateTime(i, 8));
      r.Address         := tblTemp.GetU(i, 9);
      r.Diff            := tblTemp.GetAsInteger(i, 10);
      if r.ID = 0 then batch.Add(r, True);
    finally
      r.Free;
    end;
  end;
  ServerDB.BatchSend(batch);
  batch.Free;
end;

how speedup insert?

Last edited by noobies (2017-01-27 12:40:56)

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: Best way insert big data (600000 record) from MSSQL to sqlite3?

any idea?
Batch mode add record is best performance or have other ways?

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: Best way insert big data (600000 record) from MSSQL to sqlite3?

At the time of export, set in SQLite:
synchronous = 0
locking_mode = EXCLUSIVE

Michal

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Best way insert big data (600000 record) from MSSQL to sqlite3?

Also consider the method used in SynDbExplorer to export a table into Sqlite3 without in between json marshalling.