10 Вопрос: Улучшить производительность SQLite INSERT-в-секунду?

вопрос создан в Wed, Apr 24, 2019 12:00 AM

Оптимизировать SQLite сложно. Массовая вставка приложения C может варьироваться от 85 вставок в секунду до более 96 000 вставок в секунду!

Фон . Мы используем SQLite как часть настольного приложения. У нас есть большие объемы данных конфигурации, хранящихся в файлах XML, которые анализируются и загружаются в базу данных SQLite для дальнейшей обработки при инициализации приложения. SQLite идеально подходит для этой ситуации, потому что он быстрый, не требует специальной настройки, а база данных хранится на диске в виде одного файла.

Обоснование: Изначально я был разочарован производительностью, которую я видел. Оказывается, производительность SQLite может значительно различаться (как для массовых вставок, так и для выборок. ) в зависимости от того, как настроена база данных и как вы используете API. Было непросто выяснить, какие были все варианты и методы, поэтому я подумал, что было бы разумно создать эту вики-запись сообщества, чтобы поделиться результатами с читателями Stack Overflow, чтобы избавить других от проблем, связанных с теми же исследованиями. /р>

The Experiment: Вместо того, чтобы просто говорить о советах по повышению производительности в общем смысле (например, «Использовать транзакцию!» ), я подумал, что лучше написать некоторый код на C и фактически измеряет влияние различных вариантов. Мы собираемся начать с простых данных:

  • Текстовый файл с разделителями табуляции в 28 МБ (приблизительно 865 000 записей) из полного расписания транзита. для города Торонто
  • Мой тестовый компьютер - P4 с тактовой частотой 3,60 ГГц и Windows XP.
  • Код скомпилирован с Visual C ++ 2005 как " Отпустите "с помощью" Полная оптимизация "(/Ox) и Favor Fast Code (/Ot).
  • Я использую SQLite "Amalgamation", скомпилированный непосредственно в мое тестовое приложение. Моя версия SQLite немного старше (3.6.7), но я подозреваю, что эти результаты будут сопоставимы с последним выпуском (пожалуйста, оставьте комментарий, если вы думаете иначе).

Давайте напишем некоторый код!

The Code: простая программа на 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 вставок в секунду.

PRAGMA синхронный = ВЫКЛ

По умолчанию 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 synchronous = OFF и 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   секунд р>

Fantastic! Мы можем сделать 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 вставок в секунду!

CREATE INDEX, затем INSERT и INSERT, затем CREATE INDEX

Прежде чем мы начнем измерять производительность 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.

    
2788
  1. Хорошая мысль! В нашем случае мы имеем дело с примерно 1,5 миллионами пар ключ /значение, считанными из текстовых файлов XML и CSV в 200 тыс. Записей. Маленький по сравнению с базами данных, на которых работают такие сайты, как SO- но достаточно большой, чтобы настройка производительности SQLite стала важной.
    2009-11-10 22: 34: 59Z
  2. "У нас есть большие объемы данных конфигурации, хранящихся в файлах XML, которые анализируются и загружаются в базу данных SQLite для дальнейшей обработки при инициализации приложения." почему вы не храните все в базе данных sqlite, вместо того, чтобы хранить в XML и затем загружать все во время инициализации?
    2012-02-21 08: 36: 48Z
  3. Вы пытались не вызывать sqlite3_clear_bindings(stmt);? Вы каждый раз устанавливаете привязки, которых должно быть достаточно: Перед первым вызовом sqlite3_step () или сразу после sqlite3_reset () приложение может вызывать один из интерфейсов sqlite3_bind () для присоединения значений к параметрам. Каждый вызов sqlite3_bind () переопределяет предыдущие привязки для одного и того же параметра (см .: sqlite.org/cintro.html ). В документах для этой функции нет ничего, что говорит о том, что вы должны вызывать ее.
    2012-08-03 19: 33: 20Z
  4. ahcox: привязка к адресу, на который указывает, а не к переменной, поэтому не будет работать, поскольку strtok каждый раз возвращает новый указатель. Вам нужно будет либо strcpy после каждого strtok, либо создать свой собственный токенизатор, который всегда копирует при чтении по строке.
    2012-09-02 15: 20: 05Z
  5. Вы делали повторные измерения? «Победа» 4s за обход 7 локальных указателей странна, даже если предположить, что оптимизатор запутан.
    2012-11-26 15: 11: 34Z
10 ответов                              10                         

