3008 votos

¿Cómo puedo mejorar la INSERCIÓN por segundo de rendimiento de SQLite?

Optimizar SQLite es complicado. Bulk-insertar el rendimiento de una aplicación de C pueden variar de 85 inserta por segundo a más de 96 000 inserta por segundo!

Antecedentes: 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 analiza y se carga en una base de datos SQLite para su posterior procesamiento cuando la aplicación se inicia. SQLite es ideal para esta situación ya que es rápido, no requiere ninguna configuración especializada y la base de datos se almacena en el disco como un archivo único.

Justificación: al principio yo estaba decepcionado con el rendimiento que estaba viendo. Resulta que el rendimiento de SQLite puede variar considerablemente (tanto a granel se inserta y se selecciona), dependiendo de la base de datos se configura y cómo se está utilizando la API. No era un asunto trivial para la figura-lo que de todas las opciones y técnicas, así que aunque es prudente crear esta entrada de wiki de la comunidad para compartir los resultados con los lectores con el fin de salvar a otros de los problemas de las mismas investigaciones.

El Experimento: en Lugar de simplemente hablar sobre consejos de interpretación en el sentido general (es decir. "El uso de una transacción!"), Pensé que era mejor escribir algo de código C y medir el impacto de las distintas opciones. Vamos a empezar con algunos datos simples:

  • Un 28 de meg FICHA archivo de texto delimitado (aprox 865000 registros) de la completa tránsito horario para la ciudad de Toronto
  • Mi máquina de prueba es un 3.60 GHz P4 con Windows XP.
  • El código es compilado con MSVC 2005 como "Liberación" de la "Optimización" (/Ox) y a Favor de un Código Rápido (/Ot).
  • Estoy usando SQLite "Amalgama", compilado directamente en mi aplicación de prueba. La versión de SQLite sucede que tengo es un poco mayor (3.6.7), pero sospecho que estos resultados serán comparables a la última versión (por favor, deje un comentario si usted piensa lo contrario).

Permite escribir código!

El Código: Un sencillo programa en C que lee el archivo de texto línea por línea, divide la cadena en valores y, a continuación, se inserta los datos en una base de datos SQLite. En esta "línea de base" de la versión de el código, la base de datos es creada, pero no vamos a insertar 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"

Ejecutando el código tal y como está en realidad no realizar ningún operaciones de base de datos, pero nos da una idea de cómo de rápido las primas archivo C IO y en la cadena de procesamiento de las operaciones.

Importado 864913 registros en 0.94 segundos

Genial!!! Podemos hacer 920 000 inserta por segundo, a condición de que en realidad no hacer cualquier inserta :-)


El "Peor Escenario"

Vamos a generar la cadena SQL utilizando los valores leídos desde el archivo y la invocación de que la operación de SQL utilizando 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);

Este va a ser lento debido a que el SQL se compilarán en VDBE código para cada inserto y cada inserto va a suceder en su propia transacción. ¿Cómo despacio?

Importado 864913 registros en 9933.61 segundos

¡Caramba! 1 hora y 45 minutos! Eso es sólo el 85 inserta por segundo.

Mediante una Transacción

Por defecto SQLite evaluará cada INSERTAR / ACTUALIZAR la declaración dentro de una única transacción. Si la realización de un gran número de inserciones, es recomendable envolver la operación en una transacción que:

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

Importado 864913 registros en 38.03 segundos

Eso está mejor. Simplemente envolver todo de nuestros insertos en una sola transacción mejorado nuestro rendimiento a 23 000 inserta por segundo.

Mediante un comunicado

Mediante una transacción fue una gran mejora, pero volver a compilar la instrucción SQL para cada inserto no tiene sentido si estamos usando el mismo SQL más y más. Vamos a usar sqlite3_prepare_v2 para compilar nuestra instrucción una vez y, a continuación, enlace a nuestros parámetros para que la declaración utilizando 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;

Importado 864913 registros en 16.27 segundos

Bueno! Hay un poco más de código (no olvide llamar a sqlite3_clear_bindings y sqlite3_reset) pero hemos más que duplicado nuestro rendimiento a 53 000 inserta por segundo.

PRAGMA sincrónico = OFF

Por defecto SQLite hará una pausa después de la emisión de un nivel de SO comando de escritura. Esto garantiza que los datos se escriben en el disco. Estableciendo synchronous = OFF, estamos instruyendo a SQLite simplemente a mano a partir de los datos del sistema operativo para escribir y luego continuar. Hay una posibilidad de que el archivo de base de datos pueden resultar dañados si el equipo sufre un catastrófico accidente (o fallo de alimentación) antes de que los datos se escriben 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);

