Питання Покращити продуктивність INSERT-per-second SQLite?


Оптимізація SQLite складна. Масова інсталяція продуктивності програми C може коливатися від 85 вкладок в секунду до понад 96000 вставок у секунду!

Довідкова інформація: Ми використовуємо SQLite як частину настільної програми. У нас великі обсяги даних конфігурації зберігаються у файлах XML, які аналізуються та завантажуються в базу даних SQLite для подальшої обробки при ініціалізації програми. SQLite ідеально підходить для цієї ситуації, оскільки вона швидко, вона не вимагає спеціалізованої конфігурації, а база даних зберігається на диску як єдиний файл.

Обґрунтування:  Спочатку я була розчарована продуктивністю, яку я бачив. Виявляється, продуктивність SQLite може суттєво відрізнятися (як для основних вставок, так і для вибору) залежно від того, як налаштована база даних і як ви використовуєте API. Не було тривіальним питанням, щоб з'ясувати, які варіанти та методи були, тому я вважав за доцільне створити цей вхід до спільноти вікі, щоб поділитися результатами з читачами сценаріїв переповнення, щоб заощадити іншим проблему тих самих досліджень.

Експеримент: Замість того, щоб просто говорити про підказки про ефективність в загальному сенсі (тобто "Використовувати транзакцію!"), Я думав, що найкраще написати якийсь C-код і насправді міра вплив різних варіантів. Ми почнемо з деяких простих даних:

  • Текстовий файл із роздільною здатністю 28 Мб (близько 865 000 записів) повний транзитний графік для міста Торонто
  • Моя тестова машина - це 3,60 ГГц P4 під керуванням Windows XP.
  • Код складається з Visual C ++ 2005 р. Як "Release" з "Повний Оптимізація" (/ Ox) та Favor Fast Code (/ Ot).
  • Я використовую SQLite "Об'єднання", складене безпосередньо в моєму тестовому додатку. Версія 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;
}

"Контроль"

Запуск коду як так само фактично не виконує жодних операцій з базою даних, але це дасть нам уявлення про те, наскільки швидко виконуються процеси обробки файлів вводу / виводу та обробки рядків.

Імпортовано 864913 записів у 0,94   секунд

Чудово! Ми можемо зробити 920 тисяч вставок у секунду, за умови, що ми взагалі не робимо жодних вставок :-)


"Найгірший сценарій"

Ми збираємося генерувати рядок 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   секунд

Yikes! 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 over-and-over. Давайте використовувати 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 вкладок в секунду.

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 = ПАМ'ЯТЬ

Розглянемо можливість зберігання журналу відкату в пам'яті шляхом оцінки 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   секунд

Трохи повільніше попередньої оптимізації на 64000 вставок у секунду.

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   секунд

Нам не суперпрактично зберігати нашу базу даних в оперативній пам'яті, але це вражає, що ми можемо виконати 79000 вставок у секунду.

Рефакторинг 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 вставок у секунду до більш ніж 96000 вставок у секунду!


CREATE INDEX потім INSERT v 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   секунд

Як і очікувалося, основні вставки стають повільнішими, якщо один стовпець індексується, але це змінює, якщо індекс створюється після вставки даних. Наша безіндексна базова лінія становить 96000 входів на секунду. Створення індексу спочатку потім введення даних дає нам 47700 вставок в секунду, тоді як спочатку додавання даних, після створення індексу дає 63 300 вставок у секунду.


Я з радістю прийму пропозицій для інших сценаріїв, щоб спробувати ... І збиратиму подібні дані для SELECT запитів незабаром.


2573


походження


