Fast Loading of into SqLite DB

[bryan.ray]

Hello,
I have a procedure I'm trying to optimize (currently taking several hours to load data).
Is there any way to speed this process up? The FileList.Count is around 20K per file and I have about 30 files to process. Using Unidac 6.1.3 for XE8, Win 7
Thanks for any suggestions.
Bryan

Code: Select all

procedure ReadModelFileToDB(inFile, inStormID : String);
var
  FileList : TStringList;
  x : integer;
  aRec : TModelRecord;
  aModel : TModelObject;
  aList : TList<TModelRecord>;
begin
    DM.sqliteQuery.SQL.Clear;
    //Create the table if it does not exist...
    DM.sqliteQuery.SQL.Add('CREATE TABLE IF NOT EXISTS '+QuotedStr('file_'+inStormID)
                          +' (PosDateTime varchar(24), Model varchar(5), Tau Int, Lat double, Lon double, Wind Int, Pres Int)');
    DM.sqliteQuery.Execute;
    //add the data
    aList := TList<TModelRecord>.Create;

    aModel := TModelObject.Create;
    FileList := TStringList.Create;
    FileList.LoadFromFile(InFile);
    Try
      for x := 0 to FileList.Count -1 do 
      // reading a csv file (I make some formatting changes with the ExtractModelDataToModelRecord)
      begin
        aRec := aModel.ExtractModelDataToModelRecord(FileList.Strings[x]);
        DM.sqliteQuery.SQL.Clear;
        DM.sqliteQuery.SQL.Add('INSERT OR REPLACE INTO '+QuotedStr('file_'+inStormID)
                      +' VALUES ('+QuotedStr(aRec.PosDateTimeStr)+', '+ QuotedStr(aRec.MType)+', '
                      + QuotedStr(IntToStr(aRec.TAU))+', '+ QuotedStr(FloatToStr(aRec.Lat))+', '
                      + QuotedStr(FloatToStr(aRec.Lon))+', '+ QuotedStr(IntToStr(aRec.Wind))+', '
                      + QuotedStr(IntToStr(aRec.Pressure))
                      +')');
        DM.sqliteQuery.Execute;
      end;
    Finally
      FileList.Free;
      aModel.Free;
      aList.Free;
    End;
end;

[AlexP]

Hello,

To increase data insert speed, you can use a parametrized query, i.e. assign the following query before the loop:

Code: Select all

DM.sqliteQuery.SQL.Text := 'INSERT OR REPLACE INTO '+QuotedStr('file_'+inStormID) +' VALUES (:PosDateTimeStr, :MType, .....)';

Then in the loop just assign parameter values and call the Execute method. In addition, you should use the transaction mechanism, perform insert operations in transactions, for example, insert 1000 rows in a transaction.
You can also use the UniLoader component: https://www.devart.com/unidac/docs/?dev ... loader.htm .

[bryan.ray]

Thanks Alex.

Processing bulk transaction (500) at a time significantly improved performance.
I will work on using parameters also.
Regards,
Bryan

[AlexP]

The number of operations executed in one transaction should be found experimentally, since quite many factors affect performance.