С этого поста я хочу начать серию статей о настройке и эффективном использовании базы данных SQLite. Поскольку SQLite является файловой базой данных и прекрасно обходится без сервера. Программа, которая работает с файлом базы, сама настраивает свойства подключения и может их изменять при открытом соединении. Это значит, что необязательно задавать все нужные опции при подключении.
В этой статье мы разберемся какие параметры влияют на скорость работы SQLite. В этом мне поможет моя библиотека dbFacade, в которой, с версии 0.5.7, появилась возможность задавать некоторые дополнительные настройки подключения.
Обычное подключение в большинстве случаев выглядит так:
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-команды по типу выполняемых действий делятся на следующие:
Это все касается строки подключения. Во время работы программа может сама задать настройки, выполнив простой 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-команды:
Для теста мы будем выполнять INSERT для 100000 записей варьируя настройки. В таблицу будет вставляться строка с тремя колонками. Первая колонка содержит число, две вторых текст (в исходниках теста можно посмотреть каких размеров текст).
В тестовом проекте записано сразу два теста. Первый без использования транзакций, второй с их использованием.
Первый тест будет работать только с 2000 записями (т.к. он и так будет долгим). После каждого теста будет происходить удаление всех записей и выполняться VACUUM.
Итак первый исходный тест выполняется с настройками по умолчанию. На скриншоте видны эти настройки…
… и результаты. Особенно плачевный первый =)
Как видите, использование транзакций значительно повышает скорость работы. Поэтому в первом тесте добавляем только 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(); |
Далее веселее, смотрите настройки на картинках ну и результаты. Делаем вывод, что транзакции решают. Удачного кодинга.