Влучне зауваження! У нашому випадку ми маємо справу з приблизно 1,5 мільйонами ключів / значень пари, прочитаних з текстових файлів XML і CSV у 200 тисячах записів. Малі в порівнянні з базами даних, на яких запускаються такі сайти, як SO - але досить великий, що налаштування продуктивності SQLite стають важливими. - Mike Willekes
"Ми маємо великі обсяги даних конфігурації, що зберігаються в XML-файлах, які аналізуються та завантажуються в базу даних SQLite для подальшої обробки при ініціалізації програми". чому б ви не зберігали все в базі даних sqlite, а не зберігали в XML, а потім завантажували все під час ініціалізації? - CAFxX
Ви пробували не дзвонити sqlite3_clear_bindings(stmt);? Ви встановлюєте прив'язки кожного разу, через який повинно бути достатньо: Перш ніж викликати sqlite3_step () вперше або відразу після sqlite3_reset (), програма може викликати один із інтерфейсів sqlite3_bind () для прикріплення значень до параметрів. Кожен виклик до sqlite3_bind () перевизначає попередні прив'язки за тим самим параметром (побачити: sqlite.org/cintro.html) У цьому немає нічого документи для цієї функції кажучи, що ти повинен це назвати. - ahcox
ахкокс: прив'язка до вказаної адреси, а не до змінної, так що не буде працювати з тих пір strtok повертає новий покажчик кожного разу. Вам доведеться або це зробити strcpy після кожного strtok або створіть свій власний токенизатор, який завжди копіює, як він читається уздовж рядка. - nemetroid
Ви робили повторні вимірювання? 4-е "перемога", що дозволяє уникнути 7 локальних покажчиків, є дивною, навіть якщо припускати, що це заважає оптимізатору. - peterchen


Відповіді:


Кілька порад:

  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)!

Я також задавав подібні питання тут і тут.


672



Документи не знають PRAGMA journal_mode NORMAL sqlite.org/pragma.html#pragma_journal_mode - OneWorld
Якийсь час, мої пропозиції застосовувалися до старіших версій, перш ніж з'явився WAL. Схоже, DELETE - це нове нормальне налаштування, а зараз є також параметри OFF та MEMORY. Я вважаю, OFF / MEMORY покращить ефективність запису за рахунок цілісності бази даних, а OFF вимкне відкази повністю. - Snazzer
для # 7, у вас є приклад того, як включити кеш обмін сторіноквикористовуючи обгортку c # system.data.sqlite? - Aaron Hudon
# 4 повернула вікові давні спогади - раніше, коли випав індекс до того, як група додає і повторно створила її, він мав принаймні один випадок, після чого значно вставив спред. Може все ще працювати швидше в сучасних системах для деяких доповнень, де ви знаєте, що ви маєте єдиний доступ до таблиці за період. - Bill K


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

SQLITE_TRANSIENT буде викликати SQLite для копіювання рядкових даних перед тим, як повертатись.

SQLITE_STATIC повідомляє, що адреса пам'яті, яку ви надали, буде дійсною, доки запит не буде виконано (що завжди в цьому циклі). Це дозволить заощадити декілька операцій виділення, копіювання та вилучення за цикл. Можливо велике поліпшення.


102





Уникати sqlite3_clear_bindings (stmt);

Код в тесті встановлює прив'язки кожного разу, через який повинно бути достатньо.

Інтерфейс API C із SQLite docs говорить

Перед викликом sqlite3_step () вперше або негайно   після sqlite3_reset () програма може викликати одну з   sqlite3_bind () інтерфейси, щоб прикріпити значення до параметрів. Кожен   виклик sqlite3_bind () перевизначає попередні прив'язки за тим самим параметром

(побачити: sqlite.org/cintro.html) У документах немає нічого це функція кажучи, ви повинні називати це крім просто встановити прив'язки.

Більш детально: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Чудово вірно: "Всупереч інтуїції багатьох, sqlite3_reset () не скидає прив'язки на підготовлену операцію. Використовуйте цю процедуру для скидання всіх параметрів хоста до NULL". - sqlite.org/c3ref/clear_bindings.html - Francis Straccia


На оптових вставках

Натхненний цим постом та питанням переповнення стека, який мене привів - Чи можна вставити декілька рядків одночасно в базі даних SQLite? - Я опублікував свій перший Гіт сховище:

https://github.com/rdpoor/CreateOrUpdate

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

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


47



@Jess: Якщо ви стежите за посиланням, ви побачите, що він означав синтаксис пакетного вставки. - Alix Axel


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


40



Ви хочете налаштувати х = 10 000, так що x = cache [= cache_size * page_size] / середній розмір вставки. - Alix Axel


Якщо ви зацікавлені тільки в читанні, версія трохи швидше (але може сприймати застарілі дані) повинна бути прочитана з декількох з'єднань з декількох потоків (підключення за потоком).

