Carga rápida de datos en Access

[Toñico]

Hola a todos, tengo un gran problema de lentitud a la hora de hacer una carga masiva de datos en access, os cuento mi problema.

Estoy usando Delphi 6 y para conectarme con Access uso componentes ADO.

Tengo que cargar datos en una tabla de access desde una tabla de otra base de datos, a la cual sólo puedo acceder usando una libreria que me obliga a leer los registros de forma secuencial. El problema es la cantidad de registros que contiene la tabla, unos 100.000 registros, y que el usuario tiene que esperar a que termine este proceso para continuar trabajando con la aplicación.

La solución que estoy usando hasta ahora es:
1 - Genero un fichero de texto plano con los registros exportados.
2- Usando un componente BatchMove copio el fichero de texto a un fichero DBF con la misma estructura.
3- Uso una secuencia SQL de inserción para grabar los registros en la tabla de access.

Este proceso con 125000 registros tarda menos de 10 minutos en hacerlo.

Si intento grabar directamente registro a registro usando secuencias INSERT contra la tabla de access el proceso me tarda mas de una hora en hacerlo.

Ahora la pregunta ¿Existe alguna forma de agilizar este proceso sin tener que usar ficheros intermedios como estoy haciendo?

Muchas gracias a todos de antemano.

Un saludo.

[Neftali [Germán.Estévez]]

Empezado por Toñico

...Si intento grabar directamente registro a registro usando secuencias INSERT contra la tabla de access el proceso me tarda mas de una hora en hacerlo...

Posiblemente en éste caso, sea más rápido realizarlo desde la tabla (Append y Post con los controles DataAware desactivados) que con un INSERT.

[marcoszorrilla]

A mi un proceso similar Dbf hacia Db (Pardox), 60.000 registros más o menos me tardan 15 minutos, utilizo incluso una barra de progreso.

No ataco directamente la tabla DBf sino que lanzo un SQL Select *.

El post solamente lo hago despues de añadir el último registro.

Pienso que uno de los problemas en los que interviene esta lentitud, son los índices que tenga la tabla destino.

Un Saludo.

[Toñico]

Gracias Neftali, aunque he hecho una prueba de rendimiento haciendo 1000 insert contra 1000 append .. post y es el doble de rápido ejecutando secuencias sql de inserción.

[Toñico]

Gracias Marcoszorrilla. La tabla que estoy llenando tiene un solo índice que además es clave primaria. La última parte que realizo cuando paso el fichero DBF a access no le cuesta mas de 1 minuto ya que puedo lanzar la consulta SQL que haga la inserción de usa sola vez. Uso la siguiente secuencia:

INSERT INTO Tabla SELECT * FROM fichero_dbf IN "{ruta del fichero}" "dBASE IV;"

Si hubiera una secuencia similar pero que el fichero origen fuera un fichero de texto, me salvaría la vida.

[hgiacobone]

Casualmente yo nececito hacer algo similar.
Para ejecutar esa instrucción SQL, utilizas un ADOConnection+ADODataSet y para la tabla DBF un TTable común?
No logro que funcione esa instruccion, hay algún error en las comillas o los string que mostraste?

[mamcx]

Podrias dar numeros? Que es lo que se demora? Cuanto es el tiempo?

Quiero decir:

- Cuanto se gasta leyendo un registro... cuanto se demora en leer en total
- Cuanto se gasta dando un inserte
- El codigo de ejemplo de que es lo que haces
- Los parametros de conexion (que componentes usas ademas). Me interesa saber que nivel de aislamiento de transacciones manejas...

La "otra" tabla que es? Si intentas usar el asistente de importacion de datos de Acces, cuanto se demora este?

Se me ocurre:

1- Estas usando cursores de solo lectura-hacia adelante? Deberias....
2- Intenta lanzar todos los insert dentro de una transaccion...deberia ser mas rapido
3- Si usas ADO, puedes intentar con un cursor del lado del servidor (CursorLocation=Server)

[Toñico]

Hola hgiacobone,

para lanzar la instrucción SQL uso un componente TADOCommand. La rutina que uso es la siguiente:

 
Código Delphi [-]
 
 
Procedure ...
var
cSQL: String;
...
Begin
.....
 
