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

Le fractionnement de la tâche en plusieurs transactions comme l'a fait @Jimmy_A est la solution. Sinon, vous risquez de saturer votre RAM avec une transaction monstre et une tâche COMMIT lourde.

Pour améliorer les performances, vous pouvez également activer le cache en écriture sur votre disque dur si vous utilisez un système alimenté par batterie (ordinateur portable, onduleur, contrôleur RAID avec batterie...).

Answer 2

Utilisez ContentProvider pour insérer les données en masse dans la base de données. La méthode ci-dessous est utilisée pour insérer des données en masse dans la base de données. Cela devrait améliorer les performances d'INSERT par seconde de 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();

}

Appelez la méthode bulkInsert :

App.getAppContext().getContentResolver().bulkInsert(contentUriTable,
            contentValuesArray);

Lien : https://www.vogella.com/tutorials/AndroidSQLite/article.html consultez la section Using ContentProvider pour plus de détails.