I am using Delphi XE8 with FireDAC to load a large SQLite database. To do so, I'm using the Array DML Command to efficiently insert a large number of records at once, like this:
FDQueryAddINDI.SQL.Text := 'insert into indi values ('
+ ':indikey, :hasdata, :gedcomnames, :sex, :birthdate, :died, '
+ ':deathdate, :changed, :eventlinesneedprocessing, :eventlines, '
+ ':famc, :fams, :linkinfo, :todo, :nextreportindi, :firstancestralloop'
+ ')';
FDQueryAddINDI.Params.Bindmode := pbByNumber; {more efficient than by name }
FDQueryAddINDI.Params.ArraySize := MaxParams; { large enough to load all of them }
NumParams := 0;
repeat
{ processing that determines the parameter values goes here }
inc(NumParams);
FDQueryAddINDI.Params[0].AsStrings[NumParams] := IndiKey;
FDQueryAddINDI.Params[1].AsIntegers[NumParams] := HasData;
FDQueryAddINDI.Params[2].AsStrings[NumParams] := GedcomNames;
FDQueryAddINDI.Params[3].AsStrings[NumParams] := Sex;
FDQueryAddINDI.Params[4].AsStrings[NumParams] := Birthdate;
FDQueryAddINDI.Params[5].AsIntegers[NumParams] := Died;
FDQueryAddINDI.Params[6].AsStrings[NumParams] := Deathdate;
FDQueryAddINDI.Params[7].AsStrings[NumParams] := Changed;
FDQueryAddINDI.Params[8].AsIntegers[NumParams] := EventLinesNeedProcessing;
FDQueryAddINDI.Params[9].AsStrings[NumParams] := EventLines;
FDQueryAddINDI.Params[10].AsIntegers[NumParams] := FamC;
FDQueryAddINDI.Params[11].AsIntegers[NumParams] := FamS;
FDQueryAddINDI.Params[12].AsIntegers[NumParams] := Linkinfo;
FDQueryAddINDI.Params[13].AsIntegers[NumParams] := ToDo;
FDQueryAddINDI.Params[14].AsIntegers[NumParams] := NextReportIndi;
FDQueryAddINDI.Params[15].AsIntegers[NumParams] := FirstAncestralLoop;
until done;
FDQueryAddINDI.Params.ArraySize := NumParams; { Reset to actual number }
FDQueryAddINDI.Execute(LogoAppForm.FDQueryAddINDI.Params.ArraySize);
The actual loading of the data into the SQLite database is very fast, and I have no problem with the speed of that.
What is slowing me down is the amount of time being taken in the repeat loop to assign all the values to the parameters.
The Params are built into FireDAC and are a TCollection. I do not have access to the source code, so I can't see what the AsStrings and AsIntegers methods are actually doing.
Assigning each value to each parameter for each insert does not appear to me to be a very efficient way to load this TCollection. Is there a faster way to load this? I'm thinking maybe a way to load a whole set of parameters at once, e.g. (IndiKey, HasData, ... FirstAncestralLoop) all as one. Or maybe to load my own TCollection as efficiently as I can, and then use the TCollection's Assign method to copy my TCollection into the the FireDAC's TCollection.
So my question is what would be the fastest way to load this TCollection of parameters that FireDAC requires?
Aucun commentaire:
Enregistrer un commentaire