Самый быстрый способ загрузки массива DML в Delphi FireDAC

Question

Я использую 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 очень быстрая, и у меня нет проблем с такой скоростью.

Что меня тормозит, так это количество времени, затрачиваемое в цикле повторения, чтобы назначить все значения параметрам.

Параметры встроены в FireDAC и представляют собой TCollection. У меня нет доступа к исходному коду, поэтому я не вижу, что на самом деле делают методы AsStrings и AsIntegers.

Присвоение каждого значения каждому параметру для каждой вставки не представляется мне очень эффективным способом загрузки этой коллекции TCollection. Есть ли более быстрый способ загрузить это? Я думаю, может быть, способ загрузить весь набор параметров одновременно, например (IndiKey, HasData, ... FirstAncestralLoop) все как один. Или, возможно, загрузить свою собственную TCollection настолько эффективно, насколько это возможно, а затем использовать метод Assol TCollection, чтобы скопировать мою TCollection в TCollection FireDAC.

Поэтому мой вопрос: какой самый быстрый способ загрузить эту коллекцию параметров TCO, которая требуется FireDAC?

---

Обновление: я включаю некоторые времена для Арно.

Как указано в разделе Использование SQLite с FireDAC (см. Раздел "Массив DML"):

Начиная с версии 3.7.11, SQLite поддерживает команду INSERT с несколькими значениями. FireDAC использует эту функцию для реализации Array DML, когда Params.BindMode = pbByNumber. В противном случае FireDAC эмулирует массив 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 мс при каждом запуске теста. Неважно, добавляются ли они по одному за раз или 10000 за раз, эти накладные расходы на заполнение TCollection of Params всегда присутствуют.

Для сравнения я сделал больший тест с 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)

Для всех случаев этого теста накладные расходы на загрузку TCollection Params занимают около 503 мс.

Таким образом, загрузка TCollection составляет около 400 000 записей в секунду. Это значительная часть времени вставки, и как только я начну работать с большими базами данных миллионами, это добавленное время будет весьма заметно для пользователя моей программы.

Я хотел бы улучшить это, но я еще не нашел способ ускорить загрузку параметров.

---

Обновление 2: я смог добиться улучшения примерно в 10% времени, поместив весь свой код между StartTransaction и Commit, так что все блоки будут обрабатываться одновременно.

Но я все еще ищу способ загрузить TCollection of Params намного быстрее.

---

Еще одна идея:

То, что могло бы работать хорошо и могло бы быть в 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. Сейчас я предлагаю вознаграждение, чтобы кто-нибудь помог мне найти способ ускорить загрузку массива Params TCollection, реализованного в FireDAC.

Answer 1

Для меня это звучит как преждевременная оптимизация. ИМХО профайлер показал бы что repeat .... until done цикл занимает гораздо меньше времени, чем Execute позвони сам. Назначение integer почти мгновенно, так же, как назначение string, благодаря парадигме CopyOnWrite Delphi string тип, который копирует текст по ссылке.

Обратите внимание, что на практике в SQLite3 нет функции массива DML. FireDac эмулирует массив DML, создавая множественную вставку, т.е.

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

AFAIK это самый быстрый способ вставки данных с использованием SQLite3. По крайней мере до тех пор, пока не будет доступна новая функция OTA.

Также убедитесь, что вы вкладываете свою вставку в несколько транзакций, а количество одновременно заданных параметров не слишком велико. Из моих тестов вы также должны создать несколько транзакций, если у вас есть много строк для вставки. Поддержка одной транзакции замедляет процесс. 10000 строк на транзакцию - хорошее число из эксперимента.

Кстати, наш ORM может выполнять всю эту низкоуровневую сантехнику самостоятельно, в зависимости от внутреннего механизма, на котором он работает.

Обновление: Похоже, что параметры FireDac могут быть в вашем случае реальным узким местом. Поэтому вам следует обойти FireDAC и напрямую связать свой TCollection довольствоваться движком SQlite3. Попробуйте, например, наш модуль SynSQLite3.pas. Не забудьте подготовить оператор INSERT, используя множественную вставку ((?,?,?,....),(?,?,?,....),....), затем напрямую свяжите ваши ценности. КСТАТИ 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 выполняет в сравнении. И вы сможете при необходимости переключиться на другую базу данных - другую СУБД (MySQL, Oracle, MSSQL, FireBird) или даже MongoDB. Просто добавив новую строку.

Надеюсь, поможет!

Answer 2

Лучшее улучшение, которое я могу найти, - заменить вызовы AsString и AsInteger вызовами Values. Это предотвращает присвоение типа данных (строка или целое число) каждому элементу и экономит около 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;