Спочатку знайдіть елементи у таблиці:

 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 МБ) дБ це прискорило швидкість (64-розрядний 3.8.0.2 на Windows 7) на 50-75%. Наші таблиці сильно не нормовані (1000-1500 стовпчиків, приблизно 100 000 або більше рядків).

Занадто багато чи занадто маленькі нитки не будуть робити це, потрібно провести тестування та профілі себе.

Також для нас, SHAREDCACHE зробив продуктивність повільніше, тому я вручну встановив PRIVATECACHE (оскільки він був увімкнений для нас у всьому світі)


32





Я не отримую будь-який прибуток від транзакцій, доки я не підвищив cache_size до більш високого значення, тобто PRAGMA cache_size=10000;


20





Прочитавши цей навчальний посібник, я спробував застосувати його до моєї програми.

У мене 4-5 файлів, що містять адреси. Кожен файл має приблизно 30 мільйонів записів. Я використовую ту саму конфігурацію, яку ви пропонуєте, але мій номер INSERTs в секунду є низьким способом (~ 10,000 записів за секунду).

Ось де ваша пропозиція не вдається. Ви використовуєте одну транзакцію для всіх записів і одну вставку без помилок / помилок. Скажімо, ви розбиваєте кожну запис на кілька вставок на різні таблиці. Що станеться, якщо запис буде порушена?

Команда ON CONFLICT не застосовується, якщо у вас є 10 елементів у запису, і вам потрібно, щоб кожен елемент вставлявся в іншу таблицю, якщо на елемент 5 з'являється помилка CONSTRAINT, то всі попередні 4 вклади також повинні йти.

Ось тут відбувається відкат. Єдине питання з відмовою полягає в тому, що ви втратите всі вкладиші і починаєте зверху. Як ви можете це вирішити?

Моє рішення було використати багаторазовий операції Я починаю та закінчую транзакцію кожні 10 000 записів (не питайте, чому це число, це найшвидший, який я протестував). Я створив масив розміром 10.000 і вставив там успішні записи. Коли виникає помилка, я роблю відкат, починаю транзакцію, вставляю записи з масиву, фіксую, а потім починаю нову транзакцію після несправної записи.

Це рішення допомогло мені обійти проблеми, які я маю при роботі з файлами, що містять погані / дубльовані записи (у мене було майже 4% поганих записів).

Створений нами алгоритм допоміг скоротити процес на 2 години. Остаточний процес завантаження файлу 1ч 30м, який все ще повільний, але не порівняний з 4 годинами, що він спочатку взяв. Мені вдалося прискорити вставки з 10 000 / с до ~ 14 000 / с

Якщо у кого-небудь є інші ідеї щодо прискорення, я відкритий для пропозицій.

UPDATE:

У додаток до моєї відповіді вище, ви повинні мати на увазі, що вставляє в секунду залежно від жорсткого диску, який ви використовуєте теж. Я тестував це на 3 різних комп'ютерах з різними жорсткими дисками і мав величезні відмінності в часи. PC1 (1 год 30 м), PC2 (6 годин) PC3 (14 годин), тому я почав цікаво, чому б це було.

Після двох тижнів дослідження та перевірки кількох ресурсів: жорсткий диск, RAM, кеш, я дізнався, що деякі налаштування на вашому жорсткому диску можуть вплинути на швидкість вводу-виводу. Натискаючи властивості на бажаному диску виводу, ви можете побачити два варіанти на загальній вкладці. Opt1: Стиснення цього диска, Opt2: Дозволити файли цього диска індексувати вміст.

Відключивши ці два варіанти, всі 3 комп'ютери забирають приблизно однаковий час, щоб закінчити (1 год і 20 до 40 хвилин). Якщо ви зіткнулися з повільними вставками, перевірте, чи налаштований жорсткий диск на ці параметри. Це заощадить вам багато часу і головні болі, намагаючись знайти рішення


11





Відповідь на ваше запитання полягає в тому, що новий sqlite3 поліпшив роботу, використовуючи його.

Це відповідь Чому SQLAlchemy вставляється в sqlite 25 разів повільніше, ніж безпосередньо використовує sqlite3? Автор SqlAlchemy Orm автор має 100k вставки через 0,5 сек, і я бачив аналогічні результати з python-sqlite і SqlAlchemy. Що змушує мене вважати, що продуктивність покращилася завдяки sqlite3


5