SQLite.NET — использование PRAGMA

Содержание

Основные сведения о командах PRAGMA в SQLite
Использование PRAGMA в SQLite.NET

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

Основные сведения о командах PRAGMA в SQLite

Несмотря на то, что команды PRAGMA выполняются также, как и любые другие команды к БД SQLite, выполнение этих команда имеет ряд особенностей, а именно:

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

Выполнение PRAGMA в SQLite выглядит следующим образом:

PRAGMA pragma_name;
PRAGMA pragma_name = pragma_value;
PRAGMA pragma_name(pragma_value)

здесь pragma_name — это название команды, а pragma_value — значение, которое мы пробуем присвоить, если PRAGMA допускает запись значений. Третий вариант вызова PRAGMA используется, например, в том случае, когда нам необходимо получить какую-либо информацию о базе данных или её таблицах.

Использование PRAGMA в SQLite.NET

В библиотеке SQLite.NET нет каких-либо специальных методов, которые бы выполняли ту или иную команду PRAGMA, однако, это и не требуется — весь необходимый инструментарий для выполнения любых команда к SQLite имеется в библиотеке. Далее рассмотрим наиболее часто используемые PRAGMA и их влияние на работу БД.

Как узнать список PRAGMA, которые поддерживаются SQLite?

Как бы это не прозвучало, но, чтобы узнать список всех доступных PRAGMA — надо выполнить PRAGMA. Выполнение команды

PRAGMA pragma_list

вернет таблицу, состоящую из одного столбца с именем name, в которой будут перечислены все PRAGMA, поддерживаемые используемым ядром SQLite. Чтобы выполнить такую команду в SQLite.NET мы можем воспользоваться методом Query, например, так:

using SQLite;
namespace ConsoleApp1
{
    
    public class Pragma
    {
        [Column("name")]
        public string Name { get; set; }
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            SQLiteConnection connection = new SQLiteConnection("opendata.sqlite", true);
            var pragmas = connection.Query<Pragma>("PRAGMA pragma_list");

            //перечисляем все PRAGMA
            int count = 0;
            foreach (var pragma in pragmas)
                Console.WriteLine($"{++count} - {pragma.Name}");
        }
    }
}

в итоге, получим список из, порядка, 65-66 имен команд PRAGMA, доступных для использования.

Как узнать установленное значение PRAGMA в SQLite?

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

SQLiteConnection connection = new SQLiteConnection("opendata.sqlite", true);

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

SQLiteConnection connection = new SQLiteConnection("opendata.sqlite", true);
var data = connection.ExecuteScalar<int>("PRAGMA cache_size");
Console.WriteLine($"cache_size = {data}");
data = connection.ExecuteScalar<int>("PRAGMA synchronous");
Console.WriteLine($"synchronous = {data}");

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

cache_size = -2000
synchronous = 2

Отрицательное значение cache_size означает то, что количество страниц кэша настраивается таким образом, чтобы использовать приблизительно abs(N*1024) байт памяти.

Что касается значения synchronous = 2, то, следуя документации, означает, что применяется полная синхронизация, которая гарантирует, что при сбое операционной системы или даже при потере электропитания, база данных не будет повреждена. Если для вас на первом месте стоит безопасность использования БД, то можно оставить эту настройку как есть, но стоит учитывать, что полная синхронизация замедляет производительность.

Как установить настройку с помощью PRAGMA в SQLite?

Как было сказано выше, PRAGMA может по разному воздействовать на настройки работы SQLite. Условно, все PRAGMA, принимающие какие-либо значения, можно разделить на три большие группы:

действующие постоянно, даже в том случае, если соединение с БД было разорвано
действующие в течение жизни соединения. Такие PRAGMA сбрасываются на значение по умолчанию после закрытия соединения.
выполняющие однократно какие-либо действия с БД (оптимизацию, проверку индексов, сжатие БД и т.д.)

Например, к постоянно действующим настройкам можно отнести следующие PRAGMA:

application_id
auto_vacuum
journal_mode

К настройкам конкретного соединения можно отнести:

synchronous
temp_store
busy_timeout

Чтобы установить какое-либо значение настройки с помощью PRAGMA, необходимо передать значение настройки либо в виде числового значения, либо в виде строкового эквивалента. Например, если для вас критичным является размер файла базы данных, то для автоматического сжатия БД можно вызвать PRAGMA auto_vacuum следующим образом:

PRAGMA auto_vacuum = 0 | NONE | 1 | FULL | 2 | INCREMENTAL;

Например, установим настройку в значение FULL при котором база будет сжиматься после каждой фиксации изменений в БД:

SQLiteConnection connection = new SQLiteConnection("opendata.sqlite", true);
connection.Execute("PRAGMA auto_vacuum = FULL"); //установили настройку
connection.Execute("VACUUM"); //вызвали команду для сжатия БД, чтобы БД перестроилась и настройка вступила в силу

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

Эту же команду мы могли выполнить, используя целочисленное значение:

connection.Execute("PRAGMA auto_vacuum = 1"); //установили настройку в значение FULL

Оптимизация работы SQLite с использованием PRAGMA и методов SQLite.NET

Сама библиотека SQLite.NET активно использует в своей работе различные PRAGMA, например, при получении метаданных какой-ибо таблицы используется прагма table_info. Что же касается готовых методов библиотеки для управления SQLite, то здесь стоит обратить внимание на метод EnableWriteAheadLogging(). Этот метод вызывает команду

PRAGMA journal_mode=WAL

Прагма journal_mode устанавливает режим работы журнала базы данных и этот режим может напрямую влиять на производительность работы вашей БД. Так, выполнение метода EnableWriteAheadLogging SQLite.NET устанавливает постоянное значение этой настройки в режим ведения журнала WAL. По данным разработчиков и SQLite и SQLite.NET в таком режиме ведения журнала обеспечивается большая производительность работы базы, а также улучшается работа с базой данных при использовании параллельных операций.

При этом, если изучить документацию SQLite, то можно увидеть, что в режиме WAL рекомендовано использовать режим синхронизации как synchronous=NORMAL вместо установленного по умолчанию значения FULL. Поэтому, чтобы ещё больше повысить производительность БД можно поступить следующим образом — после того, как установлен режим ведения журнала WAL и создании очередного подключения вызвать команду

PRAGMA synchronous=NORMAL

Чтобы продемонстрировать влияние этих двух настроек на работу БД я взял реальную базу SQLite в которую последовательно пишутся данные в три таблицы: первая таблица содержит около 400 000 записей, вторая — около 110 000 и третья — более 1 500 000 записей. Вот какие данные были получены при разных сочетаниях прагмы journal_mode=WAL и synchronous:

synchronous = FULL — 00:23:56

synchronous = NORMAL— 00:21:58

synchronous = OFF— 00:02:56

Как можно видеть по результатам (хоть они и достаточно ориентировочные), при использовании синхронизации в режиме NORMAL имеется крайне небольшой прирост производительности, буквально в пару минут. Больше всего производительность вырастает при отключенной синхронизации, однако, здесь стоит отметить, что в таком режиме есть возможность повредить БД в случае каких-либо сбоев в операционной системе или электропитании. Поэтому, такой режим лучше всего использовать там где сохранность данных не является высшим приоритетом.