在Delphi FireDAC中加载数组DML的最快方法
我正在使用Delphi XE8和FireDAC加载大型SQLite数据库。 为此，我使用Array DML执行技术一次高效地插入大量记录，如下所示：
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
{ the code to determin IndiKey,... is not shown, but goes here }
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;
inc(NumParams);
until done;
FDQueryAddINDI.Params.ArraySize := NumParams; { Reset to actual number }
FDQueryAddINDI.Execute(LogoAppForm.FDQueryAddINDI.Params.ArraySize);
数据实际加载到SQLite数据库的速度非常快，而且我的速度没有问题。
减慢我的速度是在重复循环中将所有值分配给参数的时间量。
Params内置于FireDAC中，是TCollection。 我没有访问源代码的权限，所以我不能看到AsStrings和AsIntegers方法实际上在做什么。
为每个插入的每个参数分配每个值在我看来并不是一种加载此TCollection的非常有效的方式。 有没有更快的方式来加载？ 我想可能是一种方法来加载一整套参数，例如（IndiKey，HasData，... FirstAncestralLoop）都是一个整体。 或者可以尽可能高效地加载我自己的TCollection，然后使用TCollection的Assign方法将我的TCollection复制到FireDAC的TCollection中。
所以我的问题是加载FireDAC需要的这个TCollection参数的最快方法是什么？
更新：我为Arnaud提供了一些时间。
如使用SQLite和FireDAC中所述（请参阅其Array DML部分）：
从v 3.7.11开始，SQLite支持带有多个VALUES的INSERT命令。 当Params.BindMode = pbByNumber时，FireDAC使用此功能来实现Array DML。 否则，FireDAC会模拟Array DML。
我测试了插入33,790条记录，改变了数组大小（每次执行加载的记录数），并使用pbByName（用于仿真）和pbByNumber（使用多个插入值）对加载时间进行了定时。
这是时机：
Arraysize: 1, Executes: 33,790, Timing: 1530 ms (pbByName), 1449 ms (pbByNumber)
Arraysize: 10, Executes: 3,379, Timing: 1034 ms (pbByName), 782 ms (pbByNumber)
Arraysize: 100, Executes: 338, Timing: 946 ms (pbByName), 499 ms (pbByNumber)
Arraysize: 1000, Executes: 34, Timing: 890 ms (pbByName), 259 ms (pbByNumber)
Arraysize: 10000, Executes: 4, Timing: 849 ms (pbByName), 227 ms (pbByNumber)
Arraysize: 20000, Executes: 2, Timing: 594 ms (pbByName), 172 ms (pbByNumber)
Arraysize: 50000, Executes: 1, Timing: 94 ms (pbByName), 94 ms (pbByNumber)
现在关于这些时间的一个有趣的事情是，将这33,790条记录加载到TCollection中，每一次测试就要花费93毫秒的时间。 无论他们是一次增加1个还是每次增加10000个，填充Params TCollection的开销总是在那里。
为了进行比较，我使用了198,522个插入仅用于pbByNumber的较大测试：
Arraysize: 100, Executes: 1986, Timing: 2774 ms (pbByNumber)
Arraysize: 1000, Executes: 199, Timing: 1371 ms (pbByNumber)
Arraysize: 10000, Executes: 20, Timing: 1292 ms (pbByNumber)
Arraysize: 100000, Executes: 2, Timing: 894 ms (pbByNumber)
Arraysize: 1000000, Executes: 1, Timing: 506 ms (pbByNumber)
对于此测试的所有情况，加载Params TCollection的开销大约需要503 ms。
所以TCollection的加载似乎在每秒大约400,000条记录。 这是插入时间的一个重要部分，一旦我开始使用数百万的大型数据库工作，这段时间对我的程序用户来说将非常明显。
我想改善这一点，但我还没有找到加速Params加载的方法。
更新2：通过将我的所有代码放在StartTransaction和Commit之间，我能够获得大约10％的时间改进，因此所有的块都会一次处理。
但我仍然在寻找某种方法来更快地加载Params的TCollection。
另一个想法是：
如果可能的话，可能运行得很好，速度可能高达16倍，就像ParamValues方法一样。 这一次赋值多个参数，并且具有直接提供变量数组的额外优点，并且避免了需要赋值的情况。
它会这样工作：
FDQueryAddINDI.Params.ParamValues['indikey;hasdata;gedcomnames;sex;birthdate;died;deathdate;changed;eventlinesneedprocessing;eventlines;famc;fams;linkinfo;todo;nextreportindi;firstancestralloop']
:= VarArrayOf([Indikey, 0, ' ', ' ', ' ', 0, ' ', ' ', 1, ' ', -1, -1, -1, -1, -1, -1]);
但是，ParamValues只会分配给第一组参数，即NumIndiParms = 0。
有没有办法为循环中的每个索引执行此操作，即NumIndiParms的每个实例？
赏金：我真的想加快Params的加载速度。 我现在为某人提供了一笔赏金，帮助我找到一种方法来加速加载FireDAC中实现的Params数组TCollection。
听起来有点像我过早的优化。 恕我直言，一个分析器将显示repeat .... until done循环比Execute调用本身所需的时间少得多。 赋值一个integer几乎是即时的，就像分配一个string ，这要归功于Delphi string类型的CopyOnWrite范例，它通过引用来复制文本。
请注意，在实践中，SQLite3中没有数组DML特性。 FireDac通过创建多个插入（即执行）来模拟数组DML
insert into indi values (?,?,?,....),(?,?,?,....),(?,?,?,....),....,(?,?,?,....);
AFAIK这是使用SQLite3插入数据的最快方式。 至少在即将到来的OTA功能可用之前。
此外，请确保您在多个事务中嵌套插入，并且一次设置的参数数量不会太高。 从我的测试中，如果要插入很多行，还应该创建多个事务。 维护单个事务会降低流程速度。 每次交易10000行是一个很好的数字，来自实验。
顺便说一下，我们的ORM能够独立完成所有这些低级管道工作，具体取决于它运行的后端引擎。
更新 ：听起来好像FireDac参数可能会成为您的瓶颈。 因此，您应该绕过FireDAC，并直接将您的TCollection内容与SQlite3引擎绑定。 试试例如我们的SynSQLite3.pas单元。 请记住使用多重插入（ (?,?,?,....),(?,?,?,....),.... ）准备INSERT语句，然后直接绑定您的值。 BTW DB.pas可能是一个真正的瓶颈，这就是为什么我们的整个ORM绕过这个层（但可能在需要时使用它）。
Update2 ：既然你问了它，这是一个使用mORMot的版本。
首先你定义你的记录：
type
TSQLIndy = class(TSQLRecord)
...
published
property indikey: string read findikey write findikey;
property hasdata: boolean read fhasdata write fhasdata;
property gedcomnames: string read fgedcomnames write fgedcomnames;
property sex: string read fsex write fsex;
property birthdate: string read fbirthdate write fbirthdate;
property died: boolean read fdied write fdied;
...
end;
然后通过ORM运行插入：
db := TSQLRestServerDB.CreateWithOwnModel([TSQLIndy],'test.db3');
db.CreateMissingTables; // will CREATE TABLE if not existing
batch := TSQLRestBatch.Create(db,TSQLIndy,10000);
try
indy := TSQLIndy.Create;
try
for i := 1 to COUNT do begin
indy.indikey := IntToString(i);
indy.hasdata := i and 1=0;
...
batch.Add(indy,true);
end;
finally
indy.Free;
end;
db.BatchSend(batch);
完整的源代码可在paste.ee上在线获得。
以下是1,000,000条记录的时间：
Prepared 1000000 rows in 874.54ms
Inserted 1000000 rows in 5.79s
如果我计算得好，插入每秒超过170,000行。 在这里，ORM不是开销，这是一个优势。 所有多重INSERT工作，事务（每10000行），编组将由框架完成。 TSQLRestBatch会将所有内容作为JSON存储在内存中，然后一次计算SQL。 我很好奇FireDAC如何直接进行比较。 如果需要，你可以切换到其他数据库 - 另一个RDBMS（MySQL，Oracle，MSSQL，FireBird）甚至MongoDB。 只需添加一条新线。
希望能帮助到你！
我可以找到的最佳改进是用值调用替换AsString和AsInteger调用。 这可以防止为每个项目分配数据类型（字符串或整数）并节省大约10％的开销。
因此，小测试中的93毫秒降至83毫秒。 大测试中的503毫秒降至456毫秒。
FDQueryAddINDI.Params[0].Values[NumParams] := IndiKey;
FDQueryAddINDI.Params[1].Values[NumParams] := HasData;
FDQueryAddINDI.Params[2].Values[NumParams] := GedcomNames;
FDQueryAddINDI.Params[3].Values[NumParams] := Sex;
FDQueryAddINDI.Params[4].Values[NumParams] := Birthdate;
FDQueryAddINDI.Params[5].Values[NumParams] := Died;
FDQueryAddINDI.Params[6].Values[NumParams] := Deathdate;
FDQueryAddINDI.Params[7].Values[NumParams] := Changed;
FDQueryAddINDI.Params[8].Values[NumParams] := EventLinesNeedProcessing;
FDQueryAddINDI.Params[9].Values[NumParams] := EventLines;
FDQueryAddINDI.Params[10].Values[NumParams] := FamC;
FDQueryAddINDI.Params[11].Values[NumParams] := FamS;
FDQueryAddINDI.Params[12].Values[NumParams] := Linkinfo;
FDQueryAddINDI.Params[13].Values[NumParams] := ToDo;
FDQueryAddINDI.Params[14].Values[NumParams] := NextReportIndi;
FDQueryAddINDI.Params[15].Values[NumParams] := FirstAncestralLoop;
打开文件时，可以选择初始设置类型。 最大字符串长度也可以设置。 这对时间没有任何影响，并且设置长度不会减少使用的内存。 类型和长度以这种方式设置：
FDQueryAddINDI.Params[0].DataType := ftString;
FDQueryAddINDI.Params[1].DataType := ftInteger;
FDQueryAddINDI.Params[2].DataType := ftString;
FDQueryAddINDI.Params[3].DataType := ftString;
FDQueryAddINDI.Params[4].DataType := ftString;
FDQueryAddINDI.Params[5].DataType := ftInteger;
FDQueryAddINDI.Params[6].DataType := ftString;
FDQueryAddINDI.Params[7].DataType := ftString;
FDQueryAddINDI.Params[8].DataType := ftInteger;
FDQueryAddINDI.Params[9].DataType := ftString;
FDQueryAddINDI.Params[10].DataType := ftInteger;
FDQueryAddINDI.Params[11].DataType := ftInteger;
FDQueryAddINDI.Params[12].DataType := ftInteger;
FDQueryAddINDI.Params[13].DataType := ftInteger;
FDQueryAddINDI.Params[14].DataType := ftInteger;
FDQueryAddINDI.Params[15].DataType := ftInteger;
FDQueryAddINDI.Params[0].Size := 20;
FDQueryAddINDI.Params[2].Size := 1;
FDQueryAddINDI.Params[3].Size := 1;
FDQueryAddINDI.Params[4].Size := 1;
FDQueryAddINDI.Params[6].Size := 1;
FDQueryAddINDI.Params[7].Size := 1;
FDQueryAddINDI.Params[9].Size := 1;