Inserciones masivas más rápidas en sqlite3?

Tengo un archivo de aproximadamente 30000 líneas de datos que quiero cargar en una base de datos sqlite3. ¿Hay una forma más rápida que generar instrucciones de inserción para cada línea de datos?

Los datos están delimitados por espacios y se asignan directamente a una tabla sqlite3. ¿Hay algún tipo de método de inserción masiva para agregar datos de volumen a una base de datos?

¿Alguien ha ideado alguna manera misteriosamente maravillosa de hacer esto si no está integrado?

Debo preceder esto preguntando, ¿hay alguna forma C ++ para hacerlo desde la API?

También puede intentar ajustar algunos parámetros para obtener mayor velocidad. Específicamente, probablemente desee PRAGMA synchronous = OFF; .

  • Envuelva todos los INSERT en una transacción, incluso si hay un solo usuario, es mucho más rápido.
  • usar declaraciones preparadas.

Desea utilizar el comando .import . Por ejemplo:

 $ cat demotab.txt 44 92 35 94 43 94 195 49 66 28 135 93 135 91 67 84 135 94 $ echo "create table mytable (col1 int, col2 int);" | sqlite3 foo.sqlite $ echo ".import demotab.txt mytable" | sqlite3 foo.sqlite $ sqlite3 foo.sqlite -- Loading resources from /Users/twignujan/.sqliterc SQLite version 3.6.6.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select * from mytable; col1 col2 44 92 35 94 43 94 195 49 66 28 135 93 135 91 67 84 135 94 

Tenga en cuenta que este comando de carga masiva no es SQL, sino que es una función personalizada de SQLite. Como tal, tiene una syntax extraña porque la pasamos por echo al intérprete de línea de comando interactivo, sqlite3 .

En PostgreSQL, el equivalente es COPY FROM : http://www.postgresql.org/docs/8.1/static/sql-copy.html

En MySQL está LOAD DATA LOCAL INFILE : http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Una última cosa: recuerde tener cuidado con el valor de .separator . Ese es un problema muy común al hacer inserciones a granel.

 sqlite> .show .separator echo: off explain: off headers: on mode: list nullvalue: "" output: stdout separator: "\t" width: 

Debería establecer explícitamente el separador para que sea un espacio, una pestaña o una coma antes de hacer .import .

  • Aumenta PRAGMA default_cache_size a un número mucho mayor. Esto boostá el número de páginas almacenadas en la memoria caché.

  • Envuelva todas las inserciones en una sola transacción en lugar de una transacción por fila.

  • Use declaraciones SQL comstackdas para hacer las inserciones.
  • Finalmente, como ya se mencionó, si está dispuesto a renunciar al cumplimiento total de ACID, configure PRAGMA synchronous = OFF; .

RE: “¿Hay una manera más rápida de generar declaraciones de inserción para cada línea de datos?”

Primero: ciérrelo a 2 declaraciones SQL haciendo uso de la API de la tabla virtual de Sqlite3, por ej.

 create virtual table vtYourDataset using yourModule; -- Bulk insert insert into yourTargetTable (x, y, z) select x, y, z from vtYourDataset; 

La idea aquí es que implemente una interfaz C que lea su conjunto de datos de origen y lo presente a SQlite como una tabla virtual y luego haga una copia de SQL del origen a la tabla de destino de una vez. Suena más difícil de lo que realmente es y he medido las mejoras de gran velocidad de esta manera.

Segundo: utilice los otros consejos provistos aquí, es decir, los ajustes de pragma y haciendo uso de una transacción.

Tercero: Tal vez vea si puede eliminar algunos de los índices en la tabla objective. De esa forma, sqlite tendrá menos índices para actualizar por cada fila insertada

No hay forma de insertar de forma masiva, pero hay una forma de escribir grandes fragmentos en la memoria y luego enviarlos a la base de datos. Para la API de C / C ++, simplemente hazlo:

sqlite3_exec (db, “BEGIN TRANSACTION”, NULL, NULL, NULL);

… (instrucciones INSERT)

sqlite3_exec (db, “COMMIT TRANSACTION”, NULL, NULL, NULL);

Suponiendo que db es su puntero a la base de datos.

Un buen compromiso es envolver sus INSERTOS entre BEGIN; y punto; palabra clave es decir:

 BEGIN; INSERT INTO table VALUES (); INSERT INTO table VALUES (); ... END; 

Dependiendo del tamaño de los datos y la cantidad de RAM disponible, una de las mejores ganancias de rendimiento ocurrirá al configurar sqlite para usar una base de datos todo en memoria en lugar de escribir en el disco.

Para las bases de datos en memoria, pase NULL como el argumento de nombre de archivo a sqlite3_open y asegúrese de que TEMP_STORE esté definido apropiadamente

(Todo el texto anterior se extrae de mi propia respuesta a una pregunta separada relacionada con sqlite )

Si solo estás insertando una vez, es posible que tenga un truco sucio para ti.

La idea es simple, primero insertando en una base de datos de memoria, luego haciendo una copia de seguridad y finalmente restaurando a su archivo de base de datos original.

Escribí los pasos detallados en mi blog . 🙂

Encontré que esta es una buena combinación para una importación larga.

 .echo ON .read create_table_without_pk.sql PRAGMA cache_size = 400000; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA count_changes = OFF; PRAGMA temp_store = MEMORY; PRAGMA auto_vacuum = NONE; .separator "\t" .import a_tab_seprated_table.txt mytable BEGIN; .read add_indexes.sql COMMIT; .exit 

fuente: http://erictheturtle.blogspot.be/2009/05/fastest-bulk-import-into-sqlite.html

alguna información adicional: http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/

Intereting Posts