Ввиду некоторых особенностей SQLite, процедура записи данных в базу (INSERT) является довольно длительной по времени. Но эту задачу можно решить, если следовать определенным рекомендациям.
Исходный код демо-проекта доступен на GitHub.
Описание этой проблемы можно найти в первоисточнике. Текст оригинала выглядит так.
INSERT is really slow – I can only do few dozen INSERTs per second
Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.
Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..
By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN…COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.
Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.
Вольный перевод этой заметки следующий.
INSERT очень медленный – Я могу сделать только несколько десятков INSERT’ов в секунду!
На самом деле, SQLite может легко сделать 50000 или более INSERT’ов в секунду на среднем десктопе. Но, при этом, количество транзакций за это же время будет только несколько десятков. Скорость транзакции ограничена скоростью вращения шпинделя жесткого диска. Транзакция, как правило, требует два полных оборота диска, что на скорости 7200 об/мин даст порядка 60 транзакций.
Скорость транзакций ограничена скоростью HDD, потому что SQLite ждет, пока данные реально безопасно запишутся на диск, и только после этого, транзакция будет считаться завершенной. Таким образом, если внезапно пропадет питание или разрушится ОС, ваши данные будут в безопасности. Более подробно этот вопрос описан в документе по атомарным коммитам в SQLite.
По умолчанию, каждый INSERT обрабатывается в рамках собственной транзакции. Но если вы поместите несколько INSERT’ов в окружение BEGIN…COMMIT, то все эти INSERT’ы будут сгруппированы в одну транзакцию. Время на транзакцию будет амортизировано количеством INSERT’ов внутри неё, и таким образом, время на одну вставку значительно снизится.
Другой способ – это запуск с флагом PRAGMA synchronous=OFF. Эта команда говорит SQLite не ожидать каждый раз записи на диск. Это создаст ощущение, что операция записи ускорилась. Но при потере питания в середине транзакции, ваш файл базы данных может быть поврежден.
Теперь попробует применить данные рекомендации на практике. Ограничимся реализацией первого способа.
Для исследования данного вопроса создадим небольшую программу, внешний вид которой представлен ниже.
В поле “Time of experiment” необходимо ввести время эксперимента в секундах, в “Count INSERTs in transaction” – количество INSERT’ов внутри одной транзакции. Для запуска эксперимента нужно нажать START. Эксперимент завершится по прошествии времени указанном в поле “Time of experiment” или при нажатии на кнопку STOP. После завершения по таймеру, в нижней части программы будет выведена информация о количестве INSTERT’ов в секунду (“INSERTs per second”) и общем количестве INSERT’ов за эксперимент.
Теперь взглянем под капот нашей программы.
Предварительно обеспечим поддержку SQLite, как это сделать подробно написано здесь. Также добавим дополнительные пространства имен для работы с SQLite, потоками и вводом-выводом.
Из всего кода программы интерес представляет функция writeDataToDb(), которая выполнятся в отдельном потоке, запускаемом при нажатии на кнопку START.
private void writeDataToDb() { String query = "INSERT INTO Test ('message') values ('Hello message')"; while (!stopThread) { if (countOfInsertsInTrns == 1) { m_sqlCmd = new SQLiteCommand(query, m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery(); insertsCounter++; } else { m_sqlCmd = new SQLiteCommand("begin", m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery(); //---INSIDE LOOP for (int i = 0; i < countOfInsertsInTrns; i++) { m_sqlCmd = new SQLiteCommand(query, m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery(); insertsCounter++; } //---END LOOP m_sqlCmd = new SQLiteCommand("end", m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery(); } } }
В приведенной выше функции, вначале формируется запрос и сохраняется в переменной query. Если количество INSERT’ов в транзакции 1, то в таком случает нет необходимости помещать их в дополнительное окружение. Если это количество больше единицы, то для того, чтобы отправить их в рамках одной транзакции нам необходимо указать начало и конец.
m_sqlCmd = new SQLiteCommand("begin", m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery(); //---INSIDE LOOP for (int i = 0; i < countOfInsertsInTrns; i++) { m_sqlCmd = new SQLiteCommand(query, m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery(); insertsCounter++; } //---END LOOP m_sqlCmd = new SQLiteCommand("end", m_sqlCmd.Connection); m_sqlCmd.ExecuteNonQuery();
У меня получились следующие результаты (время эксперимента во всех случаях 5 секунд).
| Условие эксперимента | Количество INSERT’ов в секунду |
| Количество INSERT’ов в транзакции: 1 | 6 |
| Количество INSERT’ов в транзакции: 10 | 84 |
| Количество INSERT’ов в транзакции: 100 | 824 |
| Количество INSERT’ов в транзакции: 1000 | 7224 |
| Количество INSERT’ов в транзакции: 10000 | 40906 |
Как видим из таблицы, можно довольно неплохо выиграть в производительности, при этом свести к минимуму риски потерять данные. И не забываем про бэкапы!
Спасибо за внимание!