Importado 864913 registros en 12.41 segundos

Las mejoras son ahora más pequeñas, pero estamos hasta el 69 600 inserta por segundo.

PRAGMA journal_mode = MEMORIA

Considere la posibilidad de almacenar la reversión de la revista en la memoria evaluando PRAGMA journal_mode = MEMORY. Su transacción será más rápido, pero si se pierde el poder o el programa se bloquea durante una transacción de base de datos podría quedar en un estado corrupto con un parcial transacción completa:

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

Importado 864913 registros en 13.50 segundos

Un poco más lento que el anterior optimización en 64 000 inserta por segundo.

PRAGMA sincrónico = OFF y PRAGMA journal_mode = MEMORIA

Vamos a combinar los dos anteriores optimizaciones. Es un poco más arriesgado (en caso de un accidente), pero estamos sólo a la importación de datos (que no se ejecuta 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);

Importado 864913 registros en las 12.00 segundos

Fantástico! Somos capaces de hacer 72 000 inserta por segundo.

El uso de una Base de datos En Memoria

Sólo por curiosidad, vamos a construir sobre todo de la anterior optimizaciones y redefinir la base de datos de nombre de archivo para la que estamos trabajando completamente en la memoria RAM:

#define DATABASE ":memory:"

Importado 864913 registros en 10.94 segundos

No es super-práctico para almacenar nuestra base de datos en la memoria RAM, pero es impresionante que podemos realizar de 79 000 inserta por segundo.

Refactorización De Código C

Aunque no son específicamente un SQLite mejora, no me gusta el extra char* de asignación de operaciones en la while de bucle. Vamos rápidamente que refactorizar el código para pasar la salida de strtok() directamente en sqlite3_bind_text() y dejar que el compilador tratar de 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: Estamos de vuelta a la utilización de un verdadero archivo de base de datos. En memoria de bases de datos rápido, pero no necesariamente práctico

Importado 864913 registros en 8.94 segundos

Un ligero rediseño de la cadena de procesamiento de código utilizado en nuestro enlace de parámetros que nos ha permitido realizar 96 700 inserta por segundo. Creo que es seguro decir que este es bastante rápido. Como empezamos a ajustar otras variables (es decir. el tamaño de página, la creación de índices, etc.) este será nuestro punto de referencia.


Resumen (por ahora)

Espero que esté conmigo! La razón por la que comenzamos este camino es que el grueso-insertar el rendimiento varía tan violentamente con SQLite y no siempre es evidente qué cambios deben hacerse para acelerar nuestra operación. Utilizando el mismo compilador (y opciones del compilador), la misma versión de SQLite y los mismos datos que hemos optimizado nuestro código y de nuestro uso de SQLite para ir de un escenario del peor caso de 85 inserta por segundo a más de 96 000 inserta por segundo!


CREAR un ÍNDICE, a continuación, INSERTE vs. INSERTAR, a continuación, CREAR un ÍNDICE

Antes de iniciar la medición SELECT de rendimiento, sabemos que vamos a ser la creación de índices. Se ha sugerido en una de las respuestas más abajo que cuando se realizan inserciones masivas, es más rápido para crear el índice después de que los datos ha sido insertado (en oposición a la creación del índice en primer lugar luego de la inserción de los datos). Vamos a probar:

Crear un Índice, a continuación, Insertar Datos

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

Importado 864913 registros en 18.13 segundos

Insertar los Datos, a continuación, Crear un Índice

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

Importado 864913 registros en 13.66 segundos

Como era de esperar, a granel-inserciones son más lentos si una columna indexada, pero se hace diferencia si el índice es creado después de que los datos que se inserta. Nuestros ningún índice de referencia es de 96 000 insertar-por-segundo. Crear el índice de la primera, a continuación, insertar los datos que nos proporciona 47 700 inserta por segundo, mientras que la inserción de los datos en primer lugar, a continuación, crear el índice nos da 63 300 inserta por segundo.


Me gustaría con mucho gusto sugerencias de otros escenarios para probar... Y va a ser la compilación de datos similares para las consultas de selección de pronto.

792voto

Snazzer Puntos 2688

