Часто по работе использую библиотеку 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;
Ниже привожу результаты замеров вставки данных.
Сначала загружаем данные без транзакций.
Как видим у обычной вставки результаты довольно таки печальные по сравнению с новым методом.
Теперь включаем транзакции и смотрим результат.
Сильно сократилось время загрузки у первого метода, и чуть-чуть улучшилось у второго. Однако по сравнению с первым методом результат значительно лучше.
Кстати можно не выполнять сразу всю вставку
Q1.Execute(Q1.Params.ValueCount);
а можно делать это по частям
Q1.Execute(500,0); // с нулевой строки грузим 500 строк Q1.Execute(500,500);// с 500 строки грузим 500 строк
Думаю так можно еще ускорить добавление данных.
Приобрести Devart LiteDac
Хороший пример по batch. Спасибо!
RSS лента комментариев этой записи