• Программинг

Нужны источники бесперебойного питания?

Источники бесперебойного питания от дизельстор

Заставляем SQLite работать быстрее. PRAGMA-команды. Часть 1.

С этого поста я хочу начать серию статей о настройке и эффективном использовании базы данных SQLite. Поскольку SQLite является файловой базой данных и прекрасно обходится без сервера. Программа, которая работает с файлом базы, сама настраивает свойства подключения и может их изменять при открытом соединении. Это значит, что необязательно задавать все нужные опции при подключении.

В этой статье мы разберемся какие параметры влияют на скорость работы SQLite. В этом мне поможет моя библиотека dbFacade, в которой, с версии 0.5.7, появилась возможность задавать некоторые дополнительные настройки подключения.

Введение в PRAGMA

Обычное подключение в большинстве случаев выглядит так:

1 data source=file.db;New=True;UseUTF16Encoding=True

data source — указывает на путь к файлу.
New=True — значит, что если файл не будет найден, по умолчанию будет создан новый.
UseUTF16Encoding=True — позволяет использовать кодировку UTF16, как универсальную кодировку для большинства языков.

Для задания других параметров используются PRAGMA-команды. Их можно задавать в строке подключения или выполнять в виде запросов. Большинство параметров в качестве аргумента принимает True (которое равносильно 1 yes true on) и False (0 no false off). Некоторые параметры задают количество или размер, и поэтому принимают числовое значение. Третий вид опций принимают перечисления. Например, параметру synchronous можно задать OFF или NORMAL или FULL, что в цифром эквиваленте значит 0 или 1 или 2. Ну, и на конец, четвертый вид принимает строковое значение. Например, encoding=»UTF-8″ или encoding=»UTF-16be».

PRAGMA-команды по типу выполняемых действий делятся на следующие:

  • Получающие данные о схеме базы данных (информацию о таблицах, индексах, внешних ключах и т.д.).
  • Изменяющие различные параметры (кодировка, синхронизация и т.д.).
  • Изменяющие внутреннюю версию схем (schema_version и user_version)
  • Позволяющие управлять отладкой.

Это все касается строки подключения. Во время работы программа может сама задать настройки, выполнив простой nonQuery запрос типа:

1 PRAGMA auto_vacuum = 1

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

1 PRAGMA database_list;

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

Или такой пример:
Опция PRAGMA cache_size если в запросе описывается как:

1 PRAGMA cache_size;

То просто возвращается значение (размер кэша). В то же время запрос:

1 PRAGMA cache_size=3000;

Задает размер кэша. Но только на одну сессию (т.е. до закрытия подключения). В строке подключения можно использовать параметр Сache Size (размер в страницах).

Запомните важную вещь, что если вы меняете что-то через запрос, то это действует только на время одной сессии, т.е. подключения. Если закрыть подключение, то все настройки обнуляться. Поэтому, если хотите задать настройки и быть уверенным что они работают, то делайте это при создании объекта класса dbFacade. Все методы в классе dbFacade работают по следующей схеме — если соединение было открыто до выполнения метода, то оно не закрывается при выходе из метода. Но если оно было закрыто, оно открывается и закрывает в методе.

Тестирование

Теперь давайте перейдем к тестированию некоторых настроек. Возьмем задачу, когда требуется максимально увеличить скорость добавления новых строк в таблицу.

Я написал приложение, которое использует следующие PRAGMA-команды:

  • synchronous — задает тип синхронизации с базой данных. Возможные параметры: FULL — полная синхронизация (2), NORMAL — частичная (1), OFF — синхронизация отключена (0). При полной синхронизации при изменении данных SQLite каждый раз ждет, пока данные будут полностью записаны на диск. Соответственно скорость работы сильно зависит от скорости накопителя на котором находится база данных. В режиме NORMAL SQLite так же ждет, но только в наиболее критические моменты, поэтому скорость в этом режиме выше. И отключение синхронизации приводит к большому увеличению скорости, но при этом появляется риск, что база данных просто накроется медным тазом, если вдруг ваша программа вывалится на ошибку во время изменении данных, операционная система вывалится в синий экран или банально вам выключат свет. В режиме NORMAL вероятность появления ошибки в базе данных сильно сокращается. По умолчанию FULL.
  • count_changes — принимает True или False. При True INSERT, UPDATE и DELETE каждый раз возвращают количество обработанных строк (нельзя задать через строку подключения). Но в это число не входит количество строк обработанных триггером. В режиме по умолчанию False ничего не возвращается. Соответственно при True скорость работы ниже. По умолчанию false.
  • temp_store — задает в каком месте будут храниться временные данные (например, временные таблицы, ). Принимает значения DEFAULT, FILE и MEMORY. Таких мест всего два — в файле базы и в памяти. DEFAULT — место по умолчанию заданное при компиляции (чаще всего это файл), FILE — в файле, MEMORY — в памяти. Теоретически в памяти хранить лучше (нельзя задать через строку подключения).
  • cache_size — задает размер кэша в памяти, с которым работает SQLite. Задается в виде количества страниц. По умолчанию устанавливается 2000.
  • journal_mode — включает или отключает журналирвание. Если журналирование отключено, то выполнить rollback не получится. По умолчанию включено.

Для теста мы будем выполнять INSERT для 100000 записей варьируя настройки. В таблицу будет вставляться строка с тремя колонками. Первая колонка содержит число, две вторых текст (в исходниках теста можно посмотреть каких размеров текст).

В тестовом проекте записано сразу два теста. Первый без использования транзакций, второй с их использованием.
Первый тест будет работать только с 2000 записями (т.к. он и так будет долгим). После каждого теста будет происходить удаление всех записей и выполняться VACUUM.

Итак первый исходный тест выполняется с настройками по умолчанию. На скриншоте видны эти настройки…

SQLite

… и результаты. Особенно плачевный первый =)

Как видите, использование транзакций значительно повышает скорость работы. Поэтому в первом тесте добавляем только 2000 записей.

Вот код, который работает использует транзакции:

01 dbFacade db = new dbFacade();
02 ...
03 db.BeginTransaction();
04 for (int i = 0; i < countTest2; i++)
05 {
06     paramss.Clear();
07     paramss.Add("num", i, System.Data.DbType.Int32);
08     paramss.Add("name", colname, System.Data.DbType.String);
09     paramss.Add("name2", colname2, System.Data.DbType.String);
10     db.Insert("main", paramss);
11 }
12 db.CommitTransaction();
13 db.Close();

Далее веселее, смотрите настройки на картинках ну и результаты. Делаем вывод, что транзакции решают. Удачного кодинга.

PRAGMA

PRAGMA

PRAGMA

PRAGMA

PRAGMA