Улучшить производительность SQLite INSERT-в-секунду?
Оптимизировать SQLite сложно. Массовая вставка приложения C может варьироваться от 85 вставок в секунду до более 96 000 вставок в секунду!
Фон: Мы используем SQLite как часть настольного приложения. У нас есть большие объемы данных конфигурации, хранящихся в файлах XML, которые анализируются и загружаются в базу данных SQLite для дальнейшей обработки при инициализации приложения. SQLite идеально подходит для этой ситуации, потому что он быстрый, не требует специальной настройки, а база данных хранится на диске в виде одного файла.
Обоснование: Сначала я был разочарован тем, что увидел. Оказывается, что производительность SQLite может значительно различаться (как для массовых вставок, так и для выборок) в зависимости от того, как настроена база данных и как вы используете API. Было непросто выяснить, какие были все варианты и методы, поэтому я подумал, что было бы разумно создать эту вики-статью сообщества, чтобы поделиться результатами с читателями Stack Overflow, чтобы избавить других от проблем, связанных с теми же исследованиями.
Эксперимент: Вместо того, чтобы просто говорить о советах по производительности в общем смысле (т.е."Используйте транзакцию!"), Я подумал, что лучше написать код на C ина самом деле измерить влияние различных вариантов. Мы собираемся начать с некоторых простых данных:
Текстовый файл с разделителями табуляции в 28 МБ (приблизительно 865 000 записей)полное расписание транзита по городу ТоронтоМой тестовый компьютер - 3,60 ГГц P4 под управлением Windows XP.Код скомпилирован сVisual C ++ 2005 как «Релиз» с «Полной оптимизацией» (/ Ox) и Favor Fast Code (/ Ot).Я использую SQLite "Amalgamation", скомпилированный непосредственно в мое тестовое приложение. Моя версия SQLite немного старше (3.6.7), но я подозреваю, что эти результаты будут сопоставимы с последним выпуском (пожалуйста, оставьте комментарий, если вы думаете иначе).Давайте напишем некоторый код!
Код: Простая программа на C, которая читает текстовый файл построчно, разбивает строку на значения и затем вставляет данные в базу данных SQLite. В этой «базовой» версии кода база данных создается, но мы не будем вставлять данные:
/*************************************************************
Baseline code to experiment with SQLite performance.
Input data is a 28 MB TAB-delimited text file of the
complete Toronto Transit System schedule/route info
from http://www.toronto.ca/open/datasets/ttc-routes/
**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"
#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256
int main(int argc, char **argv) {
sqlite3 * db;
sqlite3_stmt * stmt;
char * sErrMsg = 0;
char * tail = 0;
int nRetCode;
int n = 0;
clock_t cStartClock;
FILE * pFile;
char sInputBuf [BUFFER_SIZE] = "\0";
char * sRT = 0; /* Route */
char * sBR = 0; /* Branch */
char * sVR = 0; /* Version */
char * sST = 0; /* Stop Number */
char * sVI = 0; /* Vehicle */
char * sDT = 0; /* Date */
char * sTM = 0; /* Time */
char sSQL [BUFFER_SIZE] = "\0";
/*********************************************/
/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
/*********************************************/
/* Open input file and import into Database*/
cStartClock = clock();
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
/* ACTUAL INSERT WILL GO HERE */
n++;
}
fclose (pFile);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_close(db);
return 0;
}
Выполнение кода «как есть» на самом деле не выполняет никаких операций с базой данных, но даст нам представление о том, насколько быстрыми являются операции ввода-вывода и обработки строк необработанного C-файла.
Импортировано 864913 записей за 0,94 секунды
Большой! Мы можем сделать 920 000 вставок в секунду, при условии, что мы фактически не делаем никаких вставок :-)
«Худший случай-сценарий»Мы собираемся сгенерировать строку SQL, используя значения, считанные из файла, и вызвать эту операцию SQL, используя sqlite3_exec:
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);
Это будет медленно, потому что SQL будет скомпилирован в код VDBE для каждой вставки, и каждая вставка будет происходить в своей собственной транзакции.Как медленно?
Импортировано 864913 записей за 9933,61 секунд.
Хлоп! 2 часа 45 минут! Это только85 вставок в секунду.
Использование транзакцииПо умолчанию SQLite оценивает каждый оператор INSERT / UPDATE в пределах уникальной транзакции. Если выполняется большое количество вставок, желательно заключить вашу операцию в транзакцию:
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
...
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION",, NULL, NULL, &sErrMsg);
Импортировано 864913 записей за 38,03 секунды
Так-то лучше. Простое объединение всех наших вставок в одну транзакцию улучшило нашу производительность до23 000 вставок в секунду.
Использование подготовленного заявленияИспользование транзакции было огромным улучшением, но перекомпиляция оператора SQL для каждой вставки не имеет смысла, если мы используем один и тот же SQL снова и снова. Давайте использоватьsqlite3_prepare_v2 чтобы скомпилировать наш оператор SQL один раз, а затем связать наши параметры с этим оператором, используяsqlite3_bind_text:
/* Open input file and import into the database */
cStartClock = clock();
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db, sSQL, BUFFER_SIZE, &stmt, &tail);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);
sqlite3_step(stmt);
sqlite3_clear_bindings(stmt);
sqlite3_reset(stmt);
n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
Импортировано 864913 записей за 16,27 секунды
Приятно! Там немного больше кода (не забудьте позвонитьsqlite3_clear_bindings а такжеsqlite3_reset), но мы более чем удвоили нашу производительность53 000 вставок в секунду.
По умолчанию SQLite приостанавливается после выдачи команды записи на уровне ОС. Это гарантирует, что данные будут записаны на диск. Установивsynchronous = OFFМы инструктируем SQLite просто передать данные в ОС для записи, а затем продолжить. Существует вероятность того, что файл базы данных может быть поврежден, если на компьютере произойдет катастрофический сбой (или сбой питания) перед записью данных на диск:
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
Импортировано 864913 записей за 12,41 секунды
Улучшения теперь меньше, но мы до69 600 вставок в секунду.
PRAGMA journal_mode = MEMORYРассмотрите возможность сохранения журнала отката в памяти, оцениваяPRAGMA journal_mode = MEMORY, Ваша транзакция будет быстрее, но если вы потеряете энергию или ваша программа выйдет из строя во время транзакции, ваша база данных может остаться в поврежденном состоянии с частично завершенной транзакцией:
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
Импортировано 864913 записей за 13.50 секунд
Немного медленнее, чем предыдущая оптимизация в64 000 вставок в секунду.
ПРАГМА синхронная = ВЫКЛа также PRAGMA journal_mode = MEMORYДавайте объединим две предыдущие оптимизации. Это немного более рискованно (в случае сбоя), но мы просто импортируем данные (не управляя банком):
/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
Импортировано 864913 записей за 12,00 секунд
Фантастика! Мы можем сделать72 000 вставок в секунду.
Использование базы данных в памятиДля простоты давайте опираемся на все предыдущие оптимизации и переопределим имя файла базы данных, чтобы мы полностью работали в оперативной памяти:
#define DATABASE ":memory:"
Импортировано 864913 записей за 10,94 секунды
Хранить нашу базу данных в оперативной памяти непросто, но впечатляет, что мы можем79 000 вставок в секунду.
Рефакторинг кода CХотя это и не улучшение SQLite, мне не нравятся дополнительныеchar* операции присваивания вwhile петля. Давайте быстро рефакторинг этого кода, чтобы передать выводstrtok() прямо вsqlite3_bind_text()и пусть компилятор попытается ускорить процесс для нас:
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Branch */
sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Version */
sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Stop Number */
sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Vehicle */
sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Date */
sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Time */
sqlite3_step(stmt); /* Execute the SQL Statement */
sqlite3_clear_bindings(stmt); /* Clear bindings */
sqlite3_reset(stmt); /* Reset VDBE */
n++;
}
fclose (pFile);
Примечание: мы вернулись к использованию реального файла базы данных. Базы данных в памяти быстрые, но не обязательно практичные
Импортировано 864913 записей за 8,94 секунды
Небольшой рефакторинг кода обработки строки, используемого в привязке нашего параметра, позволил нам выполнить96 700 вставок в секунду. Я думаю, можно с уверенностью сказать, что этоочень быстро, Когда мы начнем настраивать другие переменные (например, размер страницы, создание индекса и т. Д.), Это будет нашим эталоном.
Резюме (пока)Я надеюсь, что ты все еще со мной! Причина, по которой мы пошли по этому пути, заключается в том, что производительность массовых вставок так сильно варьируется в SQLite, и не всегда очевидно, какие изменения необходимо внести для ускорения нашей работы. Используя тот же компилятор (и опции компилятора), ту же версию SQLite и те же данные, мы оптимизировали наш код и используем SQLite для работыот наихудшего сценария 85 вставок в секунду до более 96 000 вставок в секунду!
СОЗДАТЬ ИНДЕКС, затем ВСТАВИТЬ против ВСТАВКИ, затем СОЗДАТЬ ИНДЕКСПрежде чем мы начнем измерятьSELECT производительность, мы знаем, что мы будем создавать индексы. В одном из ответов ниже было предложено, чтобы при массовых вставках индекс быстрее создавался после вставки данных (в отличие от создания индекса сначала, а затем вставки данных). Давай попробуем:
Создать индекс, затем вставить данные
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...
Импортировано 864913 записей за 18,13 секунд
Вставьте данные, затем создайте индекс
...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
Импортировано 864913 записей за 13,66 секунд
Как и ожидалось, массовая вставка выполняется медленнее, если индексируется один столбец, но это имеет значение, если индекс создается после вставки данных. Наш базовый уровень без индекса составляет 96 000 вставок в секунду.Сначала создание индекса, а затем вставка данных дает нам 47 700 вставок в секунду, тогда как вставка данных сначала, а затем создание индекса дает нам 63 300 вставок в секунду.
Я бы с удовольствием принял предложения по другим сценариям, чтобы попробовать ... И скоро собираю аналогичные данные для запросов SELECT.