Часто по работе использую библиотеку Sqlite,  для хранения различных данных. Данных бывает большое количество и зачастую играет роль скорость обработки данных, поэтому приходится часто оптимизировать код программ для работы данных. Для работы с библиотекой использую компоненты от Devart LiteDac . Компоненты всем хороши и оправдывают свою цену. К тому же разработчики часто выпускают новые версии с усовершенствованиями.

Недавно вычитал в блоге у Влада (каюсь не знаю как по отчеству) статью про ускорение вставки данных. Очень интересная статья. И вспомнил о еще одном методе ускорения вставки данных, а так же их редактирования и удаления.

 

Применяется он именно у LiteDac. Этот метод называется Batch Operations (пакетные операции), суть его в том что за раз выполняется не одна команда sql,  сразу несколько пакетом.

Не силен в историческом прошлом работы с базами данных но вроде раньше не один компонент для Delphi так не работал, поправьте если ошибаюсь. 

 

Для тестирования метода я написал простенькое приложение.

 

Соответственно используются два метода вставки данных. Обычный и новый.

В качестве тестовых данных использовался один из рабочих файлов, тестовый файл csv с разделителем табуляция, который содержит 6 колонок и  41129 строк. Программа загружает файл и записывает его в тестовую базу Sqlite.

Для чистоты эксперимента файл базы данных находится на SSD накопителе.

Ниже приведен код обоих способов. 

Обычный способ

procedure TForm1.Button1Click(Sender: TObject);
// обычный метод
var
list, stroka: TStringList;
i: Integer;
PerSec: TLargeInteger;
schet1, schet2: TLargeInteger;
begin
LiteConnection1.ExecSQL('delete from usl');
list := TStringList.Create;
stroka := TStringList.Create;
stroka.Delimiter := #9;
stroka.StrictDelimiter := True;
list.LoadFromFile('c:\test\usl.csv');
q1.SQL.Clear;
q1.SQL.Add('insert into usl(nn1, nn2, pre, code, price, qnt) values(:n1, :n2,:pr,:cd, :pri, :q);');
QueryPerformanceFrequency(PerSec);
QueryPerformanceCounter(schet1);
if (UseTransaction.Checked) then
LiteConnection1.StartTransaction;
for i := 0 to list.Count - 1 do
begin
stroka.DelimitedText := list.Strings[i];
q1.ParamByName('n1').AsString := stroka.Strings[0];
q1.ParamByName('n2').AsString := stroka.Strings[1];
q1.ParamByName('pr').AsString := stroka.Strings[2];
q1.ParamByName('cd').AsString := stroka.Strings[3];
q1.ParamByName('pri').AsString := stroka.Strings[4];
q1.ParamByName('q').AsString := stroka.Strings[5];
q1.ExecSQL;
end;
QueryPerformanceCounter(schet2);
if (UseTransaction.Checked) then
LiteConnection1.Commit;
Label1.Caption := 'Время ' + FormatFloat('0.0000', (schet2 - schet1) / PerSec) + ' сек.';
list.Free;
stroka.Free;
end;

 Новый способ

procedure TForm1.Button2Click(Sender: TObject);
// новый способ
var
  list, stroka: TStringList;
  i: Integer;
  PerSec: TLargeInteger;
  schet1, schet2: TLargeInteger;
begin
 LiteConnection1.ExecSQL('delete from usl');
  list := TStringList.Create;
  stroka := TStringList.Create;
  stroka.Delimiter := #9;
  stroka.StrictDelimiter := True;
  list.LoadFromFile('c:\test\usl.csv');
  q1.SQL.Clear;
  q1.SQL.Add('insert into usl(nn1, nn2, pre, code, price, qnt) values(:n1, :n2,:pr,:cd, :pri, :q);');
  QueryPerformanceFrequency(PerSec);
  QueryPerformanceCounter(schet1);
  if (UseTransaction.Checked) then
    LiteConnection1.StartTransaction;
  Q1.Params.ValueCount := list.Count; // задаем количество строк в пакете
  for i := 0 to list.Count - 1 do
  begin
    stroka.DelimitedText := list.Strings[i];
    q1.Params[0][i].AsString := stroka.Strings[0];
    q1.Params[1][i].AsString := stroka.Strings[1];
    q1.Params[2][i].AsString := stroka.Strings[2];
    q1.Params[3][i].AsString := stroka.Strings[3];
    q1.Params[4][i].AsString := stroka.Strings[4];
    q1.Params[5][i].AsString := stroka.Strings[5];
  end;
  Q1.Execute(Q1.Params.ValueCount);// вставляем все строки из пакета
  QueryPerformanceCounter(schet2);
  if (UseTransaction.Checked) then
    LiteConnection1.Commit;
  Label2.Caption := 'Время ' + FormatFloat('0.0000', (schet2 - schet1) / PerSec) + ' сек.';
  list.Free;
  stroka.Free;
end;

 

Так же можно кусок заполнения данных записать так 

for i := 0 to list.Count - 1 do
  begin
    stroka.DelimitedText := list.Strings[i];
    q1.params.ParamByName('n1')[i].AsString := stroka.Strings[0];
    q1.params.ParamByName('n2')[i].AsString := stroka.Strings[1];
    q1.params.ParamByName('pr')[i].AsString := stroka.Strings[2];
    q1.params.ParamByName('cd')[i].AsString := stroka.Strings[3];
    q1.params.ParamByName('pri')[i].AsString := stroka.Strings[4];
    q1.params.ParamByName('q').[i]AsString := stroka.Strings[5];
  end;

Ниже привожу результаты замеров вставки данных.

Сначала загружаем данные без транзакций.

 litedac batch

Как видим у обычной вставки результаты довольно таки печальные по сравнению с новым методом.

Теперь включаем транзакции и смотрим результат.

litedac batch

Сильно сократилось время загрузки у первого метода, и чуть-чуть улучшилось у второго. Однако по сравнению с первым методом результат значительно лучше. 

Кстати можно не выполнять сразу всю вставку

Q1.Execute(Q1.Params.ValueCount);

 а можно делать это по частям 

Q1.Execute(500,0); // с нулевой строки грузим 500 строк
Q1.Execute(500,500);// с 500 строки грузим 500 строк

 Думаю так можно еще ускорить добавление данных.

Приобрести Devart LiteDac  

 

 

 
Комментарии   
0 #2 Евгений 23.03.2017 19:04
Спасибо за статью. Очень полезно для больших данных. Буду признателен если выложите или пришлете исходник примера. Я уже давно не программировал с базами, подзабыл способы работы с ними
Цитировать
0 #1 Vlad55 21.11.2015 01:53
Приветствую!
Хороший пример по batch. Спасибо!
Цитировать
Добавить комментарий