Ещё один способ ускорить запись данных в БД SQLite

Вот уже два с лишним года я использую для работы с SQLite компоненты от Devart. Мне они нравятся — простые в использовании, удобные, полностью удовлетворяют моим запросам в плане функциональности и т.д. Но речь сегодня не об этих замечательных компонентах доступа к SQLite (хотя я и буду их использовать в статье), а о самой СУБД. Довольно часто приходилось сталкиваться с мнением о том, что SQLite «тормозит». Да и сам я в начале своей работы с этой замечательной СУБД так считал, пока не нашел один из способов ускорения записи данных в БД SQLite о котором я рассказывал здесь и в статье про DBExpress — использование транзакций.

Использование транзакций в разы увеличивает скорость записи данных в БД SQLite, но и этого прироста скорости бывает  иногда недостаточно.  И сегодня я рассмотрю ещё один способ ускорить запись данных в БД SQLite, используя настройки самой СУБД. 

Прежде всего обращаю ваше снимание на то, что БД SQLite имеет по умолчанию настройки ориентированные в первую очередь на безопасность. Поэтому, прежде, чем изменить те или иные настройки базы данных вам следует для себя определить — нужна ли вам очень быстрая скорость в ущерб безопасности?

Итак, снова воспроизведем тот пример, который уже был в этом блоге:

const
  cTableDDL = 'CREATE TABLE hardware (h_id INTEGER PRIMARY KEY AUTOINCREMENT, h_name TEXT, h_price DOUBLE, h_charact TEXT)';
 
procedure TForm5.LiteConnection1AfterConnect(Sender: TObject);
begin
  LiteConnection1.ExecSQL(cTableDDL);
end;
 
procedure TForm5.LiteConnection1BeforeConnect(Sender: TObject);
begin
  LiteConnection1.Database:=ExtractFilePath(Application.ExeName)+'test.sqlite';
end;
 
procedure TForm5.Button2Click(Sender: TObject);
var iCounterPerSec: TLargeInteger;
    T1, T2: TLargeInteger;
    i:integer;
begin
    QueryPerformanceFrequency(iCounterPerSec);
    QueryPerformanceCounter(T1);
 
    for I := 0 to 999 do
      begin
        LiteConnection1.SQL.SQL.Text:=Format('INSERT INTO hardware VALUES(%d, "test", %d, "test")',[i,i]);
        LiteConnection1.SQL.Execute;
      end;
    QueryPerformanceCounter(T2);
end;

Здесь мы пишем в базу данный 1000 элементов без использования транзакций. Как мы знаем, это действие займет огромное количество времени. Если быть точным, то у меня счётчик показал 140,8444 секунд.

Попробуем сократить время записи, изменяя настройки базы данных.

PRAGMA synchronous

Как было написано выше, по умолчанию база SQLite создается с настройками максимальной безопасности. В частности, флаг  synchronous установлен в значение FULL. Использование этого значения гарантирует вам, что все данные будут записаны в базу данных и авария или сбой питания не нарушат целостность базы данных. Однако за такую гарантию мы «платим» временем.

synchronous может принимать три значения:

• 0 | OFF — синхронизация БД полностью отключена. При таком режиме работы, в случае аварии, возможен выход из строя базы данных
• 1 | NORMAL — в этом случае SQLite синхронизирует данные только в самых критичных ситуациях и синхронизация запускается намного реже, чем при режиме FULL.
• 2 | FULL — максимальный уровень безопасности.

Таким образом, в зависимости от ваших требований к безопасности работы БД, можно «поиграть» с флагом synchronous и ускорить время записи данных. 

Как можно менять значения PRAGMA при использовании LiteDAC? Собственно, как и при использовании любых других компонентов для работы с SQLite. Например, так:

LiteConnection1.ExecSQL('PRAGMA synchronous = OFF';

Теперь посмотрим на сколько ускорится запись всё тех же 1000 записей при изменении значения флага synchronous.

Как можно увидеть из представленной выше таблицы, отключение синхронизации увеличивает скорость записи данных в десятки раз даже без использования транзакций. Ну, а если использовать транзакции, то получим вот такие скорости:

Как видите, использование только одной настройки SQLite способно ускорить работу вашей базы данных SQLite в десятки раз. Однако при этом стоит помнить о безопасности. Выбирайте оптимальное для себя соотношение «скорость — безопасность».

PRAGMA temp_store

Еще один способ ускорения работы с БД SQLite — указать место хранения временных таблиц и индексов.

temp_store может принимать следующие значения:

• 0 | DEFAULT
• 1 | FILE
• 2 | MEMORY

Подробно прочитать о значениях этого флага можно на официальном сайте. Скажу только, что использовать этот параметр стоит с осторожностью, т.к. его изменение, например, в Android может привести к тому, что ваша БД будет «поломана».

Вот какие данные были получены при использовании разных вариантов этого флага:

И, в заключении, посмотрим, что получится, если выставить для БД следующие значения флагов:

• synchronous = OFF
• temp_store = MEMORY

и, используя транзакции.

Как видите, скорость изменилась крайне незначительно при использовании транзакций. Так что этот параметр можете менять в самую последнюю очередь.

Вот, собственно, и ещё один способ ускорить запись данных в БД SQLite. Воспользоваться им или нет — дело каждого, но, ещё раз подчеркну — выбираете правильное соотношение «скорость — безопасность». Для себя я определил, что для моей работы достаточно использовать флаг synchronous в значении NORMAL.

Удачных разработок с SQLite и Devart.

Книжная полка

	Автор: Дмитрий Осипов Название:Базы данных и Delphi. Теория и практика Описание Книга основана на материалах лекций и практических занятий, разработанных автором, и объединяет теоретические основы и практические аспекты разработки реляционных баз данных.	Купить на ЛитРес	383 руб.
	Автор: Анатолий Хомоненко, Владимир Гофман Название:Работа с базами данных в Delphi Описание: Рассматривается использование средств Delphi для разработки приложений баз данных. Даются понятия баз данных, характеризуются элементы и описываются этапы проектирования реляционных баз данных, изложена технология разработки информационных систем, освещаются приемы работы с данными, создание таблиц и приложений баз данных, подготовка отчетов.	Купить на ЛитРес	151 руб.