Несколько советов:

  1. Поместите вставки /обновления в транзакцию.
  2. Для более старых версий SQLite - рассмотрите менее параноидальный режим журнала (pragma journal_mode). Это NORMAL, а затем OFF, который может значительно увеличить скорость вставки, если вы не слишком беспокоитесь о возможном повреждении базы данных в случае сбоя ОС. Если ваше приложение дает сбой, данные должны быть в порядке. Обратите внимание, что в более новых версиях настройки OFF/MEMORY небезопасны для сбоев на уровне приложений.
  3. Игра с размерами страниц также имеет значение (PRAGMA page_size). Наличие страниц большего размера может сделать чтение и запись более быстрыми, поскольку большие страницы хранятся в памяти. Обратите внимание, что для вашей базы данных будет использовано больше памяти.
  4. Если у вас есть индексы, попробуйте позвонить по номеру CREATE INDEX после выполнения всех вставок. Это значительно быстрее, чем создание индекса и вставка.
  5. Вы должны быть очень осторожны, если у вас есть параллельный доступ к SQLite, поскольку вся база данных заблокирована, когда запись завершена, и, хотя возможно несколько считывателей, запись будет заблокирована. Это было несколько улучшено с добавлением WAL в более новых версиях SQLite.
  6. Воспользуйтесь преимуществом экономии места ... меньшие базы данных работают быстрее. Например, если у вас есть пары ключ-значение, попробуйте сделать ключ INTEGER PRIMARY KEY, если это возможно, что заменит столбец подразумеваемого уникального номера строки в таблице.
  7. Если вы используете несколько потоков, вы можете попробовать использовать общий кэш страницы , который будет разрешить разделение загруженных страниц между потоками, что позволяет избежать дорогостоящих вызовов ввода /вывода.
  8. Не используйте !feof(file)!

Я также задавал похожие вопросы здесь и 725

2018-06-04 15: 07: 47Z
  1. Документы не знают PRAGMA journal_mode NORMAL 2014-01-31 08: 52: 57Z
  2. Давно мои предложения применялись к более старым версиям до появления WAL. Похоже, что DELETE - это новая нормальная настройка, а теперь есть настройки OFF и MEMORY. Я полагаю, что OFF /MEMORY улучшит производительность записи за счет целостности базы данных, а OFF полностью отключит откат.
    2014-01-31 14: 13: 04Z
  3. для # 7, есть ли у вас пример того, как включить общий кеш страниц с помощью оболочки c # system.data.sqlite?
    2015-08-13 22: 37: 44Z
  4. # 4 вернул давние воспоминания веков - в прежние времена был по крайней мере один случай, когда отбрасывали индекс перед группой добавлений и заново создавали его после этого значительно ускорился. Может все же работать быстрее на современных системах для некоторых добавлений, когда вы знаете, что у вас есть единственный доступ к таблице в течение определенного периода.
    2016-08-24 19: 46: 28Z

Попробуйте использовать SQLITE_STATIC вместо SQLITE_TRANSIENT для этих вставок.

SQLITE_TRANSIENT заставит SQLite скопировать строковые данные перед возвратом.

SQLITE_STATIC сообщает, что указанный вами адрес памяти будет действителен до тех пор, пока запрос не будет выполнен (что в этом цикле всегда имеет место). Это сэкономит вам несколько операций выделения, копирования и освобождения за цикл. Возможно, большое улучшение.

    
121
2015-08-28 16: 54: 11Z

Избегайте sqlite3_clear_bindings (stmt);

Код в тесте каждый раз устанавливает привязки, которых должно быть достаточно.

В вводной части API C из документации по SQLite говорится

  

До вызова sqlite3_step () в первый раз или сразу   после sqlite3_reset () приложение может вызвать один из   sqlite3_bind () интерфейс для добавления значений к параметрам. каждый   вызов sqlite3_bind () отменяет предыдущие привязки для того же параметра

(см .: sqlite.org/cintro.html ). В документации нет ничего для этой функции о том, что вы должны вызывать ее в дополнение к простому установка привязок.

Подробнее: Avoid_sqlite3_clear_bindings ()     

92
2019-06-08 06: 37: 36Z
  1. Замечательно правильно: «Вопреки интуиции многих, sqlite3_reset () не сбрасывает привязки для подготовленного оператора. Используйте эту подпрограмму для сброса всех параметров хоста в NULL. " - sqlite.org/c3ref/clear_bindings.html
    2016-11-02 14: 09: 15Z

На массовых вставках

Вдохновленный этим постом и вопросом о переполнении стека, который привел меня сюда - Можно ли одновременно вставлять несколько строк в базу данных SQLite? - Iопубликовал мой первый Git репозиторий:

