Улучшить производительность SQLite INSERT-в-секунду?
Оптимизировать SQLite сложно. Массовая вставка приложения C может варьироваться от 85 вставок в секунду до более 96000 вставок в секунду!
Справочная информация: Мы используем 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 секунды
Так-то лучше. Простое объединение всех наших вставок в одну транзакцию улучшило нашу производительность до 23000 вставок в секунду.
Использование подготовленного заявления
Использование транзакции было огромным улучшением, но перекомпиляция оператора 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), но мы более чем удвоили нашу производительность до 53000 вставок в секунду.
ПРАГМА синхронная = ВЫКЛ
По умолчанию 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 синхронный = ВЫКЛ и PRAGMA journal_mode = ПАМЯТЬ
Давайте объединим две предыдущие оптимизации. Это немного более рискованно (в случае сбоя), но мы просто импортируем данные (а не банк):
/* 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 операций вставки в секунду.
Рефакторинг кода С
Хотя это не совсем улучшение 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 вставками в секунду к более чем 96000 вставок в секунду!
СОЗДАТЬ ИНДЕКС, затем ВСТАВИТЬ против ВСТАВИТЬ, затем СОЗДАТЬ ИНДЕКС
Прежде чем мы начнем измерять 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 секунд
Как и ожидалось, массовая вставка выполняется медленнее, если индексируется один столбец, но это имеет значение, если индекс создается после вставки данных. Наш базовый уровень без индекса составляет 96000 вставок в секунду. Сначала создание индекса, а затем вставка данных дает нам 47 700 вставок в секунду, тогда как вставка данных сначала, а затем создание индекса дает нам 63 300 вставок в секунду.
Я с радостью приму предложения по другим сценариям, которые можно попробовать... И в ближайшее время буду собирать аналогичные данные для запросов SELECT.
15 ответов
Несколько советов:
- Поместите вставки / обновления в транзакцию.
- Для более старых версий SQLite - рассмотрите менее параноидальный режим журнала (
pragma journal_mode). ЕстьNORMALи естьOFF, что может значительно увеличить скорость вставки, если вы не слишком беспокоитесь о возможном повреждении базы данных при сбое ОС. Если ваше приложение дает сбой, данные должны быть в порядке. Обратите внимание, что в более новых версияхOFF/MEMORYнастройки не безопасны для сбоев на уровне приложений. - Игра с размерами страниц также имеет значение (
PRAGMA page_size). Наличие страниц большего размера может ускорять чтение и запись, поскольку в памяти хранятся страницы большего размера. Обратите внимание, что для вашей базы данных будет использовано больше памяти. - Если у вас есть индексы, подумайте о том, чтобы позвонить
CREATE INDEXпосле выполнения всех ваших вставок. Это значительно быстрее, чем создание индекса и вставка. - Вы должны быть очень осторожны, если у вас есть параллельный доступ к SQLite, так как вся база данных заблокирована, когда запись завершена, и хотя возможно несколько считывателей, записи будут заблокированы. Это было несколько улучшено с добавлением WAL в более новых версиях SQLite.
- Воспользуйтесь преимуществом экономии места... меньшие базы данных работают быстрее. Например, если у вас есть пары ключ-значение, попробуйте сделать ключ
INTEGER PRIMARY KEYесли возможно, это заменит подразумеваемый столбец уникального номера строки в таблице. - Если вы используете несколько потоков, вы можете попробовать использовать общий кеш страниц, который позволит загруженным страницам делиться между потоками, что позволит избежать дорогостоящих вызовов ввода-вывода.
- Не использовать
!feof(file)!
Попробуйте использовать SQLITE_STATIC вместо SQLITE_TRANSIENT для этих вставок.
SQLITE_TRANSIENT заставит SQLite скопировать строковые данные перед возвратом.
SQLITE_STATIC сообщает, что адрес памяти, который вы дали, будет действителен до тех пор, пока запрос не будет выполнен (что в этом цикле всегда имеет место). Это сэкономит вам несколько операций выделения, копирования и освобождения за цикл. Возможно, большое улучшение.
Избегайте sqlite3_clear_bindings(stmt);
Код в тесте устанавливает привязки каждый раз, через которые должно быть достаточно.
Введение в API C из документов SQLite гласит:
Перед вызовом sqlite3_step () в первый раз или сразу после sqlite3_reset () приложение может вызвать один из интерфейсов sqlite3_bind () для присоединения значений к параметрам. Каждый вызов sqlite3_bind () переопределяет предыдущие привязки для одного и того же параметра
(см.: https://www.sqlite.org/cintro.html). В этой документации нет ничего, что говорило бы, что вы должны вызывать ее в дополнение к простой установке привязок.
Более подробно: http://www.hoogli.com/blogs/micro/index.html
На объемных вставках
Вдохновленный этим постом и вопросом о переполнении стека, который привел меня сюда - можно ли одновременно вставлять несколько строк в базу данных SQLite? - Я разместил свой первый Git- репозиторий:
https://github.com/rdpoor/CreateOrUpdate
какая масса загружает массив ActiveRecords в базы данных MySQL, SQLite или PostgreSQL. Он включает в себя возможность игнорировать существующие записи, перезаписать их или вызвать ошибку. Мои элементарные тесты показывают 10-кратное улучшение скорости по сравнению с последовательными записями - YMMV.
Я использую его в производственном коде, где мне часто нужно импортировать большие наборы данных, и я очень доволен этим.
Массовый импорт лучше всего работает, если вы можете разделить свои операторы INSERT/UPDATE. Значение 10000 или около того хорошо сработало для меня в таблице с несколькими строками, YMMV...
Если вы заботитесь только о чтении, более быстрая (но может считывать устаревшие данные) версия - это чтение из нескольких соединений из нескольких потоков (соединение на поток).
Сначала найдите предметы в таблице:
SELECT COUNT(*) FROM table
затем читать на страницах (LIMIT/OFFSET)
SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>
где и рассчитываются на поток, как это:
int limit = (count + n_threads - 1)/n_threads;
для каждой темы:
int offset = thread_index * limit
Для нашей маленькой (200 МБ) базы данных это ускорилось на 50-75% (64-разрядная версия 3.8.0.2 в Windows 7). Наши таблицы сильно ненормализованы (1000-1500 столбцов, примерно 100000 или более строк).
Слишком много или слишком мало потоков этого не сделают, вам нужно самим оценить и профилировать.
Также для нас SHAREDCACHE замедлил производительность, поэтому я вручную поставил PRIVATECACHE (потому что он был включен для нас глобально)
Я не смог получить никакой прибыли от транзакций, пока не поднял cache_size до более высокого значения, т.е. PRAGMA cache_size=10000;
Прочитав этот урок, я попытался реализовать его в своей программе.
У меня есть 4-5 файлов, которые содержат адреса. Каждый файл имеет около 30 миллионов записей. Я использую ту же конфигурацию, которую вы предлагаете, но мое количество INSERT в секунду слишком мало (~10.000 записей в секунду).
Вот где ваше предложение терпит неудачу. Вы используете одну транзакцию для всех записей и одну вставку без ошибок / сбоев. Допустим, вы разбиваете каждую запись на несколько вставок в разных таблицах. Что произойдет, если запись сломана?
Команда ON CONFLICT не применяется, потому что, если у вас есть 10 элементов в записи, и вам нужно, чтобы каждый элемент был вставлен в другую таблицу, если элемент 5 получает ошибку CONSTRAINT, то все предыдущие 4 вставки также должны быть выполнены.
Так вот, где приходит откат. Единственная проблема с откатом состоит в том, что вы теряете все свои вставки и начинаете сверху. Как вы можете решить это?
Моим решением было использовать несколько транзакций. Я начинаю и заканчиваю транзакцию каждые 10.000 записей (не спрашивайте, почему это число, оно было самым быстрым, которое я проверял). Я создал массив размером 10.000 и вставил туда успешные записи. Когда происходит ошибка, я делаю откат, начинаю транзакцию, вставляю записи из моего массива, фиксирую и затем начинаю новую транзакцию после поврежденной записи.
Это решение помогло мне обойти проблемы, возникающие у меня при работе с файлами, содержащими плохие / повторяющиеся записи (у меня было почти 4% плохих записей).
Алгоритм, который я создал, помог мне сократить процесс на 2 часа. Окончательный процесс загрузки файла 1 час 30 минут, который все еще медленный, но не сравнивается с 4 часами, которые он изначально занимал. Мне удалось ускорить вставки с 10.000/ с до ~14.000/ с
Если у кого-то есть какие-либо идеи о том, как это ускорить, я открыт для предложений.
ОБНОВЛЕНИЕ:
В дополнение к моему ответу выше, вы должны иметь в виду, что число вставок в секунду зависит от используемого вами жесткого диска. Я протестировал его на 3 разных ПК с разными жесткими дисками и получил огромные различия во времени. ПК1 (1 час 30 м), ПК2 (6 часов) ПК3 (14 часов), поэтому я начал задаваться вопросом, почему это так.
После двух недель исследований и проверки нескольких ресурсов: жесткого диска, оперативной памяти, кэша, я обнаружил, что некоторые настройки на жестком диске могут влиять на скорость ввода-вывода. Нажав на свойства желаемого выходного диска, вы увидите две опции на вкладке "Общие". Opt1: сжать этот диск, Opt2: разрешить индексировать содержимое файлов на этом диске.
Отключив эти две опции, все 3 ПК теперь требуют примерно одинакового времени для завершения (1 час и от 20 до 40 минут). Если вы столкнулись с медленной вставкой, проверьте, настроен ли ваш жесткий диск с этими параметрами. Это сэкономит вам много времени и головной боли, пытаясь найти решение.
Ответ на ваш вопрос заключается в том, что более новый sqlite3 имеет улучшенную производительность, используйте это.
Этот ответ Почему SQLAlchemy вставка с sqlite в 25 раз медленнее, чем использование sqlite3 напрямую? Автор SqlAlchemy Orm Автор имеет 100 тыс. вставок за 0,5 секунды, и я видел похожие результаты с python-sqlite и SqlAlchemy. Что заставляет меня верить, что производительность улучшилась с sqlite3
У Пола Беттса есть отличная лекция о том, как он так быстро сделал C# akavache: https://www.youtube.com/watch?v=j7WnQhwBwqA
Может быть, вы можете найти некоторые подсказки для вас. Слишком долго, чтобы сделать краткое резюме здесь
Лучше всего разделить задачу на несколько транзакций, как это сделал @Jimmy_A. В противном случае вы можете переполнить свою оперативную память огромной транзакцией и тяжелой задачей COMMIT.
Для дальнейшей настройки производительности вы также можете включить кэш с обратной записью на жестком диске, если вы используете систему с каким-либо резервным питанием от батареи (ноутбук, ИБП, RAID-контроллер с батареей ...).
С использованиемPRAGMA journal_mode = WALудвоил скоростьINSERTs в моем случае, так как внутренне это то же самое, что пакетирование INSERTS , как предлагается здесь .
В моем случае мне нужно было импортировать данные в индекс, а не только в таблицу. В SQLite есть замечательная функция БЕЗ ROWID , которая позволяет объединить таблицу и индекс. По умолчанию таблица в SQLite также является B-Tree, и все индексы хранятся на отдельных страницах B-Tree. Использование БЕЗ ROWID использует только одно B-дерево для таблицы и индекса.
я также использовалPRAGMA auto_vacuum = 0так как технически это должно помешать SQLite улучшить использование пространства за счет размера базы данных, но, похоже, это не дает видимой разницы в производительности.
Хотя мой случай немного отличается от требований OP, первое предложение использовать WAL должно иметь значение для его случая.
Начиная с 3.24 SQLite поддерживает оператор UPSERT.
См. "SQL как понять SQLite" 1 Когда строка не существует, она вставляется, в противном случае обновляется. Другие двигатели называют это MERGE.
Используйте ContentProvider для вставки массовых данных в БД. Приведенный ниже метод используется для вставки больших объемов данных в базу данных. Это должно улучшить производительность INSERT в секунду SQLite.
private SQLiteDatabase database;
database = dbHelper.getWritableDatabase();
public int bulkInsert(@NonNull Uri uri, @NonNull ContentValues[] values) {
database.beginTransaction();
for (ContentValues value : values)
db.insert("TABLE_NAME", null, value);
database.setTransactionSuccessful();
database.endTransaction();
}
Вызвать метод bulkInsert:
App.getAppContext().getContentResolver().bulkInsert(contentUriTable,
contentValuesArray);
Ссылка: https://www.vogella.com/tutorials/AndroidSQLite/article.html проверка Использование раздела ContentProvider для получения более подробной информации
Используйте начальные и конечные транзакции для вставки в пакет.