performance - 在 Delphi FireDAC 中加载数组 DML 的最快方法

Question

我正在使用 Delphi XE8 和 FireDAC 来加载大型 SQLite 数据库。为此，我使用数组 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 数据库中的速度非常快，我对此速度没有任何问题。

让我放慢速度的是在重复循环中将所有值分配给参数所花费的时间。

参数内置于 FireDAC 中，是一个 TCollection。我无权访问源代码，因此我看不到 AsStrings 和 AsIntegers 方法实际上在做什么。

在我看来，为每次插入的每个参数分配每个值并不是加载此 TCollection 的非常有效的方法。有没有更快的方法来加载这个？我在想也许有一种方法可以一次加载一整套参数，例如(IndiKey、HasData、...FirstAncestralLoop)全部为一体。或者也许尽可能高效地加载我自己的 TCollection，然后使用 TCollection 的分配方法将我的 TCollection 复制到 FireDAC 的 TCollection 中。

所以我的问题是加载 FireDAC 所需的参数 TCollection 的最快方法是什么？

<小时/>

更新:我添加了 Arnaud 的一些计时。

如 Using SQLite with FireDAC 中所述(请参阅其数组 DML 部分):

Starting with v 3.7.11, SQLite supports the INSERT command with multiple VALUES. FireDAC uses this feature to implement Array DML, when Params.BindMode = pbByNumber. Otherwise, FireDAC emulates 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 个，填充 TCollection 参数的开销始终存在。

为了进行比较，我做了一个更大的测试，仅针对 pbByNumber 插入了 198,522 次:

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)

对于此测试的所有情况，加载 TCollection of Params 的开销大约需要 503 毫秒。

因此，TCollection 的加载速度似乎约为每秒 400,000 条记录。这是插入时间的一个重要部分，一旦我开始使用数百万的大型数据库，这个增加的时间对于我的程序的用户来说将是相当明显的。

我想改进这一点，但我还没有找到加快参数加载速度的方法。

<小时/>

更新 2:通过将所有代码放在 StartTransaction 和 Commit 之间，我能够获得大约 10% 的时间改进，以便立即处理所有 block 。

但我仍在寻找某种方法来更快地加载 TCollection of Params。

<小时/>

另一个想法:

如果可能的话，什么可能工作得很好并且速度可以提高 16 倍，类似于 the ParamValues method 。这会一次分配多个参数，并具有直接提供变体数组的额外优势，并且避免了转换值的需要。

它会像这样工作:

    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 的每个实例)执行此操作？

<小时/>

赏金:我真的很想加快参数的加载速度。我现在向某人提供悬赏，帮助我找到一种加快 FireDAC 中实现的 Params 数组 TCollection 加载速度的方法。

Answer 1

对我来说听起来有点像过早优化。恕我直言，探查器会显示 repeat ....until done 循环比 Execute 调用本身花费的时间少得多。分配整数几乎是即时的，就像分配字符串一样，这要归功于 CopyOnWrite Delphi string 类型的范例，通过引用复制文本。

请注意，实际上，SQLite3 中没有数组 DML 功能。 FireDac 通过创建多个插入来模拟数组 DML，即执行

insert into indi values (?,?,?,....),(?,?,?,....),(?,?,?,....),....,(?,?,?,....);

据我所知，这是使用 SQLite3 插入数据最快的方法。至少直到 upcoming OTA feature可用。

还要确保将插入嵌套在多个事务中，并且一次设置的参数数量不要太多。根据我的测试，如果您有很多行要插入，您还应该创建多个事务。维护单个事务会减慢流程。根据实验，每个事务 10000 行是一个不错的数字。

顺便说一句，我们的 ORM 能够做到所有 this low-level plumbing取决于它运行的后端引擎。

更新:听起来 FireDac 参数在您的情况下可能是真正的瓶颈。因此，您应该绕过 FireDAC，并直接将您的 TCollection 内容与 SQlite3 引擎绑定(bind)。尝试例如our SynSQLite3.pas unit 。请记住使用多重插入来准备 INSERT 语句 ((?,?,?,....),(?,?,?,....),....) ，然后直接绑定(bind)您的值。 BTW DB.pas 可能是一个真正的瓶颈，这就是为什么我们的整个 ORM 绕过这一层(但如果需要的话可以使用它)。

更新2:既然您要求，这里是使用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);

完整的源代码是available online on 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。只需添加一个新行。

希望对你有帮助!