Mejore el rendimiento de INSERCIÓN por segundo de SQLite

15 minutos de lectura

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

Fondo: Estamos usando 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 una configuración especializada y la base de datos se almacena en el disco como un solo archivo.

Razón fundamental: Al principio estaba decepcionado con el rendimiento 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é usando la API. No fue un asunto trivial averiguar cuáles eran todas las opciones y técnicas, así que pensé que era prudente crear esta entrada wiki de la comunidad para compartir los resultados con los lectores de Stack Overflow a fin de ahorrarles a otros la molestia de las mismas investigaciones.

El experimento: En lugar de simplemente hablar de consejos de rendimiento en el sentido general (es decir, “¡Usa una transacción!”), pensé que era mejor escribir algo de código C y en realidad medir el impacto de varias opciones. Vamos a empezar con unos datos sencillos:

  • Un archivo de texto delimitado por TAB de 28 MB (aproximadamente 865.000 registros) del horario de tránsito completo para la ciudad de Toronto
  • Mi máquina de prueba es una P4 de 3,60 GHz con Windows XP.
  • El código se compila con VisualC++ 2005 como “Release” con “Full Optimization” (/Ox) y Favor Fast Code (/Ot).
  • Estoy usando SQLite “Amalgamation”, compilado directamente en mi aplicación de prueba. La versión de SQLite que tengo es un poco más antigua (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 simple en C 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 de “línea de base” 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ápido son las operaciones de procesamiento de cadena y E/S del archivo C sin formato.

864913 registros importados en 0,94 segundos

¡Estupendo! Podemos hacer 920 000 inserciones por segundo, siempre que no hagamos ninguna 🙂


El “peor escenario”

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 va a ser lento porque el SQL se compilará en código VDBE para cada inserción y cada inserción ocurrirá en su propia transacción. ¿Qué tan lento?

864913 registros importados en 9933.61 segundos

¡Ay! ¡2 horas y 45 minutos! Eso es solo 85 inserciones por segundo.

Usar una transacción

De forma predeterminada, SQLite evaluará cada instrucción INSERTAR / ACTUALIZAR 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);

864913 registros importados en 38,03 segundos

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

Uso de una declaración preparada

Usar una transacción fue una gran mejora, pero volver a compilar la instrucción SQL para cada inserción no tiene sentido si usamos el mismo SQL una y otra vez. usemos sqlite3_prepare_v2 para compilar nuestra declaración SQL una vez y luego vincular 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;

864913 registros importados en 16,27 segundos

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

PRAGMA síncrono = 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. Configurando synchronous = OFF, estamos instruyendo a SQLite para que simplemente transfiera 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 un bloqueo catastrófico (o falla de energía) antes de que los datos se escriban en el disco:

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

864913 registros importados en 12,41 segundos

Las mejoras ahora son más pequeñas, pero estamos a la altura 69.600 inserciones por segundo.

PRAGMA journal_mode = MEMORIA

Considere almacenar el diario de reversión en la memoria evaluando PRAGMA journal_mode = MEMORY. Su transacción será más rápida, pero si pierde energía o su programa se bloquea 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);

864913 registros importados en 13,50 segundos

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

PRAGMA síncrono = APAGADO y PRAGMA journal_mode = MEMORIA

Combinemos las dos optimizaciones anteriores. Es un poco más arriesgado (en caso de un bloqueo), pero solo estamos importando datos (no dirigiendo 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);

864913 registros importados en 12,00 segundos

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

Uso de una base de datos en memoria

Solo por diversión, aprovechemos todas las optimizaciones anteriores y redefinamos el nombre de archivo de la base de datos para que trabajemos completamente en RAM:

#define DATABASE ":memory:"

864913 registros importados en 10,94 segundos

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

Refactorización de código C

Aunque no es específicamente una mejora de SQLite, no me gusta el extra char* operaciones de asignación en el while círculo. Refactoricemos rápidamente ese código para pasar la salida de strtok() directamente en sqlite3_bind_text()y deja que el compilador intente acelerar las cosas para 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 usar un archivo de base de datos real. Las bases de datos en memoria son rápidas, pero no necesariamente prácticas

864913 registros importados 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 rapido. A medida que comencemos a modificar otras variables (es decir, tamaño de página, creación de índices, etc.), este será nuestro punto de referencia.


Resumen (hasta ahora)

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


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

Antes de empezar a medir SELECT rendimiento, sabemos que crearemos índices. Se ha sugerido en una de las respuestas a continuación que cuando se realizan 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);
...

864913 registros importados 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);

