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


Die Optimierung von SQLite ist schwierig. Die Bulk-Insert-Leistung einer C-Anwendung kann von 85 Einsätzen pro Sekunde bis über 96.000 Einfügungen pro Sekunde variieren!

Hintergrund: Wir verwenden SQLite als Teil einer Desktop-Anwendung. Wir haben große Mengen von Konfigurationsdaten in XML-Dateien gespeichert, die analysiert und in eine SQLite-Datenbank zur weiteren Verarbeitung 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 auf der Festplatte als einzelne Datei gespeichert wird.

Begründung:  Anfangs war ich von der Leistung, die ich sah, enttäuscht. Es stellt sich heraus, dass die Leistung von SQLite je nach Konfiguration der Datenbank und Verwendung der API erheblich variieren kann (sowohl bei Masseneinfügungen als auch bei Auswahloperationen). Es war keine triviale Angelegenheit herauszufinden, was alle Optionen und Techniken waren, daher hielt ich es für sinnvoll, diesen Community-Wiki-Eintrag zu erstellen, um die Ergebnisse mit Stack Overflow-Lesern zu teilen, um anderen die gleichen Probleme zu ersparen.

Das Experiment: Anstatt nur über Leistungstipps im allgemeinen Sinn zu sprechen (d. H. "Verwenden Sie eine Transaktion!"), Dachte ich, es wäre am besten, einen C - Code zu schreiben und tatsächlich messen die Auswirkung verschiedener Optionen. Wir beginnen mit einigen einfachen Daten:

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

Lass uns etwas Code schreiben!

Der Code: Ein einfaches C-Programm, das die Textdatei Zeile für Zeile liest, die Zeichenfolge in Werte aufteilt und dann die Daten in eine SQLite-Datenbank einfügt. In dieser "Baseline" -Version des Codes wird die Datenbank erstellt, aber wir fügen keine Daten ein:

/*************************************************************
    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 der Code unverändert ausgeführt wird, führt er keine Datenbankoperationen durch, aber er gibt uns eine Vorstellung davon, wie schnell die rohen C-Datei-E / A- und Zeichenfolgenverarbeitungsoperationen sind.

Importierte 864913 Datensätze in 0,94   Sekunden

Groß! Wir können 920.000 Einsätze pro Sekunde machen, vorausgesetzt, wir machen keine Einsätze :-)


Das "Worst-Case-Szenario"

Wir werden die SQL-Zeichenfolge 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 das SQL für jeden Einfügevorgang in VDBE-Code kompiliert wird und jeder Einfügevorgang in einer eigenen Transaktion erfolgt. Wie langsam?

Importierte 864913 Datensätze in 9933.61   Sekunden

Huch! 2 Stunden und 45 Minuten! Das ist nur 85 Einsätze pro Sekunde.

Eine Transaktion verwenden

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

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 Umhüllen aller Beilagen in einer einzigen Transaktion haben wir unsere Leistung verbessert 23.000 Einsätze pro Sekunde.

Verwenden einer vorbereiteten Anweisung

Die Verwendung einer Transaktion war eine enorme Verbesserung, aber das erneute Kompilieren der SQL-Anweisung für jede Einfügung macht keinen Sinn, wenn wir dieselbe SQL-Anweisung verwenden. Lass uns benutzen sqlite3_prepare_v2 um unsere SQL-Anweisung einmal zu kompilieren und dann unsere Parameter mit dieser Anweisung zu verknüpfen 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;

Importierte 864913 Datensätze in 16.27   Sekunden

Nett! Es gibt ein bisschen mehr Code (vergessen Sie nicht, anzurufen sqlite3_clear_bindings und sqlite3_reset), aber wir haben unsere Leistung mehr als verdoppelt 53.000 Einsätze pro Sekunde.

PRAGMA synchron = AUS

Standardmäßig wird SQLite nach der Ausgabe eines Schreibbefehls auf Betriebssystemebene angehalten. Dies garantiert, dass die Daten auf den Datenträger geschrieben werden. Indem man es einstellt synchronous = OFFWir weisen SQLite an, die Daten einfach zum Schreiben an das Betriebssystem zu senden und dann fortzusetzen. Es besteht die Möglichkeit, dass die Datenbankdatei beschädigt wird, wenn der Computer einen katastrophalen Absturz (oder Stromausfall) erleidet, bevor die Daten auf die Platte 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 sind dabei 69.600 Einsätze pro Sekunde.

PRAGMA journal_mode = MEMORY

Erwägen Sie, das Rollback-Journal im Speicher zu speichern, indem Sie es auswerten PRAGMA journal_mode = MEMORY. Ihre Transaktion wird schneller, aber wenn Sie die Stromversorgung verlieren oder Ihr Programm während einer Transaktion abstürzt, könnte Ihre Datenbank in einem beschädigten Zustand mit einer teilweise abgeschlossenen Transaktion verbleiben:

/* 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 Einsätze pro Sekunde.

PRAGMA synchron = AUS und PRAGMA journal_mode = MEMORY

Lassen Sie uns die vorherigen zwei Optimierungen kombinieren. Es ist ein wenig gefährlicher (im Falle eines Absturzes), aber wir importieren nur Daten (keine Bank):

/* 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 es machen 72.000 Einsätze pro Sekunde.

Verwenden einer In-Memory-Datenbank

Lasst uns für alle Kicks auf alle vorherigen Optimierungen aufbauen und den Dateinamen der Datenbank neu definieren, so dass wir komplett im RAM arbeiten:

#define DATABASE ":memory:"

Importierte 864913 Datensätze in 10.94   Sekunden

Es ist nicht sehr praktisch, unsere Datenbank im Arbeitsspeicher zu speichern, aber es ist beeindruckend, dass wir etwas leisten können 79.000 Einsätze pro Sekunde.

Refactoring C-Code

Obwohl nicht speziell eine SQLite-Verbesserung, mag ich das Extra nicht char*Zuweisungsoperationen in der while Schleife. Lassen Sie uns diesen Code schnell umdefinieren, um die Ausgabe von zu übergeben strtok() direkt in sqlite3_bind_text()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

Ein geringfügiger Refactoring für den String-Verarbeitungscode, der in unserer Parameterbindung verwendet wird, hat uns erlaubt, dies zu tun 96.700 Einsätze pro Sekunde. Ich denke, das ist sicher viel schnell. Wenn wir mit der Optimierung anderer Variablen beginnen (z. B. Seitengröße, Indexerstellung usw.), wird dies unser Maßstab sein.


Zusammenfassung (bisher)

Ich hoffe du bist immer noch bei mir! Der Grund, warum wir diesen Weg eingeschlagen haben, ist die Tatsache, dass die Bulk-Insert-Leistung mit SQLite so stark variiert, und es ist nicht immer klar, welche Änderungen vorgenommen werden müssen, um unsere Arbeit zu beschleunigen. Mit dem gleichen Compiler (und Compiler-Optionen), der gleichen Version von SQLite und den gleichen Daten haben wir unseren Code und unsere Verwendung von SQLite optimiert von einem Worst-Case-Szenario von 85 Inserts pro Sekunde bis über 96.000 Inserts pro Sekunde!


CREATE INDEX dann INSERT vs. INSERT dann CREATE INDEX

Bevor wir anfangen zu messen SELECT Leistung, wir wissen, dass wir Indizes erstellen werden. Es wurde in einer der folgenden Antworten vorgeschlagen, dass es beim Erstellen von Masseneinfügungen schneller ist, den Index nach dem Einfügen der Daten zu erstellen (im Gegensatz zum Erstellen des Indexes und dem Einfügen der Daten). Lass es uns versuchen:

Erstellen Sie Index und 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

Fügen Sie Daten ein und 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 wird, aber es macht einen Unterschied, wenn der Index nach dem Einfügen der Daten erstellt wird. Unsere No-Index-Basislinie beträgt 96.000 Inserts pro Sekunde. Wenn wir zuerst den Index erstellen und dann die Daten einfügen, erhalten wir 47.700 Einträge pro Sekunde. Wenn wir zuerst die Daten einfügen und dann den Index erstellen, erhalten wir 63.300 Einträge pro Sekunde.


Ich würde gerne Vorschläge für andere Szenarien zu versuchen ... Und wird bald ähnliche Daten für SELECT-Abfragen kompilieren.


2568


Ursprung


Antworten:


Einige Tipps:

  1. Fügen Sie Einfügungen / Aktualisierungen in eine Transaktion ein.
  2. Für ältere Versionen von SQLite - Betrachten Sie einen weniger paranoiden Journalmodus (pragma journal_mode). Es gibt NORMALund dann ist da OFF, die die Geschwindigkeit des Einfügevorgangs erheblich erhöhen kann, wenn Sie sich keine Sorgen machen, dass die Datenbank möglicherweise beschädigt wird, wenn das Betriebssystem abstürzt. Wenn Ihre Anwendung abstürzt, sollten die Daten in Ordnung sein. Beachten Sie, dass in neueren Versionen die OFF/MEMORY Einstellungen sind nicht sicher für Abstürze auf Anwendungsebene.
  3. Das Spielen mit Seitengrößen macht ebenfalls einen Unterschied (PRAGMA page_size). Größere Seitengrößen können das Lesen und Schreiben etwas schneller machen, da größere Seiten im Speicher gehalten werden. Beachten Sie, dass mehr Speicher für Ihre Datenbank verwendet wird.
  4. Wenn Sie Indizes haben, erwägen Sie, anzurufen CREATE INDEXnachdem du alle deine Einsätze gemacht hast. Dies ist wesentlich schneller als das Erstellen des Indexes und das anschließende Einfügen von Einfügungen.
  5. Sie müssen sehr vorsichtig sein, wenn Sie gleichzeitig Zugriff auf SQLite haben, da die gesamte Datenbank gesperrt ist, wenn Schreibvorgänge ausgeführt werden, und obwohl mehrere Leser möglich sind, werden Schreibvorgänge gesperrt. Dies wurde durch Hinzufügen einer WAL in neueren SQLite-Versionen etwas verbessert.
  6. Nutzen Sie Platz sparen ... kleinere Datenbanken gehen schneller. Zum Beispiel, wenn Sie Schlüssel-Wert-Paare haben, versuchen Sie, den Schlüssel zu machen INTEGER PRIMARY KEY Wenn möglich, wird die Zeile mit der impliziten eindeutigen Zeilennummer in der Tabelle ersetzt.
  7. Wenn Sie mehrere Threads verwenden, können Sie versuchen, die Cache für gemeinsam genutzte Seiten, wodurch geladene Seiten zwischen Threads geteilt werden können, wodurch teure I / O-Aufrufe vermieden werden können.
  8. Benutze es nicht !feof(file)!

Ich habe auch ähnliche Fragen gestellt Hier und Hier.


672



Versuchen Sie es mit SQLITE_STATIC Anstatt von SQLITE_TRANSIENT für diese Einsätze.

SQLITE_TRANSIENT wird dazu führen, dass SQLite die String-Daten vor der Rückgabe kopiert.

SQLITE_STATIC sagt, dass die Speicheradresse, die Sie angegeben haben, gültig ist, bis die Abfrage ausgeführt wurde (was in dieser Schleife immer der Fall ist). Dies erspart Ihnen mehrere Zuweisungen, Kopieren und Freigeben von Operationen pro Schleife. Möglicherweise eine große Verbesserung.


102



Vermeiden Sie sqlite3_clear_bindings (stmt);

Der Code im Test legt die Bindungen jedes Mal fest, die ausreichen sollten.

Das C API-Intro aus der SQLite-Dokumentation sagt

Vor dem ersten Aufruf von sqlite3_step () oder sofort   Nach sqlite3_reset () kann die Anwendung eines der folgenden aufrufen   sqlite3_bind () -Schnittstellen, um Werte an die Parameter anzuhängen. Jeder   Der Aufruf von sqlite3_bind () überschreibt vorherige Bindungen für denselben Parameter

(sehen: sqlite.org/cintro.html). Es gibt nichts in den Dokumenten für diese Funktion Sie sagen, Sie müssen es zusätzlich zu den Bindings einfach setzen.

Mehr Details: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Auf Masseneinsätzen

Inspiriert von diesem Post und der Stack Overflow Frage, die mich hierher geführt hat - Ist es möglich, mehrere Zeilen gleichzeitig in eine SQLite-Datenbank einzufügen? - Ich habe meine erste gepostet Git Repository:

https://github.com/rdpoor/CreateOrUpdate

welcher Massen lädt ein Array von ActiveRecords in MySQL, SQLite oder PostgreSQL Datenbanken. Es enthält eine Option, um vorhandene Datensätze zu ignorieren, sie zu überschreiben oder einen Fehler zu melden. Meine rudimentären Benchmarks zeigen eine 10-fache Geschwindigkeitssteigerung im Vergleich zu sequentiellen Schreibvorgängen - YMMV.

Ich verwende es im Produktionscode, wo ich häufig große Datensätze importieren muss, und ich bin ziemlich glücklich damit.


47



Bulk-Imports scheinen am besten zu funktionieren, wenn Sie Ihre Chunks erstellen können EINFÜGEN / AKTUALISIEREN Aussagen. Ein Wert von 10.000 oder so hat für mich auf einem Tisch mit nur wenigen Zeilen gut funktioniert, YMMV ...


40



Wenn Sie nur zum Lesen interessiert sind, etwas schneller (aber möglicherweise veraltete Daten lesen) Version ist das Lesen von mehreren Verbindungen aus mehreren Threads (Verbindung per-Thread).

Finde zuerst die Gegenstände in der Tabelle:

 SELECT COUNT(*) FROM table

dann Seiten einlesen (LIMIT / OFFSET)

  SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

wo und werden per-Thread berechnet, so:

int limit = (count + n_threads - 1)/n_threads;

für jeden Thread:

int offset = thread_index * limit

Für unsere kleine (200mb) db machte dies 50-75% Beschleunigung (3.8.0.2 64-Bit unter Windows 7). Unsere Tabellen sind stark nicht normalisiert (1000-1500 Spalten, ungefähr 100.000 oder mehr Zeilen).

Zu viele oder zu wenige Threads werden es nicht tun, Sie müssen sich selbst benchmarken und profilieren.

SHAREDCACHE hat die Leistung auch für uns langsamer gemacht, deshalb habe ich PRIVATECACHE manuell gesetzt (weil es global für uns aktiviert wurde)


32



Ich konnte keinen Gewinn durch Transaktionen erzielen, bis ich cache_size auf einen höheren Wert angehoben habe, d. H. PRAGMA cache_size=10000;


20



Nach dem Lesen dieses Tutorials habe ich versucht, es in mein Programm zu implementieren.

Ich habe 4-5 Dateien, die Adressen enthalten. Jede Datei hat ca. 30 Millionen Datensätze. Ich benutze die gleiche Konfiguration, die Sie vorschlagen, aber meine Anzahl von INSERTs pro Sekunde ist sehr niedrig (~ 10.000 Datensätze pro Sekunde).

Hier schlägt Ihr Vorschlag fehl. Sie verwenden eine einzige Transaktion für alle Datensätze und eine einzelne Einfügung ohne Fehler / Fehler. Angenommen, Sie teilen jeden Datensatz in mehrere Einfügungen für verschiedene Tabellen auf. Was passiert, wenn der Rekord gebrochen ist?

Der Befehl ON CONFLICT trifft nicht zu. Wenn Sie 10 Elemente in einem Datensatz haben und jedes Element in eine andere Tabelle eingefügt werden muss, wenn Element 5 einen CONSTRAINT-Fehler erhält, müssen auch alle vorherigen 4 Einfügungen mitgehen.

Hier kommt der Rollback. Das einzige Problem mit dem Rollback ist, dass Sie alle Ihre Einsätze verlieren und von oben starten. Wie kannst du das lösen?

Meine Lösung war zu verwenden mehrere Transaktionen. Ich beginne und beende eine Transaktion alle 10.000 Datensätze (frag nicht, warum diese Nummer, es war die schnellste, die ich getestet habe). Ich habe ein Array der Größe 10.000 erstellt und die erfolgreichen Datensätze dort eingefügt. Wenn der Fehler auftritt, führe ich einen Rollback aus, beginne eine Transaktion, füge die Datensätze aus meinem Array ein, überführe und beginne dann eine neue Transaktion nach dem fehlerhaften Datensatz.

Diese Lösung hat mir geholfen, die Probleme zu umgehen, die ich im Umgang mit Dateien habe, die fehlerhafte / doppelte Datensätze enthalten (ich hatte fast 4% schlechte Datensätze).

Der Algorithmus, den ich erstellt habe, hat mir geholfen, meinen Prozess um 2 Stunden zu reduzieren. Endgültiger Ladeprozess der Datei 1h 30m, der immer noch langsam ist, aber nicht mit den 4 Stunden verglichen, die er ursprünglich genommen hat. Ich habe es geschafft, die Einsätze von 10.000 / s auf ~ 14.000 / s zu beschleunigen

Wenn jemand andere Ideen zur Beschleunigung hat, bin ich offen für Vorschläge.

AKTUALISIEREN:

Zusätzlich zu meiner obigen Antwort sollten Sie beachten, dass die Anzahl der Einfügungen pro Sekunde auch von der Festplatte abhängt, die Sie verwenden. Ich habe es auf 3 verschiedenen PCs mit verschiedenen Festplatten getestet und dabei massive Unterschiede in den Zeiten bekommen. PC1 (1 Stunde 30 Minuten), PC2 (6 Stunden) PC3 (14 Stunden), also begann ich mich zu fragen, warum das so sein sollte.

Nach zwei Wochen der Recherche und Überprüfung mehrerer Ressourcen: Festplatte, Ram, Cache, habe ich herausgefunden, dass einige Einstellungen auf Ihrer Festplatte die E / A-Rate beeinflussen können. Durch Klicken auf Eigenschaften auf dem gewünschten Ausgabegerät können Sie zwei Optionen auf der Registerkarte "Allgemein" sehen. Opt1: Komprimiere dieses Laufwerk, Opt2: Erlaube den Dateien dieses Laufwerks Inhalte indiziert zu haben.

Wenn Sie diese beiden Optionen deaktivieren, benötigen alle 3 PCs ungefähr die gleiche Zeit zum Beenden (1 Stunde und 20 bis 40 Minuten). Wenn Sie mit langsamen Einfügungen konfrontiert werden, überprüfen Sie, ob Ihre Festplatte mit diesen Optionen konfiguriert ist. Es wird Ihnen viel Zeit und Kopfschmerzen ersparen, um die Lösung zu finden


11



Die Antwort auf Ihre Frage ist, dass neuere sqlite3 die Leistung verbessert hat.

Diese Antwort Warum fügt SQLAlchemy 25 mal langsamer mit sqlite ein als sqlite3 direkt? von SqlAlchemy Orm Autor hat 100k Einsätze in 0,5 Sekunden, und ich habe ähnliche Ergebnisse mit Python-Sqlite und SqlAlchemy gesehen. Das lässt mich glauben, dass sich die Leistung mit sqlite3 verbessert hat


5



Es gibt große Vortragsform Paul Betts, wie er C # machte Akavache so schnell: https://www.youtube.com/watch?v=j7WnQhwBwqA

Vielleicht findest du ein paar Hinweise für dich. Es ist zu lang, um hier eine kurze Zusammenfassung zu machen


3



Seit 3.24 unterstützt SQLite die Anweisung UPSERT.

Siehe "SQL, wie SQLite es versteht" 1 Wenn eine Zeile nicht existiert, wird sie anderweitig eingefügt. Andere Motoren nennen das MERGE.


0