Mejore el rendimiento de INSERT por segundo de SQLite

Optimizar SQLite es complicado. El rendimiento de inserción masiva de una aplicación C puede variar desde 85 inserciones por segundo hasta más de 96.000 inserciones por segundo.

Antecedentes: estamos utilizando SQLite como parte de una aplicación de escritorio. Tenemos grandes cantidades de datos de configuración almacenados en archivos XML que se analizan y cargan en una base de datos SQLite para su posterior procesamiento cuando se inicializa la aplicación. SQLite es ideal para esta situación porque es rápido, no requiere configuración especializada y la base de datos se almacena en el disco como un único archivo.

Justificación: Al principio me decepcionó el desempeño que estaba viendo. Resulta que el rendimiento de SQLite puede variar significativamente (tanto para inserciones masivas como para selecciones) dependiendo de cómo esté configurada la base de datos y cómo esté utilizando la API. No era una cuestión trivial descubrir cuáles eran todas las opciones y técnicas, por lo que pensé que sería prudente crear esta entrada wiki de la comunidad para compartir los resultados con los lectores de Stack Overflow para ahorrarles a otros la molestia de las mismas investigaciones.

El experimento: en lugar de simplemente hablar sobre consejos de rendimiento en el sentido general (es decir, "¡Usa una transacción!" ), pensé que sería mejor escribir algo de código C y medir el impacto de varias opciones. Vamos a empezar con algunos datos simples:

• Un archivo de texto delimitado por TAB de 28 MB (aproximadamente 865 000 registros) del horario de tránsito completo de la ciudad de Toronto
• Mi máquina de prueba es una P4 de 3,60 GHz con Windows XP.
• El código está compilado con Visual C++ 2005 como "Versión" con "Optimización completa" (/Ox) y Favor Fast Code (/Ot).
• Estoy usando la "Amalgamación" de SQLite, compilada directamente en mi aplicación de prueba. La versión de SQLite que tengo es un poco anterior (3.6.7), pero sospecho que estos resultados serán comparables a la última versión (deje un comentario si piensa lo contrario).

¡Escribamos algo de código!

El código: un programa C simple que lee el archivo de texto línea por línea, divide la cadena en valores y luego inserta los datos en una base de datos SQLite. En esta versión "básica" del código, se crea la base de datos, pero en realidad no insertaremos datos:

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

El control"

Ejecutar el código tal como está en realidad no realiza ninguna operación de base de datos, pero nos dará una idea de qué tan rápidas son las operaciones de procesamiento de cadenas y de E/S del archivo C sin formato.

Importó 864913 registros en 0,94 segundos.

¡Excelente! Podemos hacer 920.000 inserciones por segundo, siempre que no hagamos ninguna inserción :-)

El "peor de los casos"

Vamos a generar la cadena SQL usando los valores leídos del archivo e invocaremos esa operación SQL usando 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);

Esto será lento porque el SQL se compilará en código VDBE para cada inserción y cada inserción se realizará en su propia transacción. ¿Qué tan lento?

Importó 864913 registros en 9933,61 segundos.

¡Ay! ¡2 horas y 45 minutos! Eso es sólo 85 inserciones por segundo.

Usando una transacción

De forma predeterminada, SQLite evaluará cada instrucción INSERT/UPDATE dentro de una transacción única. Si realiza una gran cantidad de inserciones, es recomendable envolver su operación en una transacción:

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ó 864913 registros en 38,03 segundos.

Eso es mejor. Simplemente empaquetar todas nuestras inserciones en una sola transacción mejoró nuestro rendimiento a 23.000 inserciones por segundo.

Usando una declaración preparada

Usar una transacción fue una gran mejora, pero recompilar la declaración SQL para cada inserción no tiene sentido si usamos el mismo SQL una y otra vez. Usemos sqlite3_prepare_v2para compilar nuestra declaración SQL una vez y luego vinculemos nuestros parámetros a esa declaración usando 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;

Importó 864913 registros en 16,27 segundos.

¡Lindo! Hay un poco más de código (no olvide llamar sqlite3_clear_bindingsy sqlite3_reset), pero hemos más que duplicado nuestro rendimiento a 53.000 inserciones por segundo.

PRAGMA sincrónico = APAGADO

De forma predeterminada, SQLite se detendrá después de emitir un comando de escritura a nivel del sistema operativo. Esto garantiza que los datos se escriban en el disco. Al configurar synchronous = OFF, le estamos indicando a SQLite que simplemente entregue los datos al sistema operativo para escribirlos y luego continuar. Existe la posibilidad de que el archivo de la base de datos se corrompa si la computadora sufre una falla catastrófica (o un corte de energía) antes de que los datos se escriban en el plato:

