Escribir una gran cantidad de registros(inserción masiva) para acceder en.NET/C#
ms-access dao (7)
¿Cuál es la mejor manera de realizar inserciones masivas en una base de datos de MS Access desde .NET? Usando ADO.NET, está tomando más de una hora escribir un gran conjunto de datos.
Tenga en cuenta que mi publicación original, antes de "refactorizarla", tenía tanto la pregunta como la respuesta en la parte de la pregunta. Tomé la sugerencia de Igor Turman y la reescribí en dos partes: la pregunta anterior y luego mi respuesta.
Descubrí que usar DAO de una manera específica es aproximadamente 30 veces más rápido que usar ADO.NET. Estoy compartiendo el código y los resultados en esta respuesta. Como antecedentes, en la parte inferior, la prueba consiste en escribir 100 000 registros de una tabla con 20 columnas.
Un resumen de la técnica y los tiempos, de mejor a peor:
- 02.8 segundos: use DAO, use
DAO.Fieldpara referirse a las columnas de la tabla - 02.8 segundos: escriba en un archivo de texto, use Automatización para importar el texto en Access
- 11.0 segundos: use DAO, use el índice de columna para referirse a las columnas de la tabla.
- 17.0 segundos: use DAO, consulte la columna por nombre
- 79.0 segundos: use ADO.NET, genere instrucciones INSERT para cada fila
- 86.0 segundos: use ADO.NET, use DataTable en un DataAdapter para inserción de "lote"
Como antecedentes, de vez en cuando necesito realizar análisis de cantidades razonablemente grandes de datos, y encuentro que Access es la mejor plataforma. El análisis implica muchas consultas y, a menudo, un montón de código VBA.
Por varias razones, quería usar C # en lugar de VBA. La forma típica es usar OleDB para conectarse a Access. OleDbDataReader un OleDbDataReader para tomar millones de registros, y funcionó bastante bien. Pero al enviar resultados a una tabla, tomó mucho, mucho tiempo. Más de una hora.
Primero, analicemos las dos formas típicas de escribir registros para acceder desde C #. Ambas formas implican OleDB y ADO.NET. La primera es generar sentencias INSERT una a la vez, y ejecutarlas, tomando 79 segundos para los 100 000 registros. El código es:
public static double TestADONET_Insert_TransferToAccess()
{
StringBuilder names = new StringBuilder();
for (int k = 0; k < 20; k++)
{
string fieldName = "Field" + (k + 1).ToString();
if (k > 0)
{
names.Append(",");
}
names.Append(fieldName);
}
DateTime start = DateTime.Now;
using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM TEMP";
int numRowsDeleted = cmd.ExecuteNonQuery();
Console.WriteLine("Deleted {0} rows from TEMP", numRowsDeleted);
for (int i = 0; i < 100000; i++)
{
StringBuilder insertSQL = new StringBuilder("INSERT INTO TEMP (")
.Append(names)
.Append(") VALUES (");
for (int k = 0; k < 19; k++)
{
insertSQL.Append(i + k).Append(",");
}
insertSQL.Append(i + 19).Append(")");
cmd.CommandText = insertSQL.ToString();
cmd.ExecuteNonQuery();
}
cmd.Dispose();
}
double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
return elapsedTimeInSeconds;
}
Tenga en cuenta que no encontré ningún método en Access que permita una inserción masiva.
Entonces pensé que quizás usar una tabla de datos con un adaptador de datos sería útil. Especialmente porque pensé que podría hacer inserciones por lotes utilizando la propiedad UpdateBatchSize de un adaptador de datos. Sin embargo, aparentemente solo SQL Server y Oracle lo admiten, y Access no. Y tomó el tiempo más largo de 86 segundos. El código que utilicé fue:
public static double TestADONET_DataTable_TransferToAccess()
{
StringBuilder names = new StringBuilder();
StringBuilder values = new StringBuilder();
DataTable dt = new DataTable("TEMP");
for (int k = 0; k < 20; k++)
{
string fieldName = "Field" + (k + 1).ToString();
dt.Columns.Add(fieldName, typeof(int));
if (k > 0)
{
names.Append(",");
values.Append(",");
}
names.Append(fieldName);
values.Append("@" + fieldName);
}
DateTime start = DateTime.Now;
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM TEMP";
int numRowsDeleted = cmd.ExecuteNonQuery();
Console.WriteLine("Deleted {0} rows from TEMP", numRowsDeleted);
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM TEMP", conn);
da.InsertCommand = new OleDbCommand("INSERT INTO TEMP (" + names.ToString() + ") VALUES (" + values.ToString() + ")");
for (int k = 0; k < 20; k++)
{
string fieldName = "Field" + (k + 1).ToString();
da.InsertCommand.Parameters.Add("@" + fieldName, OleDbType.Integer, 4, fieldName);
}
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.InsertCommand.Connection = conn;
//da.UpdateBatchSize = 0;
for (int i = 0; i < 100000; i++)
{
DataRow dr = dt.NewRow();
for (int k = 0; k < 20; k++)
{
dr["Field" + (k + 1).ToString()] = i + k;
}
dt.Rows.Add(dr);
}
da.Update(dt);
conn.Close();
double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
return elapsedTimeInSeconds;
}
Luego probé maneras no estándar. Primero, escribí en un archivo de texto, y luego usé la automatización para importar eso. Esto fue rápido (2.8 segundos) y empató en el primer lugar. Pero considero esto frágil por varias razones: la salida de campos de fecha es complicada. Tuve que formatearlos especialmente ( someDate.ToString("yyyy-MM-dd HH:mm") ) y luego configurar una "especificación de importación" especial que codifica en este formato. La especificación de importación también tenía que tener el delimitador "quote" establecido a la derecha. En el ejemplo siguiente, con solo campos enteros, no hubo necesidad de una especificación de importación.
Los archivos de texto también son frágiles para la "internacionalización", donde hay un uso de comas para separadores de decimales, diferentes formatos de fecha, posible uso de unicode.
Tenga en cuenta que el primer registro contiene los nombres de los campos para que el orden de las columnas no dependa de la tabla y que utilicemos Automation para hacer la importación real del archivo de texto.
public static double TestTextTransferToAccess()
{
StringBuilder names = new StringBuilder();
for (int k = 0; k < 20; k++)
{
string fieldName = "Field" + (k + 1).ToString();
if (k > 0)
{
names.Append(",");
}
names.Append(fieldName);
}
DateTime start = DateTime.Now;
StreamWriter sw = new StreamWriter(Properties.Settings.Default.TEMPPathLocation);
sw.WriteLine(names);
for (int i = 0; i < 100000; i++)
{
for (int k = 0; k < 19; k++)
{
sw.Write(i + k);
sw.Write(",");
}
sw.WriteLine(i + 19);
}
sw.Close();
ACCESS.Application accApplication = new ACCESS.Application();
string databaseName = Properties.Settings.Default.AccessDB
.Split(new char[] { '';'' }).First(s => s.StartsWith("Data Source=")).Substring(12);
accApplication.OpenCurrentDatabase(databaseName, false, "");
accApplication.DoCmd.RunSQL("DELETE FROM TEMP");
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "TEMP",
FileName: Properties.Settings.Default.TEMPPathLocation,
HasFieldNames: true);
accApplication.CloseCurrentDatabase();
accApplication.Quit();
accApplication = null;
double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
return elapsedTimeInSeconds;
}
Finalmente, probé DAO. Muchos sitios ofrecen grandes advertencias sobre el uso de DAO. Sin embargo, resulta que es simplemente la mejor manera de interactuar entre Access y .NET, especialmente cuando necesita escribir una gran cantidad de registros. Además, da acceso a todas las propiedades de una tabla. Leí en alguna parte que es más fácil programar transacciones usando DAO en lugar de ADO.NET.
Tenga en cuenta que hay varias líneas de código que se comentan. Ellos serán explicados pronto.
public static double TestDAOTransferToAccess()
{
string databaseName = Properties.Settings.Default.AccessDB
.Split(new char[] { '';'' }).First(s => s.StartsWith("Data Source=")).Substring(12);
DateTime start = DateTime.Now;
DAO.DBEngine dbEngine = new DAO.DBEngine();
DAO.Database db = dbEngine.OpenDatabase(databaseName);
db.Execute("DELETE FROM TEMP");
DAO.Recordset rs = db.OpenRecordset("TEMP");
DAO.Field[] myFields = new DAO.Field[20];
for (int k = 0; k < 20; k++) myFields[k] = rs.Fields["Field" + (k + 1).ToString()];
//dbEngine.BeginTrans();
for (int i = 0; i < 100000; i++)
{
rs.AddNew();
for (int k = 0; k < 20; k++)
{
//rs.Fields[k].Value = i + k;
myFields[k].Value = i + k;
//rs.Fields["Field" + (k + 1).ToString()].Value = i + k;
}
rs.Update();
//if (0 == i % 5000)
//{
//dbEngine.CommitTrans();
//dbEngine.BeginTrans();
//}
}
//dbEngine.CommitTrans();
rs.Close();
db.Close();
double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
return elapsedTimeInSeconds;
}
En este código, creamos las variables DAO.Field para cada columna ( myFields[k] ) y luego las usamos. Tardó 2.8 segundos. Alternativamente, uno podría acceder directamente a esos campos como se encuentra en la línea comentada rs.Fields["Field" + (k + 1).ToString()].Value = i + k; que aumentó el tiempo a 17 segundos. Envolver el código en una transacción (ver las líneas comentadas) lo dejó caer a 14 segundos. Usando un índice entero rs.Fields[k].Value = i + k; droppped eso a 11 segundos. Usar DAO.Field ( myFields[k] ) y una transacción en realidad tomó más tiempo, aumentando el tiempo a 3.1 segundos.
Por último, para completar, todo este código estaba en una clase estática simple, y las instrucciones de using son:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ACCESS = Microsoft.Office.Interop.Access; // USED ONLY FOR THE TEXT FILE METHOD
using DAO = Microsoft.Office.Interop.Access.Dao; // USED ONLY FOR THE DAO METHOD
using System.Data; // USED ONLY FOR THE ADO.NET/DataTable METHOD
using System.Data.OleDb; // USED FOR BOTH ADO.NET METHODS
using System.IO; // USED ONLY FOR THE TEXT FILE METHOD
Gracias Marc por los ejemplos.
En mi sistema, el rendimiento de DAO no es tan bueno como se sugiere aquí:
TestADONET_Insert_TransferToAccess (): 68 segundos
TestDAOTransferToAccess (): 29 segundos
Como en mi sistema el uso de bibliotecas de interoperabilidad de Office no es una opción, probé un nuevo método que implicaba la escritura de un archivo CSV y luego la importación a través de ADO:
public static double TestADONET_Insert_FromCsv()
{
StringBuilder names = new StringBuilder();
for (int k = 0; k < 20; k++)
{
string fieldName = "Field" + (k + 1).ToString();
if (k > 0)
{
names.Append(",");
}
names.Append(fieldName);
}
DateTime start = DateTime.Now;
StreamWriter sw = new StreamWriter("tmpdata.csv");
sw.WriteLine(names);
for (int i = 0; i < 100000; i++)
{
for (int k = 0; k < 19; k++)
{
sw.Write(i + k);
sw.Write(",");
}
sw.WriteLine(i + 19);
}
sw.Close();
using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM TEMP";
int numRowsDeleted = cmd.ExecuteNonQuery();
Console.WriteLine("Deleted {0} rows from TEMP", numRowsDeleted);
StringBuilder insertSQL = new StringBuilder("INSERT INTO TEMP (")
.Append(names)
.Append(") SELECT ")
.Append(names)
.Append(@" FROM [Text;Database=.;HDR=yes].[tmpdata.csv]");
cmd.CommandText = insertSQL.ToString();
cmd.ExecuteNonQuery();
cmd.Dispose();
}
double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
return elapsedTimeInSeconds;
}
Análisis de rendimiento de TestADONET_Insert_FromCsv (): 1.9 segundos
Similar al ejemplo de Marc TestTextTransferToAccess (), este método también es frágil por varias razones con respecto al uso de archivos CSV.
Espero que esto ayude.
Lorenzo
Otro método a considerar, que implica vincular tablas a través de DAO o ADOX y luego ejecutar sentencias como esta:
SELECT * INTO Table1 FROM _LINKED_Table1
Por favor, mira mi respuesta completa aquí:
Actualización por lotes MS Access a través de ADO.Net e interoperabilidad COM
Primero asegúrese de que las columnas de la tabla de acceso tengan los mismos nombres de columna y tipos similares. Entonces puedes usar esta función que creo que es muy rápida y elegante.
public void AccessBulkCopy(DataTable table)
{
foreach (DataRow r in table.Rows)
r.SetAdded();
var myAdapter = new OleDbDataAdapter("SELECT * FROM " + table.TableName, _myAccessConn);
var cbr = new OleDbCommandBuilder(myAdapter);
cbr.QuotePrefix = "[";
cbr.QuoteSuffix = "]";
cbr.GetInsertCommand(true);
myAdapter.Update(table);
}
Puede usar un KORM, mapeador de relaciones de objeto que permite operaciones masivas sobre MsAccess.
database
.Query<Movie>()
.AsDbSet()
.BulkInsert(_data);
o si tiene un lector de origen, puede usar directamente la clase MsAccessBulkInsert :
using (var bulkInsert = new MsAccessBulkInsert("connection string"))
{
bulkInsert.Insert(sourceReader);
}
KORM está disponible en nuget Kros.KORM.MsAccess y es de código abierto en GitHub
Tenga en cuenta la posición del componente DAO here . Esto ayuda a explicar las mejoras de eficiencia.
Gracias Marc , para poder votar, creé una cuenta en ...
A continuación se muestra el método reutilizable [Probado en C # con plataformas de 64 bits - Win 7, Windows 2008 R2, Vista, XP]
Detalles de rendimiento: Exporta 120,000 filas en 4 segundos.
Copie el código a continuación y pase los parámetros ... y vea el rendimiento.
- Simplemente pase su datatable con el mismo esquema, como el objetivo de Access Db Table.
- DBPath = Ruta de acceso completa Db
- TableNm = Nombre de la tabla Db de acceso de destino.
El código:
public void BulkExportToAccess(DataTable dtOutData, String DBPath, String TableNm)
{
DAO.DBEngine dbEngine = new DAO.DBEngine();
Boolean CheckFl = false;
try
{
DAO.Database db = dbEngine.OpenDatabase(DBPath);
DAO.Recordset AccesssRecordset = db.OpenRecordset(TableNm);
DAO.Field[] AccesssFields = new DAO.Field[dtOutData.Columns.Count];
//Loop on each row of dtOutData
for (Int32 rowCounter = 0; rowCounter < dtOutData.Rows.Count; rowCounter++)
{
AccesssRecordset.AddNew();
//Loop on column
for (Int32 colCounter = 0; colCounter < dtOutData.Columns.Count; colCounter++)
{
// for the first time... setup the field name.
if (!CheckFl)
AccesssFields[colCounter] = AccesssRecordset.Fields[dtOutData.Columns[colCounter].ColumnName];
AccesssFields[colCounter].Value = dtOutData.Rows[rowCounter][colCounter];
}
AccesssRecordset.Update();
CheckFl = true;
}
AccesssRecordset.Close();
db.Close();
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine);
dbEngine = null;
}
}