c# - tabla - ¿Cuál es la forma más rápida de insertar 100 000 registros de una base de datos a otra?

enlazar base de datos sql con c#

Question

Tengo una aplicación móvil. Mi cliente tiene un gran conjunto de datos ~ 100.000 registros. Se actualiza con frecuencia. Cuando sincronizamos necesitamos copiar de una base de datos a otra.

He adjuntado la segunda base de datos a la principal, y insert into table select * from sync.table una insert into table select * from sync.table .

Esto es extremadamente lento, creo que toma unos 10 minutos. Noté que el archivo de diario se incrementa paso a paso.

¿Cómo puedo acelerar esto?

EDITADO 1

Tengo los índices desactivados y el diario desactivado. Utilizando

insert into table select * from sync.table

todavía lleva 10 minutos.

EDITADO 2

Si ejecuto una consulta como

select id,invitem,invid,cost from inventory where itemtype = 1
order by invitem limit 50 

se tarda 15-20 segundos

El esquema de la tabla es:

CREATE TABLE inventory
(''id'' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
''serverid'' INTEGER NOT NULL DEFAULT 0,
''itemtype'' INTEGER NOT NULL DEFAULT 0,
''invitem'' VARCHAR,
''instock'' FLOAT NOT NULL DEFAULT 0,
''cost'' FLOAT NOT NULL DEFAULT 0,
''invid'' VARCHAR,
''categoryid'' INTEGER DEFAULT 0,
''pdacategoryid'' INTEGER DEFAULT 0,
''notes'' VARCHAR,
''threshold'' INTEGER NOT NULL DEFAULT 0,
''ordered'' INTEGER NOT NULL DEFAULT 0,
''supplier'' VARCHAR,
''markup'' FLOAT NOT NULL DEFAULT 0,
''taxfree'' INTEGER NOT NULL DEFAULT 0,
''dirty'' INTEGER NOT NULL DEFAULT 1,
''username'' VARCHAR,
''version'' INTEGER NOT NULL DEFAULT 15
)

Los índices se crean como

CREATE INDEX idx_inventory_categoryid ON inventory (pdacategoryid);
CREATE INDEX idx_inventory_invitem ON inventory (invitem);
CREATE INDEX idx_inventory_itemtype ON inventory (itemtype);

Me pregunto, ¿el inserto en ... select * from no es la forma más rápida de hacer una copia masiva de datos?

EDITADO 3

SQLite no tiene servidor, así que deje de votar una respuesta en particular, porque esa no es la respuesta, estoy seguro.

Answer 1

¿Qué pasa con el almacenamiento de la tabla de base de datos sync.table dentro de un archivo separado? De esa manera solo necesitas hacer una copia de ese archivo para sincronizar. Apuesto a que es mucho más rápido que la sincronización por SQL.

Answer 2

¿Todos los 100 000 registros cambian muy a menudo? ¿O es un subconjunto que cambia?

Si es así, debería considerar agregar una columna updated_since_last_sync que se marque cuando se realiza una actualización, por lo que durante la próxima sincronización solo copiará los registros que realmente hayan cambiado. Una vez que se copian los registros, establece la columna de bandera de nuevo en cero.

Answer 3

Ahora soy móvil, por lo que no puedo publicar una respuesta muy detallada, pero vale la pena leer esto:

http://sqlite.org/cvstrac/wiki?p=SpeedComparison

Como puede ver, SQLite 3 realiza INSERTs mucho más rápido cuando se usan índices y / o transacciones. Además, INSERTs DE SELECT no parece ser el fuerte de SQLite.

Answer 4

Enviar solo deltas. Es decir, enviar solo diffs. Es decir Enviar solo lo que ha cambiado.

Answer 5

INSERT INTO SELECT * de las bases de datos adjuntas es la opción más rápida disponible en SQLite. Unas cuantas cosas a tener en cuenta.

1. Actas. Asegúrese de que todo está dentro de una transacción. Esto es realmente crítico. Si es solo una declaración SQL, entonces no es importante, pero usted dijo que la revista aumenta "paso a paso", lo que indica que es más de una declaración.

2. Disparadores ¿Tienes desencadenantes corriendo? Esos obviamente podrían afectar el rendimiento.

3. Restricciones ¿Tienes restricciones innecesarias? No puedes deshabilitarlos o eliminarlos / agregarlos nuevamente, así que si son necesarios, no hay mucho que puedas hacer al respecto, pero es algo que debes considerar.

Ya mencionaste que desactivas los índices.

Answer 6

No creo que adjuntar las dos bases de datos y ejecutar INSERT INTO foo (SELECT * FROM bar) sea ​​la forma más rápida de hacerlo. Si está sincronizando entre un dispositivo de mano y un servidor (u otro dispositivo), ¿podría el mecanismo de transporte ser el cuello de botella? ¿O los dos archivos de base de datos ya están en el mismo conjunto de archivos? Si el sistema de archivos en el dispositivo es una memoria flash más lenta, ¿podría ser un cuello de botella?

¿Puede compilar / ejecutar el código de SQLite C sin formato en su dispositivo? (Creo que la fusión de sqlite3 RAW debería compilarse para WinCE / Mobile) Si es así, y está dispuesto:

• Para escribir algo de código C (usando la API SQLite C)
• Aumente el riesgo de pérdida de datos desactivando el registro en diario del disco

