Améliorer la performance INSERT-per-second de SQLite?

optimiser SQLite est délicat. La performance d'une application C peut varier de 85 inserts par seconde à plus de 96 000 inserts par seconde!

Contexte: Nous sommes en utilisant 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és et chargés dans une base de données SQLite pour un traitement ultérieur lorsque l'application est initialisée. SQLite est idéal pour cette situation parce qu'il est rapide, il ne nécessite aucune configuration spécialisée, et la base de données est stockée sur le disque comme un seul fichier.

justification: au départ, j'ai été déçu de la performance que je voyais. il s'avère que les performances de SQLite peuvent varier de manière significative (à la fois pour les inserts en vrac et les sélections) en fonction de la façon dont la base de données est configurée et comment vous utilisez l'API. Ce n'était pas une question insignifiante de comprendre ce que toutes les options et techniques étaient, donc j'ai pensé qu'il était prudent de créer cette entrée wiki communautaire pour partager les résultats avec les lecteurs de débordement de la pile afin d'épargner aux autres les problèmes des mêmes enquêtes.

L'expérience: plutôt que de simplement parler de conseils de performance dans le sens général (i.e. " utilisez une transaction!" ), j'ai pensé qu'il était préférable d'écrire un code C et mesurer réellement l'impact de différentes options. Nous allons commencer avec quelques données simples:

• UN 28 MO fichier texte délimité par TABULATION (environ 865,000 enregistrements) de la complète des horaires de transport en commun de la ville de Toronto
• mon appareil d'essai est un P4 3,60 GHz fonctionnant sous Windows XP.
• le code est compilé avec Visual C++ 2005 comme "Release" avec " Full Optimization" (/Ox) et Favoriser le Code Rapide (/Ot).
• j'utilise le SQLite "Amalgamation", compilé directement dans mon application test. La version SQLite que j'ai est un peu plus ancienne (3.6.7), mais je soupçonne que ces résultats seront comparables à la dernière version (veuillez laisser un commentaire si vous pensez autrement).

écrivons un peu de code!

