10 Pregunta: ¿Mejorar el rendimiento INSERT-por-segundo de SQLite?

pregunta creada en Wed, Apr 24, 2019 12:00 AM

Optimizar SQLite es complicado. El rendimiento de las inserciones masivas de una aplicación C puede variar desde 85 inserciones por segundo hasta 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 se 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 solo archivo.

Fundamento: Inicialmente, me decepcionó 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 de cómo esté utilizando 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 de wiki de la comunidad para compartir los resultados con los lectores de Stack Overflow para salvar a otros el problema 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 lo mejor era escribir un código C y mide realmente el impacto de varias opciones. Vamos a comenzar con algunos datos simples:

  • Un archivo de texto delimitado por TAB de 28 MB (aproximadamente 865,000 registros) del programa completo de tránsito para la ciudad de Toronto
  • Mi máquina de prueba es una P4 de 3.60 GHz que ejecuta Windows XP.
  • El código se compila con Visual C ++ 2005 como " Suelte "con" Optimización completa "(/Ox) y Favorece el código rápido (/Ot).
  • Estoy usando el 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 (por favor, deje un comentario si piensa lo contrario).

¡Vamos a escribir un código!

El Código: Un simple programa 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 "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ápido son las operaciones de procesamiento de cadenas y E /S sin procesar en C.

  

Importado 864913 registros en 0.94   segundos

¡Genial! Podemos hacer 920,000 inserciones por segundo, siempre que no hagamos inserciones :-)


El "peor escenario de caso"

Vamos a generar la cadena SQL utilizando los valores leídos en el 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 el código VDBE para cada inserción y cada inserción ocurrirá en su propia transacción. ¿Qué tan lento?

  

Importado 864913 registros en 9933.61   segundos

¡Ay! 2 horas y 45 minutos! Eso es solo 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);
  

Importado 864913 registros en 38.03   segundos

Eso es mejor. Simplemente envolviendo todas nuestras inserciones en una sola transacción mejoramos nuestro rendimiento a 23,000 inserciones por segundo.

Usando una declaración preparada

El uso de una transacción fue una gran mejora, pero la compilación de la declaració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 unir 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;
  

Importado 864913 registros en 16.27   segundos

¡Bien! Hay un poco más de código (no olvides llamar al sqlite3_clear_bindings y sqlite3_reset), pero hemos duplicado nuestro rendimiento a 53,000 inserciones por segundo.

PRAGMA síncrono = APAGADO

De forma predeterminada, SQLite se pausará después de emitir un comando de escritura a nivel del sistema operativo. Esto garantiza que los datos se escriben en el disco. Al configurar synchronous = OFF, le estamos indicando a SQLite que simplemente entregue los datos al sistema operativo para que los escriba y luego continúe. Existe la posibilidad de que la base de datosel archivo puede corromperse si la computadora sufre un fallo catastrófico (o falla de alimentación) 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);
  

Importado 864913 registros en 12.41   segundos

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

PRAGMA journal_mode = MEMORY

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

Importado 864913 registros en 13.50   segundos

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

PRAGMA synchronous = OFF y PRAGMA journal_mode = MEMORY