Debería ser posible escribir un ejecutable pequeño e independiente para copiar / sincronizar los registros de 100 K entre las dos bases de datos de forma extremadamente rápida.

He publicado algo de lo que aprendí sobre la optimización de inserciones de SQLite aquí: ¿ Mejorar el rendimiento de INSERT-por-segundo de SQLite?

Edición: Intenté esto con código real ...

No conozco todos los pasos involucrados en la construcción de un ejecutable de Windows Mobile, pero la fusión de SQLite3 debería compilarse de inmediato con Visual Studio. Aquí hay un programa main.c muestra que abre dos bases de datos SQLite (ambas deben tener el mismo esquema, ver la instrucción #define TABLE ) y ejecutan una instrucción SELECT y luego unen las filas resultantes con una instrucción INSERT:

/*************************************************************
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** May you find forgiveness for yourself and forgive others.
** May you share freely, never taking more than you give.
**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"
#define SOURCEDB "C://source.sqlite"
#define DESTDB "c://dest.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 * sourceDB;
sqlite3 * destDB;
sqlite3_stmt * insertStmt;
sqlite3_stmt * selectStmt;
char * insertTail = 0;
char * selectTail = 0;
int n = 0;
int result = 0;
char * sErrMsg = 0;
clock_t cStartClock;
char sInsertSQL [BUFFER_SIZE] = "/0";
char sSelectSQL [BUFFER_SIZE] = "/0";
/* Open the Source and Destination databases */
sqlite3_open(SOURCEDB, &sourceDB);
sqlite3_open(DESTDB, &destDB);
/* Risky - but improves performance */
sqlite3_exec(destDB, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(destDB, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
cStartClock = clock(); /* Keep track of how long this took*/
/* Prepared statements are much faster */
/* Compile the Insert statement */
sprintf(sInsertSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(destDB, sInsertSQL, BUFFER_SIZE, &insertStmt, &insertTail);
/* Compile the Select statement */
sprintf(sSelectSQL, "SELECT * FROM TTC LIMIT 100000");
sqlite3_prepare_v2(sourceDB, sSelectSQL, BUFFER_SIZE, &selectStmt, &selectTail);
/* Transaction on the destination database */
sqlite3_exec(destDB, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
/* Execute the Select Statement. Step through the returned rows and bind
each value to the prepared insert statement. Obviously this is much simpler
if the columns in the select statement are in the same order as the columns
in the insert statement */
result = sqlite3_step(selectStmt);
while (result == SQLITE_ROW)
{
sqlite3_bind_text(insertStmt, 1, sqlite3_column_text(selectStmt, 1), -1, SQLITE_TRANSIENT); /* Get Route */
sqlite3_bind_text(insertStmt, 2, sqlite3_column_text(selectStmt, 2), -1, SQLITE_TRANSIENT); /* Get Branch */
sqlite3_bind_text(insertStmt, 3, sqlite3_column_text(selectStmt, 3), -1, SQLITE_TRANSIENT); /* Get Version */
sqlite3_bind_text(insertStmt, 4, sqlite3_column_text(selectStmt, 4), -1, SQLITE_TRANSIENT); /* Get Stop Number */
sqlite3_bind_text(insertStmt, 5, sqlite3_column_text(selectStmt, 5), -1, SQLITE_TRANSIENT); /* Get Vehicle */
sqlite3_bind_text(insertStmt, 6, sqlite3_column_text(selectStmt, 6), -1, SQLITE_TRANSIENT); /* Get Date */
sqlite3_bind_text(insertStmt, 7, sqlite3_column_text(selectStmt, 7), -1, SQLITE_TRANSIENT); /* Get Time */
sqlite3_step(insertStmt); /* Execute the SQL Insert Statement (Destination Database)*/
sqlite3_clear_bindings(insertStmt); /* Clear bindings */
sqlite3_reset(insertStmt); /* Reset VDBE */
n++;
/* Fetch next from from source database */
result = sqlite3_step(selectStmt);
}
sqlite3_exec(destDB, "END TRANSACTION", NULL, NULL, &sErrMsg);
printf("Transfered %d records in %4.2f seconds/n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_finalize(selectStmt);
sqlite3_finalize(insertStmt);
/* Close both databases */
sqlite3_close(destDB);
sqlite3_close(sourceDB);
return 0;
}

En mi máquina de escritorio de Windows, este código copia 100k registros de source.sqlite a dest.sqlite en 1.20 segundos. No sé exactamente qué tipo de rendimiento verá en un dispositivo móvil con memoria flash (pero tengo curiosidad).

Answer 7

Si aún no lo has hecho, necesitas envolverlo en una transacción. Hace una diferencia de velocidad significativa.

Answer 8

Si el objetivo es alguna versión de MS SQL Server, SqlBulkCopy ofrece una inserción eficiente para grandes conjuntos de datos, esto es similar al comando bcp .

También puede deshabilitar / eliminar los índices no agrupados antes de insertarlos y volver a crearlos después.

En SQLite, estos suelen ser bastante rápidos:

.dump ?TABLE? ... Dump the database in an SQL text format
.import FILE TABLE Import data from FILE into TABLE

PRAGMA journal_mode = OFF también: PRAGMA journal_mode = OFF

Para su información, debería poder ejecutar la utilidad de línea de comandos en Windows Mobile si la incluye en su paquete.