Comment améliorer les performances d'INSERT par seconde de SQLite ?

Question

L'optimisation de SQLite est délicate. Les performances d'insertion en masse d'une application C peuvent varier de 85 insertions par seconde à plus de 96 000 insertions par seconde !

Le contexte : Nous utilisons SQLite dans le cadre d'une application de bureau. Nous avons de grandes quantités de données de configuration stockées dans des fichiers XML qui sont analysées et chargées dans une base de données SQLite pour un traitement ultérieur lorsque l'application est initialisée. SQLite est idéal dans cette situation parce qu'il est rapide, qu'il ne nécessite aucune configuration spécialisée et que la base de données est stockée sur le disque dans un seul fichier.

Justification : Au départ, j'étais déçu par la performance que je voyais. Il s'avère que les performances de SQLite peuvent varier de manière significative (tant pour les insertions en masse que pour les sélections) en fonction de la configuration de la base de données et de la manière dont vous utilisez l'API. Il n'a pas été facile de déterminer toutes les options et techniques possibles, aussi ai-je jugé prudent de créer cette entrée wiki communautaire pour partager les résultats avec les lecteurs de l'OS afin d'éviter à d'autres de faire les mêmes recherches.

L'expérience : Plutôt que de parler simplement de conseils de performance au sens général (c'est à dire "Utilisez une transaction !" ), j'ai pensé qu'il valait mieux écrire un peu de code C et mesurer réellement l'impact des différentes options. Nous allons commencer par des données simples :

• Un fichier texte de 28 mégaoctets délimité par des tabulations (environ 865 000 enregistrements) contenant les données suivantes horaire complet des transports en commun de la ville de Toronto
• Ma machine de test est un P4 de 3,60 GHz fonctionnant sous Windows XP.
• Le code est compilé avec MSVC 2005 comme "Release" avec "Full Optimization" (/Ox) et Favor Fast Code (/Ot).
• J'utilise le SQLite "Amalgamation", compilé directement dans mon application de test. La version de SQLite que j'ai est un peu plus ancienne (3.6.7), mais je pense que ces résultats seront comparables à ceux de la dernière version (veuillez laisser un commentaire si vous pensez le contraire).

Écrivons du code !

Le code : Un simple programme C qui lit le fichier texte ligne par ligne, divise la chaîne de caractères en valeurs et insère ensuite les données dans une base de données SQLite. Dans cette version "de base" du code, la base de données est créée mais nous n'insérons pas réellement de données :

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

---

Le "contrôle"

L'exécution du code tel quel n'effectue aucune opération sur la base de données, mais elle nous donne une idée de la rapidité des opérations brutes d'entrée/sortie de fichiers en C et de traitement des chaînes de caractères.

Importation de 864913 enregistrements en 0,94 secondes

Génial ! Nous pouvons faire 920 000 insertions par seconde, à condition que nous ne fassions pas d'insertions :-)

---

Le "pire des scénarios".

Nous allons générer la chaîne SQL en utilisant les valeurs lues dans le fichier et invoquer cette opération SQL en utilisant 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);

Cela va être lent car le SQL sera compilé en code VDBE pour chaque insertion et chaque insertion se fera dans sa propre transaction. Lent comment ?

Importation de 864913 enregistrements en 9933.61 secondes

Oups ! 1 heure et 45 minutes ! C'est seulement 85 insertions par seconde.

Utilisation d'une transaction

Par défaut, SQLite évalue chaque instruction INSERT / UPDATE dans une transaction unique. Si vous effectuez un grand nombre d'insertions, il est conseillé d'envelopper votre opération dans une transaction :

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

Importation de 864913 enregistrements en 38,03 secondes

C'est mieux. Le simple fait de regrouper toutes nos insertions dans une seule transaction a amélioré nos performances. 23 000 insertions par seconde.

Utilisation d'une déclaration préparée

L'utilisation d'une transaction était une amélioration considérable, mais la recompilation de la déclaration SQL pour chaque insertion n'a pas de sens si nous utilisons le même SQL à plusieurs reprises. Utilisons sqlite3_prepare_v2 pour compiler notre déclaration SQL une fois et ensuite lier nos paramètres à cette déclaration en utilisant sqlite3_bind_text :