le Code: un simple programme C qui lit le fichier texte ligne par ligne, divise la chaîne en valeurs et insère ensuite les données dans une base de données SQLite. Dans cette version "baseline" du code, la base de données est créée, mais nous n'allons pas réellement insérer des 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] = ""151900920"";

    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] = ""151900920"";

    /*********************************************/
    /* 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 secondsn", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

---

"Contrôle"

exécuter le code as-is n'effectue en fait aucune opération de base de données, mais cela nous donnera une idée de la vitesse à laquelle le fichier c brut I/O et le traitement des chaînes de caractères les opérations sont.

Importés 864913 enregistrements dans 0.94 secondes

Super! Nous pouvons faire 920 000 inserts par seconde, à condition que nous ne fassions pas d'inserts: -)

---

Le "Scénario De La Pire Éventualité"

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 parce que le SQL sera compilé dans le code VDBE pour chaque insert et chaque insert se produira dans sa propre transaction. à quel point?

Importés 864913 enregistrements dans 9933.61 secondes

Beurk! 2 heures et 45 minutes! C'est seulement 85 inserts par seconde.

utilisant une Transaction

par défaut, SQLite évaluera chaque INSERT / UPDATE statement dans le cadre d'une transaction unique. Si vous exécutez un grand nombre d'inserts, 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);

Importés 864913 enregistrements dans 38.03 secondes

c'est mieux. Simplement envelopper tous nos inserts dans une seule transaction a amélioré nos performances à 23.000 inserts par seconde.

utilisant une déclaration préparée

utiliser une transaction était une énorme amélioration, mais recompiler L'instruction SQL pour chaque insert n'a pas de sens si nous utilisons le même SQL over-and-over. Utilisons sqlite3_prepare_v2 pour compiler notre déclaration SQL une fois et puis lions nos paramètres à cette déclaration en utilisant 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 secondsn", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

Importés 864913 enregistrements dans 16.27 deuxième

Nice! 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é notre performance à 53,000 inserts par seconde.

PRAGMA synchrone = OFF

par défaut, SQLite fera une pause après avoir émis une commande d'écriture au niveau OS. Cela garantit que les données sont écrites sur le disque. En réglant synchronous = OFF , nous vous SQLite de simplement passer les données à L'OS pour l'écriture et puis continuer. Il y a une chance que le fichier de base de données puisse devenir corrompu si l'ordinateur souffre d'un accident catastrophique (ou une panne de courant) avant que les données 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);

Importés 864913 enregistrements dans 12.41 secondes

les améliorations sont maintenant plus petites, mais nous sommes jusqu'à 69,600 inserts par seconde.

PRAGMA journal_mode = MEMORY

envisagez de stocker le journal en mémoire en évaluant PRAGMA journal_mode = MEMORY . Votre transaction sera plus rapide, mais si vous perdez du pouvoir ou si votre programme s'écrase au cours d'une transaction, votre base de données pourrait être laissée dans un État corrompu avec une transaction partiellement terminé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);

Importés 864913 enregistrements dans 13.50 secondes

Un peu plus lent que l'optimisation précédente à 64.000 inserts 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 d'accident), mais nous ne faisons qu'importer des données (pas de 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);

Importés 864913 enregistrements dans 12.00 deuxième

fantastique! On peut faire 72 000 inserts par seconde.

utilisant une base de données en mémoire

juste pour le plaisir, nous allons construire sur toutes les optimisations précédentes et redéfinir le nom de fichier de la base de données donc nous travaillons entièrement en RAM:

#define DATABASE ":memory:"

Importés 864913 enregistrements dans 10.94 secondes

Il n'est pas très pratique de stocker notre base de données en RAM, mais c'est impressionnant que nous puissions effectuer 79 000 inserts par seconde.

Refactoring C Code

bien que ce ne soit pas spécifiquement une amélioration SQLite, Je n'aime pas les opérations supplémentaires char* dans la boucle while . Nous allons rapidement remanier ce code pour passer la sortie de strtok() directement dans sqlite3_bind_text() , et laisser le compilateur essayer de vitesse 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);

Remarque: Nous sommes de retour à l'aide d'un véritable fichier de base de données. Les bases de données en mémoire sont rapides, mais pas nécessairement pratiques

Importés 864913 enregistrements dans 8.94 secondes

un léger remaniement au code de traitement de chaîne utilisé dans notre liaison de paramètre nous a permis d'effectuer 96,700 inserts par seconde. I pense qu'il est sûr de dire que c'est très rapide . Comme nous commençons à modifier d'autres variables (i.e. taille de page, Création d'index, etc.) ce sera notre référence.

---

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

j'espère que vous êtes toujours avec moi! la raison pour laquelle nous avons commencé sur cette route est que la performance des inserts en vrac varie tellement avec SQLite, et il n'est pas toujours évident quels changements doivent être faits pour accélérer notre fonctionnement. En utilisant le même compilateur (et les options de compilateur), la même version de SQLite et les mêmes données, nous avons optimisé notre code et notre utilisation de SQLite pour passer d'un scénario du pire de 85 inserts par seconde à plus de 96.000 inserts par seconde!

---

créer L'INDEX puis insérer vs Insérer puis créer L'INDEX

avant de commencer à mesurer la performance SELECT , nous savons que nous allons créer des index. Il a été suggéré dans l'une des réponses ci-dessous que lorsque vous faites des inserts en vrac, il est plus rapide de créer l'index après que les données ont été insérées (au lieu de créer l'index d'abord puis d'insérer les données). Essayons:

créer L'Index puis insérer les données

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

Importés 864913 enregistrements dans 18.13 secondes

Insérez les données puis créez L'Index

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

Importés 864913 enregistrements dans 13.66 secondes

Comme prévu, en vrac-inserts sont plus lents 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 sans indice est de 96 000 inserts par seconde. la création de l'indice d'abord puis l'insertion des données nous donne 47.700 inserts par seconde, alors que l'insertion des données d'abord, puis la création de l'index nous donne 63.300 inserts par seconde.

---

je prendrais volontiers des suggestions pour d'autres scénarios à essayer... Et compilera bientôt des données similaires pour des requêtes sélectionnées.