Combinemos las dos optimizaciones anteriores. Es un poco más riesgoso (en caso de una falla), pero solo estamos importando datos (no estamos ejecutando 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 12.00   segundos

¡Fantástico! Podemos hacer 72,000 inserciones por segundo.

Usando una base de datos en memoria

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

#define DATABASE ":memory:"
  

Importado 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.

Código C de refactorización

Aunque no es específicamente una mejora de SQLite, no me gustan las operaciones de asignación adicionales char* en el bucle while. Refactorizamos 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 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 utilizar un archivo de base de datos real. Las bases de datos en memoria son rápidas, pero no necesariamente prácticas

  

Importado 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 . Cuando comencemos a ajustar 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 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 deben realizarse para acelerar nuestra operación. Usando el mismo compilador (y las 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 luego INSERTAR versus INSERTAR luego CREAR ÍNDICE

Antes de comenzar a medir el rendimiento de SELECT, sabemos que estaremos creando í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). Probemos:

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

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

Importado 864913 registros en 13.66   segundos

Como era de esperar, las inserciones masivas son más lentas si una columna está indexada, pero hace 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. La creación del índice primero y luego la inserción de datos nos da 47,700 inserciones por segundo, mientras que la inserción de los datos primero y luego la creación del índice nos da 63,300 inserciones por segundo.


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

    
2788
  1. ¡Buen punto! En nuestro caso, estamos tratando con aproximadamente 1.5 millones de pares clave /valor leídos desde archivos de texto XML y CSV a registros de 200k. Pequeño en comparación con las bases de datos que ejecutan sitios como SO, pero lo suficientemente grande para que el ajuste del rendimiento de SQLite sea importante.
    2009-11-10 22: 34: 59Z
  2. "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 procesamiento posterior cuando la aplicación se inicializa." ¿Por qué no guarda 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?
    2012-02-21 08: 36: 48Z
  3. ¿Ha intentado no llamar al sqlite3_clear_bindings(stmt);? Usted establece los enlaces cada vez que debe 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 (consulte: sqlite.org/cintro.html ). No hay nada en los documentos para esa función que diga que debe llamarlo.
    2012-08-03 19: 33: 20Z
  4. ahcox: el enlace es a la dirección apuntada y no a la variable, por lo que no funcionaría ya que strtok devuelve un nuevo puntero cada vez. Tendrías que llamar al strcpy después de cada strtok o hacer tu propio tokenizador que siempre se copia a medida que se lee a lo largo de la cadena.
    2012-09-02 15: 20: 05Z
  5. ¿Hiciste mediciones repetidas? La "victoria" de los 4 para evitar 7 punteros locales es extraña, incluso suponiendo un optimizador confuso.
    2012-11-26 15: 11: 34Z
10 Respuestas                              10                         

Varios consejos:

  1. Poner inserciones /actualizaciones en una transacción.
  2. Para versiones anteriores de SQLite: considere un modo de diario menos paranoico (pragma journal_mode). Hay NORMAL, y luego hay OFF, que puede aumentar significativamente la velocidad de inserción si no está demasiado preocupado por la posibilidad de que la base de datos se corrompa si el sistema operativo falla. Si su aplicación falla, los datos deberían estar bien. Tenga en cuenta que en las versiones más recientes, la configuración OFF/MEMORY no es segura para bloqueos de nivel de aplicación.
  3. Jugar con tamaños de página también marca la diferencia (PRAGMA page_size). Tener tamaños de página más grandes puede hacer que las lecturas y escrituras sean un poco más rápidas, ya que las páginas más grandes se guardan en la memoria. Tenga en cuenta que se utilizará más memoria para su base de datos.
  4. Si tiene índices, considere llamar al CREATE INDEX después de hacer todas sus inserciones. Esto es significativamente más rápido que crear el índice y luego hacer las inserciones.
  5. Debe tener mucho cuidado si tiene acceso simultáneo a SQLite, ya que toda la base de datos está bloqueada cuando se realizan las escrituras, y aunque son posibles varios lectores, las escrituras se bloquearán. Esto se ha mejorado algo con la adición de un WAL en las versiones más recientes de SQLite.
  6. Aproveche el ahorro de espacio ... las bases de datos más pequeñas van más rápido. Por ejemplo, si tiene pares de valores clave, intente convertir la clave en INTEGER PRIMARY KEY si es posible, lo que reemplazará la columna de número de fila único implícita en la tabla.
  7. Si está utilizando varios subprocesos, puede intentar usar el caché de página compartida , que permite que las páginas cargadas se compartan entre subprocesos, lo que puede evitar costosas llamadas de E /S.
  8. ¡No uses !feof(file)!

También hice preguntas similares here y aquí .

    
725
2018-06-04 15: 07: 47Z
  1. Los documentos no conocen un PRAGMA journal_mode NORMAL sqlite.org/pragma.html#pragma_journal_mode
    2014-01-31 08: 52: 57Z
  2. Ha pasado un tiempo, mis sugerencias se aplicaron a versiones anteriores antes de que se introdujera un WAL. Parece que BORRAR es la nueva configuración normal, y ahora también hay configuraciones de APAGADO y MEMORIA. Supongo que OFF /MEMORY mejorará el rendimiento de escritura a expensas de la integridad de la base de datos, y OFF deshabilita las reversiones por completo.
    2014-01-31 14: 13: 04Z
  3. para # 7, ¿tiene un ejemplo sobre cómo habilitar caché de página compartida usando el contenedor c # system.data.sqlite?
    2015-08-13 22: 37: 44Z
  4. # 4 recuperó viejos recuerdos: hubo al menos un caso en los tiempos anteriores en los que se eliminó un índice antes de que un grupo de agregados y se volviera a crear Posteriormente aceleró las inserciones significativamente. Aún puede funcionar más rápido en los sistemas modernos para algunos agregados donde sabe que tiene acceso exclusivo a la tabla para el período.
    2016-08-24 19: 46: 28Z

Intente utilizar SQLITE_STATIC en lugar de SQLITE_TRANSIENT para esas inserciones.

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 realice la consulta (que en este bucle siempre es el caso). Esto le ahorrará varias operaciones de asignación, copia y desasignación por bucle. Posiblemente una gran mejora.

    
121
2015-08-28 16: 54: 11Z

Evita sqlite3_clear_bindings (stmt);

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

La introducción de la API C de los documentos SQLite 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   Las interfaces sqlite3_bind () para adjuntar valores a los parámetros. Cada   call to sqlite3_bind () anula los enlaces anteriores en el mismo parámetro

(consulte: sqlite.org/cintro.html ). No hay nada en los documentos para esa función diciendo que debe llamarlo además simplemente estableciendo los enlaces.

Más detalles: Avoid_sqlite3_clear_bindings ()     

92
2019-06-08 06: 37: 36Z
  1. Maravillosamente correcto: "Al contrario de la intuición de muchos, sqlite3_reset () no restablece los enlaces en una declaración preparada. Use esta rutina para restablecer todos los parámetros del host a NULL. " - sqlite.org/c3ref/clear_bindings.html
    2016-11-02 14: 09: 15Z

En inserciones masivas

Inspirado por esta publicación y por la pregunta de desbordamiento de pila que me condujo aquí - ¿Es posible insertar varias filas a la vez en una base de datos SQLite? - He publicado mi primer repositorio Git :

https://github.com/rdpoor/CreateOrUpdate

qué carga masiva de una matriz de ActiveRecords en MySQL , SQLite o PostgreSQL . Incluye una opción para ignorar las existentes Registra, sobrescríbelos o genera un error. Mis pruebas de referencia rudimentarias muestran una mejora de la velocidad de 10 veces en comparación con las escrituras secuenciales - YMMV.

Lo estoy usando en el código de producción, donde con frecuencia necesito importar grandes conjuntos de datos, y estoy muy contento con él.

    
54
2017-05-23 12: 02: 48Z
  1. @ Jess: si sigues el enlace, verás que se refería a la sintaxis de la inserción de lotes.
    2013-10-15 08: 23: 26Z

Las importaciones masivas parecen funcionar mejor si puede dividir sus declaraciones INSERT /UPDATE . Un valor de aproximadamente 10,000 me ha funcionado bien en una tabla con solo unas pocas filas, YMMV ...

    
45
2012-02-21 08: 30: 20Z
  1. Desearía ajustar x = 10,000 para que x = caché [= tamaño de caché * tamaño de página] /tamaño promedio de su inserción.
    2013-10-13 05: 10: 04Z

Si solo le interesa la lectura, la versión algo más rápida (pero que podría leer datos obsoletos) es leer desde múltiples conexiones desde múltiples subprocesos (conexión por subproceso).

Primero encuentra los artículos, en la tabla:

SELECT COUNT(*) FROM table

luego lea en las páginas (LIMIT /OFFSET):

SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

dónde y se calculan por subproceso, como esto:

int limit = (count + n_threads - 1)/n_threads;

para cada hilo:

int offset = thread_index * limit

Para nuestra pequeña base de datos (200 mb), esto hizo una aceleración del 50-75% (3.8.0.2 de 64 bits en Windows 7). Nuestras tablas están muy poco normalizadas (1000-1500 columnas, aproximadamente 100,000 o más filas).

Demasiados o muy pocos subprocesos no lo harán, necesitas un punto de referencia y perfilarte.

También para nosotros, SHAREDCACHE hizo que el rendimiento fuera más lento, por lo que manualmente puse PRIVATECACHE (porque fue habilitado globalmente para nosotros)

    
37
2019-06-08 06: 36: 48Z

No pude obtener ninguna ganancia de las transacciones hasta que aumenté el tamaño de caché a un valor más alto, es decir, PRAGMA cache_size=10000;

    
27
2015-04-15 09: 47: 49Z

Después de leer este tutorial, intenté implementarlo en mi programa.

Tengo 4-5 archivos que contienen direcciones. Cada archivo tiene aproximadamente 30 millones de registros. Estoy usando la misma configuración que está sugiriendo, pero mi número de INSERTOS por segundo es muy bajo (~ 10.000 registros por segundo).

Aquí es donde su sugerencia falla. Utiliza una sola transacción para todos los registros y una sola inserción sin errores /fallos. Supongamos que está dividiendo cada registro en varias inserciones en diferentes tablas. ¿Qué pasa si se rompe el registro?

El comando ON CONFLICT no se aplica, porque si tiene 10 elementos en un registro y necesita que cada elemento se inserte en una tabla diferente, si el elemento 5 recibe un error de CONSTRAINT, entonces todas las 4 inserciones anteriores también deben ir.

Así que aquí es donde viene la reversión. El único problema con la reversión es que pierdes todas tus inserciones y comienzas desde arriba. ¿Cómo puedes resolver esto?

Mi solución fue usar múltiples transacciones. Comienzo y finalizo una transacción cada 10,000 registros (no pregunte por qué ese número, fue el más rápido que probé). Creé una matriz con un tamaño de 10.000 e inserto los registros exitosos allí. Cuando se produce el error, hago una reversión, comienzo una transacción, inserto los registros de mi matriz, me comprometo y luego comienzo una nueva transacción después del registro roto.

Esta solución me ayudó a evitar los problemas que tengo cuandon tratar con archivos que contienen registros incorrectos /duplicados (tuve un 4% de registros incorrectos).

El algoritmo que creé me ayudó a reducir mi proceso en 2 horas. Proceso de carga final del archivo 1hr 30m que aún es lento pero no comparado con las 4hrs que tomó inicialmente. Logré acelerar las inserciones de 10.000 /s a ​​~ 14.000 /s

Si alguien tiene alguna otra idea sobre cómo acelerarlo, estoy abierto a sugerencias.

ACTUALIZACIÓN :

Además de mi respuesta anterior, debes tener en cuenta que las inserciones por segundo dependen del disco duro que estés usando también. Lo probé en 3 PC diferentes con diferentes discos duros y obtuve enormes diferencias en los tiempos. PC1 (1hr 30m), PC2 (6hrs) PC3 (14hrs), así que comencé a preguntarme por qué sería eso.

Después de dos semanas de investigación y comprobación de múltiples recursos: Disco duro, RAM, caché, descubrí que algunas configuraciones en su disco duro pueden afectar la velocidad de E /S. Al hacer clic en las propiedades en la unidad de salida deseada, puede ver dos opciones en la pestaña general. Opt1: comprime esta unidad, Opt2: permite que los archivos de esta unidad tengan contenidos indexados.

Al desactivar estas dos opciones, las 3 PC tardan aproximadamente el mismo tiempo en finalizar (1 hora y 20 a 40 minutos). Si encuentra inserciones lentas, verifique si su disco duro está configurado con estas opciones. Le ahorrará mucho tiempo y dolores de cabeza al tratar de encontrar la solución

    
18
2018-01-06 11: 07: 06Z
  1. Sugeriré lo siguiente. * Use SQLITE_STATIC vs SQLITE_TRANSIENT para evitar una copia de cadena, debe asegurarse de que la cadena no se modificará antes de que se ejecute la transacción * Use inserción masiva INSERT INTO stop_times VALUES (NULL,?,?,?,?,?,?,?,?,?,? ,?), (NULL,?,?,?,?,?,?,?,?,?), (NULL,?,?,?,?,?,?,?,?,?)?, (NULL) ,?,?,?,?,?,?,?,?,?,?), (NULL,?,?,?,?,?,?,?,?,?)? Mmap el archivo para reducir el número de syscalls.
    2019-02-06 20: 33: 41Z
  2. Haciendo eso puedo importar 5,582,642 registros en 11.51 segundos
    2019-02-06 20: 43: 27Z

La respuesta a su pregunta es que el nuevo sqlite3 ha mejorado el rendimiento, úselo.

Esta respuesta ¿Por qué el inserto SQLAlchemy con sqlite es 25 veces más lento que usar sqlite3 directamente? por SqlAlchemy Orm Author tiene 100k de 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 sqlite3

    
10
2017-06-15 20: 31: 47Z

Use ContentProvider para insertar los datos masivos en db. El siguiente método utilizado para insertar datos masivos en la base de datos. Esto debería mejorar el rendimiento de INSERT-por-segundo 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();

}

Método bulkInsert de llamada:

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

Enlace: https://www.vogella.com/tutorials/AndroidSQLite/article .html consulte Uso de la sección ContentProvider para obtener más detalles

    
- 1
2019-03-12 09: 55: 16Z
fuente colocada aquí