/* Open input file and import into 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;

Importation de 864913 enregistrements en 16,27 secondes

Joli ! Il y a un peu plus de code (n'oubliez pas d'appeler sqlite3_clear_bindings et sqlite3_reset ), mais nous avons plus que doublé nos performances pour atteindre les objectifs suivants 53 000 insertions par seconde.

PRAGMA synchrone = OFF

Par défaut, SQLite se met en pause après l'émission d'une commande d'écriture au niveau du système d'exploitation. Cela garantit que les données sont écrites sur le disque. En définissant synchronous = OFF nous demandons à SQLite de simplement transmettre les données au système d'exploitation pour qu'il les écrive, puis de continuer. Il est possible que le fichier de la base de données soit corrompu si l'ordinateur subit un crash catastrophique (ou une panne de courant) avant que les données ne soient écrites sur le plateau :

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

Importation de 864913 enregistrements en 12,41 secondes

Les améliorations sont maintenant moins importantes, mais nous en sommes à 69 600 insertions par seconde.

PRAGMA journal_mode = MEMORY

Envisagez de stocker le journal de rollback en mémoire en évaluant PRAGMA journal_mode = MEMORY . Votre transaction sera plus rapide, mais si vous perdez le courant ou si votre programme se bloque pendant une transaction, votre base de données peut se retrouver dans un état corrompu avec une transaction partiellement achevée :

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

Importation de 864913 enregistrements en 13,50 secondes

Un peu plus lent que l'optimisation précédente à 64 000 insertions par seconde.

PRAGMA synchrone = OFF et PRAGMA journal_mode = MEMORY

Combinons les deux optimisations précédentes. C'est un peu plus risqué (en cas de crash), mais nous ne faisons qu'importer des données (nous ne faisons pas tourner une banque) :

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

Importation de 864913 enregistrements en 12,00 secondes

Fantastique ! Nous sommes en mesure de faire 72 000 insertions par seconde.

Utilisation d'une base de données en mémoire

Juste pour le plaisir, basons-nous sur toutes les optimisations précédentes et redéfinissons le nom de fichier de la base de données afin de travailler entièrement en RAM :

#define DATABASE ":memory:"

Importation de 864913 enregistrements en 10,94 secondes

Il n'est pas très pratique de stocker notre base de données dans la RAM, mais il est impressionnant de constater que nous pouvons exécuter 79 000 insertions par seconde.

Refactoring du code C

Bien qu'il ne s'agisse pas d'une amélioration spécifique de SQLite, je n'aime pas l'ajout de l'option char* les opérations d'affectation dans le while boucle. Refactorons rapidement ce code pour passer la sortie de strtok() directement dans sqlite3_bind_text() et laisser le compilateur essayer d'accélérer les choses pour nous :

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

Note : Nous sommes revenus à l'utilisation d'un vrai fichier de base de données. Les bases de données en mémoire sont rapides mais pas nécessairement pratiques.

Importation de 864913 enregistrements en 8,94 secondes

Un léger remaniement du code de traitement des chaînes de caractères utilisé dans notre liaison de paramètres nous a permis d'effectuer les opérations suivantes 96 700 insertions par seconde. Je pense qu'il est sûr de dire que c'est très rapide . Lorsque nous commencerons à modifier d'autres variables (taille des pages, création d'index, etc.), ce sera notre point de référence.

---

Résumé (jusqu'à présent)

J'espère que vous êtes toujours avec moi ! La raison pour laquelle nous nous sommes engagés dans cette voie est que les performances de l'insertion en masse varient énormément avec SQLite et qu'il n'est pas toujours évident de savoir quels changements doivent être apportés pour accélérer notre opération. En utilisant le même compilateur (et les mêmes options de compilation), la même version de SQLite et les mêmes données, nous avons optimisé notre code et notre utilisation de SQLite pour qu'il devienne d'un scénario catastrophe de 85 insertions par seconde à plus de 96 000 insertions par seconde !

---

CREATE INDEX puis INSERT vs. INSERT puis CREATE INDEX

Avant de commencer à mesurer SELECT nous savons que nous allons créer des index. Il a été suggéré dans l'une des réponses ci-dessous que lors d'insertions en masse, il est plus rapide de créer l'index après l'insertion des données (par opposition à la création de l'index d'abord, puis l'insertion des données). Essayons :

Créer un index puis insérer des données

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

Importation de 864913 enregistrements en 18,13 secondes

Insérer des données puis créer un index

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

Importation de 864913 enregistrements en 13,66 secondes

Comme prévu, les insertions en masse sont plus lentes si une colonne est indexée, mais cela fait une différence si l'index est créé après l'insertion des données. Notre base de référence sans indexation est de 96 000 insertions par seconde. En créant d'abord l'index puis en insérant les données, on obtient 47 700 insertions par seconde, tandis qu'en insérant d'abord les données puis en créant l'index, on obtient 63 300 insertions par seconde.

---

Je serais heureux de recevoir des suggestions pour d'autres scénarios à essayer... Et je compilerai bientôt des données similaires pour les requêtes SELECT.

Answer 1

Plusieurs conseils :

1. Mettre les insertions/mises à jour dans une transaction.
2. Pour les anciennes versions de SQLite - Envisagez un mode journal moins paranoïaque ( pragma journal_mode ). Il y a NORMAL et ensuite il y a OFF qui peut augmenter de manière significative la vitesse d'insertion si vous n'avez pas trop peur que la base de données soit corrompue en cas de panne du système d'exploitation. Si votre application tombe en panne, les données devraient être intactes. Notez que dans les versions plus récentes, la fonction OFF/MEMORY ne sont pas sûrs pour les pannes au niveau des applications.
3. Jouer avec la taille des pages fait également une différence ( PRAGMA page_size ). Une taille de page plus importante peut accélérer les lectures et les écritures, car les pages les plus grandes sont conservées en mémoire. Notez que plus de mémoire sera utilisée pour votre base de données.
4. Si vous avez des indices, pensez à appeler CREATE INDEX après avoir fait tous vos inserts. Cette méthode est nettement plus rapide que la création de l'index et l'exécution des insertions.
5. Vous devez être très prudent si vous avez un accès simultané à SQLite, car toute la base de données est verrouillée lorsque des écritures sont effectuées, et bien que plusieurs lecteurs soient possibles, les écritures seront verrouillées. Ce problème a été quelque peu amélioré par l'ajout d'un WAL dans les nouvelles versions de SQLite.
6. Profitez de l'économie d'espace... les petites bases de données vont plus vite. Par exemple, si vous avez des paires clé-valeur, essayez de faire en sorte que la clé soit un fichier INTEGER PRIMARY KEY si possible, qui remplacera la colonne de numéro de ligne unique implicite dans le tableau.
7. Si vous utilisez plusieurs threads, vous pouvez essayer d'utiliser la fonction cache de page partagé ce qui permettra aux pages chargées d'être partagées entre les threads, ce qui peut éviter des appels E/S coûteux.
8. N'utilisez pas !feof(file) !

J'ai également posé des questions similaires aquí y aquí .

Answer 2

Essayez d'utiliser SQLITE_STATIC au lieu de SQLITE_TRANSIENT pour ces inserts.

SQLITE_TRANSIENT fera en sorte que SQLite copie les données de la chaîne avant de retourner.

SQLITE_STATIC lui indique que l'adresse mémoire que vous lui avez donnée sera valide jusqu'à ce que la requête soit effectuée (ce qui, dans cette boucle, est toujours le cas). Cela vous permettra d'économiser plusieurs opérations d'allocation, de copie et de désallocation par boucle. C'est peut-être une grande amélioration.

Answer 3

Évitez sqlite3_clear_bindings(stmt) .

Le code dans le test définit les liens à chaque fois, ce qui devrait être suffisant.

El Introduction à l'API C dans la documentation de SQLite :

Avant d'appeler sqlite3_step() pour la première fois ou immédiatement après sqlite3_reset() l'application peut invoquer la fonction sqlite3_bind() interfaces pour attacher des valeurs aux paramètres. Chaque appel à sqlite3_bind() remplace les liaisons précédentes sur le même paramètre

Il n'y a rien dans la documentation pour sqlite3_clear_bindings disant que vous devez l'appeler en plus de simplement définir les liaisons.

Plus de détails : Éviter_sqlite3_clear_bindings()

Answer 4

Sur les encarts en vrac

Inspiré par ce billet et par la question Stack Overflow qui m'a conduit ici -- Est-il possible d'insérer plusieurs lignes à la fois dans une base de données SQLite ? -- J'ai posté mon premier Git dépôt :

https://github.com/rdpoor/CreateOrUpdate

qui charge en vrac un tableau d'ActiveRecords dans l'application MySQL SQLite ou PostgreSQL bases de données. Il comprend une option permettant d'ignorer les enregistrements existants, de les écraser ou de déclencher une erreur. Mes benchmarks rudimentaires montrent une amélioration de la vitesse de 10x par rapport aux écritures séquentielles -- YMMV.

Je l'utilise dans un code de production où je dois fréquemment importer de grands ensembles de données, et j'en suis assez satisfait.

Answer 5

Les importations en vrac semblent donner de meilleurs résultats si vous pouvez regrouper votre INSERT/UPDATE déclarations. Une valeur d'environ 10 000 a bien fonctionné pour moi sur une table avec seulement quelques lignes, YMMV...