https://github.com/rdpoor/CreateOrUpdate р>

массовая загрузка массива ActiveRecords в MySQL , SQLite или базы данных PostgreSQL . Он включает в себя возможность игнорировать существующие записи, перезаписать их или вызвать ошибку. Мои элементарные тесты показывают 10-кратное улучшение скорости по сравнению с последовательными записями - YMMV.

Я использую его в производственном коде, где мне часто нужно импортировать большие наборы данных, и я очень доволен этим.

    
54
2017-05-23 12: 02: 48Z
  1. @ Jess: Если вы перейдете по ссылке, вы увидите, что он имел в виду синтаксис пакетной вставки.
    2013-10-15 08: 23: 26Z

Массовый импорт лучше всего работает, если вы можете разделить свои утверждения INSERT /UPDATE . Значение 10 000 или около того хорошо сработало для меня в таблице с несколькими строками, YMMV ...

    
45
2012-02-21 08: 30: 20Z
  1. Вы хотите настроить x = 10000 так, чтобы x = cache [= cache_size * page_size] /средний размер вашей вставки.
    2013-10-13 05: 10: 04Z

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

Сначала найдите элементы в таблице:

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 столбцов, примерно 100 000 или более строк).

Слишком много или слишком мало потоков этого не сделают, вам нужно провести тестирование и профилировать себя.

Также для нас SHAREDCACHE замедлил производительность, поэтому я вручную установил PRIVATECACHE (потому что он был включен для нас глобально)

    
37
2019-06-08 06: 36: 48Z

Я не смог получить никакой прибыли от транзакций, пока не увеличил значение cache_size, т.е. PRAGMA cache_size=10000;

    
27
2015-04-15 09: 47: 49Z

Прочитав это руководство, я попытался внедрить его в свою программу.

У меня есть 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 /с

Если у кого-то есть какие-либо идеи о том, как его ускорить, я открыт для предложений.

UPDATE

В дополнение к моему ответу выше, вы должны помнить, что число операций вставки в секунду зависит от используемого вами жесткого диска. Я протестировал его на 3 разных ПК с разными жесткими дисками и получил огромные различия во времени. ПК1 (1 час 30 м), ПК2 (6 часов) ПК3 (14 часов), поэтому я начал задаваться вопросом, почему это так.

После двух недель исследований и проверки нескольких ресурсов: жесткого диска, оперативной памяти, кэша, я обнаружил, что некоторые настройки на жестком диске могут влиять на скорость ввода-вывода. Нажав на свойства желаемого выходного диска, вы увидите две опции на вкладке «Общие». Opt1: Сжать этот диск, Opt2: Разрешить индексировать содержимое файлов на этом диске.

Отключив эти две опции, все 3 ПК теперь требуют примерно одинакового времени для завершения (1 час и от 20 до 40 минут). Если вы столкнулись с медленной вставкой, проверьте, настроен ли ваш жесткий диск с этими параметрами. Это сэкономит вам много времени и головной боли, пытаясь найти решение

    
18
2018-01-06 11: 07: 06Z
  1. Я предложу следующее. * Используйте SQLITE_STATIC против SQLITE_TRANSIENT, чтобы избежать копирования строки. Вы должны убедиться, что строка не будет изменена до выполнения транзакции. * Использовать массовую вставку INSERT INTO stop_times VALUES (NULL,?,?,?,?,?,?,?,? ,?), (NULL,?,?,?,?,?,?,?,?,?), (NULL,?,?,?,?,?,?,?,?,?), (NULL ,?,?,?,?,?,?,?,?,?), (NULL,?,?,?,?,?,?,?,?,?) * Mmap файл, чтобы уменьшить количество Системные вызовы.
    2019-02-06 20: 33: 41Z
  2. Благодаря этому я могу импортировать 5 582 642 записей за 11,51 секунды
    2019-02-06 20: 43: 27Z

Ответ на ваш вопрос заключается в том, что более новый sqlite3 улучшил производительность, используйте это.

Этот ответ Почему SQLAlchemy вставляет с sqlite в 25 раз медленнее, чем использование sqlite3 напрямую? от SqlAlchemy Orm Автор имеет 100 000 вставок за 0,5 секунды, и я видел похожие результаты с python-sqlite и SqlAlchemy. Что заставляет меня верить, что производительность улучшилась с sqlite3

    
10
2017-06-15 20: 31: 47Z

Используйте 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 для получения более подробной информации

    
- 1
2019-03-12 09: 55: 16Z
источник размещен Вот