/* 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ó 864913 registros en 12,41 segundos.

Las mejoras ahora son menores, pero llegamos a 69.600 inserciones por segundo.

PRAGMA modo_diario = MEMORIA

Considere almacenar el diario de reversión en la memoria mediante la evaluación PRAGMA journal_mode = MEMORY. Su transacción será más rápida, pero si se corta la energía o su programa falla durante una transacción, su base de datos podría quedar en un estado corrupto con una transacción parcialmente completada:

/* 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ó 864913 registros en 13,50 segundos.

Un poco más lento que la optimización anterior con 64.000 inserciones por segundo.

PRAGMA sincrónico = APAGADO y PRAGMA journal_mode = MEMORIA

Combinemos las dos optimizaciones anteriores. Es un poco más riesgoso (en caso de una falla), pero solo estamos importando datos (no administrando un banco):

/* 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ó 864913 registros en 12,00 segundos.

¡Fantástico! Somos capaces de hacer 72.000 inserciones por segundo.

Usando una base de datos en memoria

Solo por diversión, vamos a desarrollar todas las optimizaciones anteriores y redefinir el nombre del archivo de la base de datos para que trabajemos completamente en RAM:

#define DATABASE ":memory:"

Importó 864913 registros en 10,94 segundos.

No es muy práctico almacenar nuestra base de datos en la RAM, pero es impresionante que podamos realizar 79.000 inserciones por segundo.

Refactorización del código C

Aunque no es específicamente una mejora de SQLite, no me gustan las char*operaciones de asignación adicionales en el whilebucle. Refactoricemos rápidamente ese código para pasar la salida de strtok()directamente a sqlite3_bind_text()y dejemos que el compilador intente acelerar las cosas por nosotros:

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

Nota: Volvemos a utilizar un archivo de base de datos real. Las bases de datos en memoria son rápidas, pero no necesariamente prácticas

Importó 864913 registros en 8,94 segundos.

Una ligera refactorización del código de procesamiento de cadenas utilizado en nuestro enlace de parámetros nos ha permitido realizar 96.700 inserciones por segundo. Creo que es seguro decir que esto es bastante rápido . A medida que empecemos a modificar otras variables (es decir, tamaño de página, creación de índice, etc.), este será nuestro punto de referencia.

Resumen (hasta ahora)

Espero que todavía estés conmigo! La razón por la que comenzamos por este camino es que el rendimiento de la inserción masiva varía enormemente con SQLite, y no siempre es obvio qué cambios se deben realizar para acelerar nuestra operación. Utilizando el mismo compilador (y opciones del compilador), la misma versión de SQLite y los mismos datos, hemos optimizado nuestro código y nuestro uso de SQLite para pasar del peor de los casos de 85 inserciones por segundo a más de 96.000 inserciones por segundo.

CREAR ÍNDICE y luego INSERTAR vs. INSERTAR y luego CREAR ÍNDICE

Antes de comenzar a medir SELECTel desempeño, sabemos que crearemos índices. En una de las respuestas a continuación se sugiere que al realizar inserciones masivas, es más rápido crear el índice después de que se hayan insertado los datos (en lugar de crear el índice primero y luego insertar los datos). Intentemos:

Crear índice y luego insertar datos

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

Importó 864913 registros en 18,13 segundos.

Insertar datos y luego crear índice

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

Importó 864913 registros en 13,66 segundos.

Como era de esperar, las inserciones masivas son más lentas si se indexa una columna, pero hay una diferencia si el índice se crea después de insertar los datos. Nuestra línea de base sin índice es de 96.000 inserciones por segundo. Crear el índice primero y luego insertar los datos nos da 47.700 inserciones por segundo, mientras que insertar los datos primero y luego crear el índice nos da 63.300 inserciones por segundo.

Con mucho gusto aceptaré sugerencias para probar otros escenarios... Y pronto compilaré datos similares para consultas SELECT.

Nov 11 '09 05:11 Peter Mortensen

Intente usarlo SQLITE_STATICen lugar de SQLITE_TRANSIENTpara esas inserciones.

SQLITE_TRANSIENThará que SQLite copie los datos de la cadena antes de regresar.

SQLITE_STATICle dice que la dirección de memoria que le proporcionó será válida hasta que se haya realizado la consulta (que en este ciclo es siempre el caso). Esto le ahorrará varias operaciones de asignación, copia y desasignación por bucle. Posiblemente una gran mejora.

Sep 17 '2014 16:09 Alexander Farber

Evitar sqlite3_clear_bindings(stmt).

El código de la prueba establece los enlaces cada vez, lo cual debería ser suficiente.

La introducción de la API de C de los documentos de SQLite dice:

Antes de llamar a sqlite3_step() por primera vez o inmediatamente después de sqlite3_reset() , la aplicación puede invocar las interfaces sqlite3_bind() para adjuntar valores a los parámetros. Cada llamada a sqlite3_bind() anula los enlaces anteriores en el mismo parámetro

No hay nada en los documentos que sqlite3_clear_bindingsdiga que debe llamarlo además de simplemente configurar los enlaces.

Más detalles: Evite_sqlite3_clear_bindings()

Aug 08 '2012 14:08 ahcox