Verbessern Sie die INSERT-pro-Sekunde-Leistung von SQLite?

Die Optimierung von SQLite ist schwierig. Die Beilagenleistung einer C-Anwendung kann zwischen 85 Beilagen pro Sekunde und über 96.000 Beilagen pro Sekunde variieren!

Hintergrund: Wir verwenden SQLite als Teil einer Desktop-Anwendung. Wir haben große Mengen an Konfigurationsdaten in XML-Dateien gespeichert, die analysiert und zur weiteren Verarbeitung in eine SQLite-Datenbank geladen werden, wenn die Anwendung initialisiert wird. SQLite ist ideal für diese Situation, da es schnell ist, keine spezielle Konfiguration erfordert und die Datenbank als einzelne Datei auf der Festplatte gespeichert wird.

Begründung: Anfangs war ich enttäuscht von der Leistung, die ich gesehen habe. Es stellt sich heraus, dass die Leistung von SQLite erheblich variieren kann (beides für Bulk-Einfügungen und -Auswahlen, je nachdem, wie die Datenbank konfiguriert ist und wie Sie die API verwenden. Es war keine triviale Angelegenheit, alle Optionen und Techniken herauszufinden. Daher hielt ich es für ratsam, diesen Community-Wiki-Eintrag zu erstellen, um die Ergebnisse mit Stack Overflow-Lesern zu teilen und anderen die Mühe der gleichen Untersuchungen zu ersparen.

The Experiment: Anstatt nur über Leistungstipps im allgemeinen Sinne zu sprechen (dh "Use a transaction!" ), fand ich es am besten C-Code schreiben und tatsächlich die Auswirkung verschiedener Optionen messen. Wir werden mit ein paar einfachen Daten beginnen:

• Eine 28-MB-TAB-begrenzte Textdatei (ca. 865.000 Datensätze) des vollständigen Transitplans für die Stadt Toronto
• Mein Testgerät ist ein P4 mit 3,60 GHz und Windows XP.
• Der Code wird mit Visual C++ 2005 als "Release" mit "Full Optimization" (/ Ox) und Favor Fast Code (/ Ot) kompiliert.
• Ich verwende die SQLite "Amalgamation", die direkt in meine Testanwendung kompiliert wurde. Die SQLite-Version, die ich habe, ist etwas älter (3.6.7), aber ich vermute, dass diese Ergebnisse mit der neuesten Version vergleichbar sind (bitte hinterlassen Sie einen Kommentar, wenn Sie etwas anderes denken).

Lass uns Code schreiben!

The Code: Ein einfaches C-Programm, das die Textdatei zeilenweise liest, die Zeichenfolge in Werte aufteilt und die Daten dann in eine SQLite-Datenbank einfügt. In dieser "Baseline" -Version des Codes wird die Datenbank erstellt, es werden jedoch keine Daten eingefügt:

/*************************************************************
    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;
}

Die Kontrolle"

Wenn Sie den Code so ausführen, wie er ist, werden keine Datenbankoperationen ausgeführt, es wird jedoch eine Vorstellung davon gegeben, wie schnell die E/A- und Zeichenfolgenverarbeitungsoperationen der unformatierten C-Datei sind.

Importierte 864913 Datensätze in 0,94 Sekunden

Toll! Wir können 920.000 Inserts pro Sekunde machen, vorausgesetzt wir machen keine Inserts :-)

Das "Worst-Case-Szenario"

Wir werden den SQL-String mit den aus der Datei gelesenen Werten generieren und diese SQL-Operation mit sqlite3_exec aufrufen:

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

Dies wird langsam sein, da die SQL für jede Einfügung in VDBE-Code kompiliert wird und jede Einfügung in einer eigenen Transaktion erfolgt. Wie langsam?

Importierte 864913 Datensätze in 9933.61 Sekunden

Huch! 2 Stunden und 45 Minuten Das sind nur 85 Einfügungen pro Sekunde.

Verwenden einer Transaktion

Standardmäßig wertet SQLite jede INSERT/UPDATE-Anweisung innerhalb einer eindeutigen Transaktion aus. Wenn Sie eine große Anzahl von Einfügungen durchführen, ist es ratsam, Ihre Operation in eine Transaktion zu binden:

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

Importierte 864913 Datensätze in 38.03 Sekunden

Das ist besser. Durch einfaches Einwickeln aller Beilagen in eine einzige Transaktion konnte die Leistung auf 23.000 Beilagen pro Sekunde. verbessert werden

Verwenden einer vorbereiteten Anweisung

Die Verwendung einer Transaktion war eine enorme Verbesserung, aber das erneute Kompilieren der SQL-Anweisung für jede Einfügung ist nicht sinnvoll, wenn immer wieder dieselbe SQL verwendet wird. Lassen Sie uns sqlite3_prepare_v2 verwenden, um unsere SQL-Anweisung einmal zu kompilieren und dann unsere Parameter mit sqlite3_bind_text an diese Anweisung zu binden:

/* 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;

Importierte 864913 Datensätze in 16,27 Sekunden

Nett! Es gibt ein bisschen mehr Code (vergessen Sie nicht, sqlite3_clear_bindings und sqlite3_reset aufzurufen), aber wir haben unsere Leistung auf 53.000 Einfügungen pro Sekunde) mehr als verdoppelt.

PRAGMA synchron = AUS

Standardmäßig wird SQLite nach der Ausgabe eines Schreibbefehls auf Betriebssystemebene angehalten. Dies garantiert, dass die Daten auf die Festplatte geschrieben werden. Durch Setzen von synchronous = OFF weisen wir SQLite an, die Daten einfach zum Schreiben an das Betriebssystem weiterzugeben und dann fortzufahren. Es besteht die Möglichkeit, dass die Datenbankdatei beschädigt wird, wenn der Computer einen Absturz (oder Stromausfall) erleidet, bevor die Daten auf den Platter geschrieben werden:

/* 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);

Importierte 864913 Datensätze in 12,41 Sekunden

Die Verbesserungen sind jetzt kleiner, aber wir haben bis zu 69.600 Einfügungen pro Sekunde.

PRAGMA journal_mode = MEMORY

Erwägen Sie, das Rollback-Journal im Speicher zu speichern, indem Sie PRAGMA journal_mode = MEMORY auswerten. Ihre Transaktion ist schneller, aber wenn Sie während einer Transaktion den Strom verlieren oder Ihr Programm abstürzt, kann Ihre Datenbank mit einer unvollständigen Transaktion in einem beschädigten Zustand belassen werden:

/* 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);

Importierte 864913 Datensätze in 13,50 Sekunden

Etwas langsamer als die vorherige Optimierung bei 64.000 Einfügungen pro Sekunde.

PRAGMA synchron = OFF und PRAGMA journal_mode = MEMORY

Kombinieren wir die beiden vorherigen Optimierungen. Es ist ein bisschen riskanter (im Falle eines Absturzes), aber wir importieren nur Daten (ohne eine Bank zu betreiben):

/* 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);

Importierte 864913 Datensätze in 12.00 Sekunden

Fantastisch! Wir können 72.000 Einfügungen pro Sekunde.

Verwenden einer In-Memory-Datenbank

Bauen wir nur zum Spaß auf allen vorherigen Optimierungen auf und definieren den Dateinamen der Datenbank neu, damit wir vollständig im RAM arbeiten:

#define DATABASE ":memory:"

Importierte 864913 Datensätze in 10,94 Sekunden

Es ist nicht sehr praktisch, unsere Datenbank im RAM zu speichern, aber es ist beeindruckend, dass wir 79.000 Einfügungen pro Sekunde. ausführen können

Refactoring von C-Code

Obwohl es sich nicht speziell um eine SQLite-Verbesserung handelt, mag ich die zusätzlichen char* Zuweisungsoperationen in der while -Schleife nicht. Überarbeiten wir diesen Code schnell, um die Ausgabe von strtok() direkt an sqlite3_bind_text() zu übergeben, und lassen Sie den Compiler versuchen, die Dinge für uns zu beschleunigen:

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

Hinweis: Wir verwenden wieder eine echte Datenbankdatei. In-Memory-Datenbanken sind schnell, aber nicht unbedingt praktisch

Importierte 864913 Datensätze in 8,94 Sekunden

Eine leichte Überarbeitung des in unserer Parameterbindung verwendeten Zeichenfolgenverarbeitungscodes hat es uns ermöglicht, 96.700 Einfügungen pro Sekunde. Ich kann mit Sicherheit sagen, dass dies eine Menge ist schnell . Wenn wir beginnen, andere Variablen zu optimieren (d. H. Seitengröße, Indexerstellung usw.), wird dies unser Maßstab sein.

Zusammenfassung (bisher)

Ich hoffe, Sie sind immer noch bei mir! Der Grund, warum wir diesen Weg eingeschlagen haben, ist, dass die Leistung von Masseneinfügungen mit SQLite so stark variiert und es nicht immer offensichtlich ist, an welchen Änderungen Änderungen vorgenommen werden müssen Beschleunigen Sie unseren Betrieb. Mit demselben Compiler (und denselben Compileroptionen), derselben Version von SQLite und denselben Daten haben wir unseren Code und unsere Verwendung von SQLite optimiert, um von einem Worst-Case-Szenario mit 85 Einfügungen pro Sekunde auf über 96.000 Einfügungen pro Sekunde!

CREATE INDEX, dann INSERT vs. INSERT, dann CREATE INDEX

Bevor wir die Leistung von SELECT messen, wissen wir, dass wir Indizes erstellen werden. In einer der folgenden Antworten wurde vorgeschlagen, dass es bei Masseneinfügungen schneller ist, den Index zu erstellen, nachdem die Daten eingefügt wurden (anstatt erst den Index zu erstellen und dann die Daten einzufügen). Lass es uns versuchen:

Index erstellen, dann Daten einfügen

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

Importierte 864913 Datensätze in 18,13 Sekunden

Daten einfügen, dann Index erstellen

...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

Importierte 864913 Datensätze in 13,66 Sekunden

Wie erwartet sind Masseneinfügungen langsamer, wenn eine Spalte indiziert ist, aber es macht einen Unterschied, ob der Index nach dem Einfügen der Daten erstellt wird. Unsere No-Index-Basislinie liegt bei 96.000 Einfügungen pro Sekunde. Wenn Sie zuerst den Index erstellen und dann Daten einfügen, erhalten Sie 47.700 Einfügungen pro Sekunde, während Sie beim erstmaligen Erstellen des Index 63.300 Einfügungen pro Sekunde erhalten.

Ich würde gerne Vorschläge für andere Szenarien machen ... und werde bald ähnliche Daten für SELECT-Abfragen zusammenstellen.