c++ - pagina - Inserciones masivas más rápidas en sqlite3?

sqlite website

Question

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?

Answer 1

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.

Answer 2

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 objetivo. De esa forma, sqlite tendrá menos índices para actualizar por cada fila insertada

Answer 3

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 )

Answer 4

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/ramanujan/.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 sintaxis 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 .

Answer 5

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/

Answer 6

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

Answer 7

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;

Answer 8

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

Answer 9

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

Answer 10

• Aumenta PRAGMA default_cache_size a un número mucho mayor. Esto aumentará 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 compiladas para hacer las inserciones.
• Finalmente, como ya se mencionó, si está dispuesto a renunciar al cumplimiento total de ACID, configure PRAGMA synchronous = OFF; .