Varios consejos:

  1. Poner inserciones y actualizaciones en una transacción
  2. Para las anteriores versiones de SQLite - Considerar a menos paranoico modo de diario (pragma journal_mode). Hay NORMAL, y luego allí OFF que puede aumentar significativamente la inserción de la velocidad, si no estás demasiado preocupado acerca de la base de datos, posiblemente, conseguir dañado si el sistema operativo se bloquea. Si la aplicación se bloquea la información debe estar bien. Tenga en cuenta que en las versiones más recientes, la OFF/MEMORY ajustes no son seguros para el nivel de aplicación se bloquea.
  3. Jugando con los tamaños de página que hace una diferencia (PRAGMA page_size). Grandes tamaños de página pueden hacer lecturas y escrituras ir un poco más rápido, ya que los grandes páginas se conservan en la memoria. Tenga en cuenta que más de memoria que utilizará para la base de datos.
  4. Si usted tiene los índices, considere llamar a CREATE INDEX después de hacer todas sus inserciones. Esto es significativamente más rápido que crear el índice y, a continuación, hacer su inserta.
  5. Tienes que ser muy cuidadoso si usted tiene acceso simultáneo a SQLite, como toda la base de datos está bloqueado cuando se escribe se hace, y a pesar de múltiples lectores son posibles, escribe serán bloqueados. Esto ha mejorado un poco con la adición de un WAL en las nuevas versiones de SQLite.
  6. Tome ventaja de ahorrar espacio...bases de datos más pequeñas a ir más rápido. Por ejemplo, si usted tiene clave de pares de valores, tratar de hacer la clave de un INTEGER PRIMARY KEY si es posible, que reemplazará las únicas número de fila de la columna en la tabla.
  7. Si usted está usando múltiples hilos, puedes intentar usar el compartido de caché de la página, lo que permitirá cargar las páginas a ser compartida entre hilos, que puede evitar las costosas llamadas de e/S.

También le he pedido a preguntas similares aquí y aquí.

111voto

ahcox Puntos 1781

Evitar sqlite3_clear_bindings(sentencia);

El código de la prueba de conjuntos de los enlaces cada vez que a través de lo que debería ser suficiente.

La API de C de introducción de SQLite docs dice

Antes de llamar a sqlite3_step() por primera vez o inmediatamente después de sqlite3_reset(), la aplicación puede invocar uno de los sqlite3_bind() interfaces para fijar los valores de los parámetros. Cada llame a sqlite3_bind() reemplaza antes de los enlaces en el mismo parámetro

(ver: sqlite.org/cintro.html). No hay nada en los documentos que la función que dice que se debe llamar es además de la configuración de los enlaces.

Más detalle: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings()

63voto

fearless_fool Puntos 9190

En inserciones masivas

Inspirado por este post y por Stack Overflow pregunta que me llevó de aquí, Es posible insertar varias filas en un momento en una base de datos SQLite? -- He publicado mi primer Git repository:

https://github.com/rdpoor/CreateOrUpdate

que granel carga una serie de ActiveRecords en MySQL, SQLite o PostgreSQL bases de datos. Incluye una opción para ignorar a los registros existentes, se sobrescribe o se producirá un error. Mi rudimentario puntos de referencia muestran una 10x mejora de la velocidad en comparación con las escrituras secuenciales -- YMMV.

Lo estoy usando en el código de producción, donde con frecuencia la necesidad de importación de conjuntos de datos grandes, y estoy bastante contento con ella.

48voto

Leon Puntos 34

Seleccione el rendimiento es la otra cara de la moneda, y que de mayor interés para mí, y la razón por la que el amor SQLite. He visto más de 100.000 selecciona por segundo en mi aplicación de C++, con un triple combinación en un 50 MB tabla. Que, obviamente, después de bastante 'warm-up' de tiempo para obtener las tablas en el Linux de caché de la página, pero aún así es increíble rendimiento!

Históricamente SQLite ha tenido problemas para seleccionar los índices a utilizar para una combinación, pero la situación ha mejorado, hasta el punto en que ya no aviso. Cuidado con el uso de índices es obviamente importante. A veces, simplemente, el nuevo orden de los parámetros en un SELECT declaración puede hacer una gran diferencia.

48voto

Leon Puntos 34

Las importaciones masivas parece funcionar mejor si usted puede pedazo de su INSERCIÓN/ACTUALIZACIÓN de declaraciones. Un valor de 10.000 ha funcionado bien para mí en una mesa con sólo un par de filas, YMMV...

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X