864913 registros importados en 13,66 segundos

Como era de esperar, las inserciones masivas son más lentas si se indexa una columna, pero marca la diferencia si el índice se crea después de que se insertan los datos. Nuestra referencia 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.

  • ¡Buen punto! En nuestro caso, estamos tratando con aproximadamente 1,5 millones de pares clave/valor leídos de archivos de texto XML y CSV en 200k registros. Pequeño en comparación con las bases de datos que ejecutan sitios como SO, pero lo suficientemente grande como para que ajustar el rendimiento de SQLite se vuelva importante.

    –Mike Willekes

    10 de noviembre de 2009 a las 22:34

  • “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”. ¿Por qué no mantiene todo en la base de datos sqlite en primer lugar, en lugar de almacenar en XML y luego cargar todo en el momento de la inicialización?

    – CAFxX

    21 de febrero de 2012 a las 8:36

  • ¿Has probado a no llamar? sqlite3_clear_bindings(stmt);? Establece los enlaces cada vez que debería ser suficiente: Antes de llamar a sqlite3_step() por primera vez o inmediatamente después de sqlite3_reset(), la aplicación puede invocar una de las interfaces de sqlite3_bind() para adjuntar valores a los parámetros. Cada llamada a sqlite3_bind() anula los enlaces anteriores en el mismo parámetro (ver: sqlite.org/cintro.html). No hay nada en el documentos para esa función diciendo que debes llamarlo.

    – ahcox

    03/08/2012 a las 19:33

  • ¿Hiciste mediciones repetidas? La “ganancia” de 4s por evitar 7 punteros locales es extraña, incluso asumiendo un optimizador confuso.

    – peterchen

    26 de noviembre de 2012 a las 15:11

  • no usar feof() para controlar la terminación de su bucle de entrada. Usar el resultado devuelto por fgets(). stackoverflow.com/a/15485689/827263

    –Keith Thompson

    3 de noviembre de 2015 a las 22:38

Intenta usar SQLITE_STATIC en vez de SQLITE_TRANSIENT para esos insertos.

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

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

  • Querría ajustar x = 10,000 para que x = caché [= cache_size * page_size] / tamaño promedio de su inserto.

    – Alix Axel

    13 de octubre de 2013 a las 5:10

No pude obtener ninguna ganancia de las transacciones hasta que elevé cache_size a un valor más alto, es decir PRAGMA cache_size=10000;

  • Tenga en cuenta que el uso de un valor positivo para cache_size establece el número de páginas para almacenar en caché, no el tamaño total de RAM. Con el tamaño de página predeterminado de 4 kB, esta configuración contendrá hasta 40 MB de datos por archivo abierto (o por proceso, si se ejecuta con caché compartida).

    – Groo

    23 de enero de 2020 a las 14:44

Evitar sqlite3_clear_bindings(stmt).

El código en la prueba establece los enlaces cada vez que debería ser suficiente.

Él Introducción a la API de C de los documentos de SQLite dice:

Antes de llamar sqlite3_step() por primera vez o inmediatamente después sqlite3_reset()la aplicación puede invocar el
sqlite3_bind() interfaces 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 para sqlite3_clear_bindings diciendo que debe llamarlo además de simplemente establecer los enlaces.

Mas detalle: Evitar_sqlite3_clear_bindings()

  • Tenga en cuenta que el uso de un valor positivo para cache_size establece el número de páginas para almacenar en caché, no el tamaño total de RAM. Con el tamaño de página predeterminado de 4 kB, esta configuración contendrá hasta 40 MB de datos por archivo abierto (o por proceso, si se ejecuta con caché compartida).

    – Groo

    23 de enero de 2020 a las 14:44

La respuesta a su pregunta es que el nuevo SQLite 3 ha mejorado el rendimiento, utilícelo.

esta respuesta ¿Por qué la inserción de SQLAlchemy con sqlite es 25 veces más lenta que usar sqlite3 directamente? por SqlAlchemy Orm Author tiene 100k inserciones en 0.5 segundos, y he visto resultados similares con python-sqlite y SqlAlchemy. Lo que me lleva a creer que el rendimiento ha mejorado con SQLite 3.

¿Ha sido útil esta solución?

Esta web utiliza cookies propias y de terceros para su correcto funcionamiento y para fines analíticos y para mostrarte publicidad relacionada con sus preferencias en base a un perfil elaborado a partir de tus hábitos de navegación. Al hacer clic en el botón Aceptar, acepta el uso de estas tecnologías y el procesamiento de tus datos para estos propósitos. Configurar y más información
Privacidad