Try
MyAdoCommand.Connection := MyAdoConnection;
MyAdoConnection.BeginTrans;
cSQL := 'INSERT INTO {Tabla} ' +
      'SELECT * FROM {TablaDBF} ' +
      'IN ' + #39 + {directorio de la tablaDBF} + #39 + ' ' +
      #39 + 'dBASE IV;' + #39;
MyAdoCommand.CommandText := cSQL;
MyAdoCommand.Execute;
 
MyAdoConnection.CommitTrans;
Except
MyAdoConnection.RollbackTrans;
end;
....
end;

La tabla dentro de Access tiene que existir previamente y tener la misma estructura.

Un saludo.

[Toñico]

Hola Mario,

voy a intentar responderte a todo. Te vuelvo a explicar el problema que tengo: Tengo que rellenar una Tabla en Access con los datos que están en otra base de datos. El problema radica en que dicha base de datos es totalmente cerrada al acceso y lo único que me permite es, usando una libreria, leer secuencialmente la tabla de donde quiero obtener los datos.

Leer secuencialmente la tabla origen con 100.000 registros y rellenar un fichero de texto con los campos que necesito tarda unos 5 minutos como mucho.

Hacer insert mientras estoy leyendo la tabla pasa de media hora como poco.

Para abrir la base de datos access uso componentes ADO. El código que probé para hacer los insert es similar al que he puesto como ejemplo en la respuesta que le he dado a hgiacobone. Inicio la transacción, hago todos los insert y al final hago el commit.

El problema fundamental de todo esto, es que tiene que ser transparente para el usuario este proceso. Tengo que rellenar la tabla en access porque tengo que realizar procesos y calculos de registros que no puedo hacer directamente con la tabla origen. Y claro, decirle al usuario que se vaya a tomar un café mientras realizo la exportación no es una opción admisible.

Si hubiera alguna forma de realizar la importación de los datos desde un fichero de texto a access, directamente, usando una secuencia SQL, me solucionaría la vida.

Muchas gracias.

Un saludo.

[Neftali [Germán.Estévez]]

Una idea... Sería cuesión de probar a ver qué tal...

La improtación tada 5 minutos, eso no te lo quita nadie. Probar dos opciones:
(a) Importar como hasta ahora.
(b) Importar con un formato separado por comas.

Desde access adjuntar (no importar) una tabla con formato texto (apuntando a tu fichero de texto); si es separado por comas se puede intentar con formato Excel. La idea es tener la tabla adjunta desde la Base de Datos Access. Menú de Archivo/Obtener Datos Externos/Vincular Tablas.

Teniendo la tabla adjunta desde Access puedes generar una consulta (dentro de Access) del tipo SELECT INTO; Eso te haría el volcado de todos los datos en un sólo paso.

Con ésto tendrás una consulta creada en Access (llamemósle Importar1) que te vuelca todos los Datos del TXT a una Tabla Access.

Desde Delphi las consultas creadas en Access son accesibles como Stored Procedures, de forma que con el componente TADOStoredProc puedes "llamar" al Stored Procedure Importar1.

[mamcx]

Ok, ya me es claro. Bajo las circunstancias, es obvio que la lectura de los datos no es la parte lenta.

Puedes hacer pruebas jugando con el tamaño de la transaccion... en vez de volcar todo de una vez, lee registro a registro en tandas de , por ejemplo, 100 registros (cada 100 registros, haces un commit). Te lo digo porque en un caso similar con Visual FoxPro hacer un proceso parecido se demoraba bastate y jugando con el numero de registros de la transaccion se me soluciono (aunque no te lo aseguro para Acces).

La otra es poner el proceso en un TThread (hilo) lo cual permitira que aunque el proceso se demore, el usuario no tenga que esperarlo. Con ambas tecnicas se deberia aliviar la situacion...

[Toñico]

Gracias Neftali y Mamcx. Voy a probar alguna de las ideas que me habeis dado y os contaré el resultado.

[hgiacobone]

Solo una pequeña observacion: el BeginTrans va fuera del Try, sino nunca salta.

 
Código Delphi [-]
 
Begin
.....
MyAdoConnection.BeginTrans; 
Try
MyAdoCommand.Connection := MyAdoConnection;
 cSQL := 'INSERT INTO....'
 MyAdoCommand.CommandText := cSQL;
 MyAdoCommand.Execute;
 MyAdoConnection.CommitTrans;
Except
MyAdoConnection.RollbackTrans;
end;
end;