Каков наилучший способ выполнения массовых вставок в базу данных MS Access из .NET? Используя ADO.NET, он занимает более часа, чтобы выписать большой набор данных.
Обратите внимание, что мой оригинальный пост, прежде чем я "реорганизовал" его, имел как вопрос, так и ответ в части вопроса. Я взял предложение Игоря Турмана и переписал его в двух частях - вопрос выше, а затем мой ответ.
Я обнаружил, что использование DAO определенным образом примерно в 30 раз быстрее, чем использование ADO.NET. Я разделяю код и получаю этот ответ. В качестве фона, в нижеследующем, тест состоит в том, чтобы записать 100 000 записей таблицы с 20 столбцами.
Резюме техники и времени - от лучшего к худшему:
DAO.Field для ссылки на столбцы таблицы.В качестве фона иногда приходится выполнять анализ достаточно больших объемов данных, и я считаю, что Access - лучшая платформа. Анализ включает в себя множество запросов и часто много кода VBA.
По разным причинам я хотел использовать С# вместо VBA. Типичным способом является использование OleDB для подключения к Access. Я использовал OleDbDataReader, чтобы захватить миллионы записей, и он работал достаточно хорошо. Но при выводе результатов в таблицу потребовалось много времени. Через час.
Сначала обсудим два типичных способа записи записей в Access из С#. Оба способа включают OleDB и ADO.NET. Первый заключается в том, чтобы генерировать инструкции INSERT по одному и выполнять их, принимая 79 секунд для 100 000 записей. Код:
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;
}
Обратите внимание, что я не нашел метода в Access, который позволяет вставлять массив.
Тогда я подумал, что полезно использовать таблицу данных с адаптером данных. Тем более, что я думал, что могу делать пакетные вставки, используя свойство UpdateBatchSize адаптера данных. Однако, видимо, только SQL Server и Oracle поддерживают это, а Access - нет. И это заняло самое длинное время 86 секунд. Код, который я использовал, был:
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;
}
Затем я попробовал нестандартные способы. Сначала я написал текстовый файл, а затем использовал Automation для импорта этого. Это было быстро - 2,8 секунды - и привязано к первому месту. Но я считаю это хрупким по ряду причин: вывод полей даты сложный. Мне пришлось форматировать их специально (someDate.ToString("yyyy-MM-dd HH:mm")), а затем настроить специальную "спецификацию импорта", которая кодов в этом формате. Спецификация импорта также должна иметь правильный разделитель "quote". В приведенном ниже примере с только целыми полями спецификация импорта не требуется.
Текстовые файлы также хрупки для "интернационализации", где используется запятая для десятичных разделителей, разные форматы дат, возможно использование юникода.
Обратите внимание, что первая запись содержит имена полей, так что порядок столбцов не зависит от таблицы, и мы использовали Automation для фактического импорта текстового файла.
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;
}
Наконец, я попробовал DAO. Многие сайты дают огромные предупреждения об использовании DAO. Однако выясняется, что это просто лучший способ взаимодействия между Access и .NET, особенно когда вам нужно записывать большое количество записей. Кроме того, он предоставляет доступ ко всем свойствам таблицы. Я где-то читал, что проще всего программировать транзакции, используя DAO вместо ADO.NET.
Обратите внимание, что есть несколько строк кода, которые комментируются. Скоро они будут объяснены.
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;
}
В этом коде мы создали переменные DAO.Field для каждого столбца (myFields[k]), а затем использовали их. Это заняло 2,8 секунды. В качестве альтернативы можно получить прямой доступ к этим полям, найденным в прокомментированной строке rs.Fields["Field" + (k + 1).ToString()].Value = i + k;, которая увеличила время до 17 секунд. Обертка кода в транзакции (см. Прокомментированные строки) упала до 14 секунд. Использование целочисленного индекса rs.Fields[k].Value = i + k; уменьшило его до 11 секунд. Использование DAO.Field(myFields[k]), и транзакция на самом деле заняла больше времени, увеличив время до 3,1 секунды.
Наконец, для полноты весь этот код находился в простом статическом классе, а операторы using:
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
Спасибо Marc, чтобы проголосовать за вас, я создал учетную запись на StackOverFlow...
Ниже приведен метод многократного использования [Протестировано на С# с платформами 64 бит - Win 7, Windows 2008 R2, Vista, XP]
Сведения о производительности: Экспортирует 120 000 строк за 4 секунды.
Скопируйте приведенный ниже код и передайте параметры... и посмотрите производительность.
Код:
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;
}
}
Вы можете использовать KORM, сопоставитель объектных отношений, который позволяет выполнять массовые операции через MsAccess.
database
.Query<Movie>()
.AsDbSet()
.BulkInsert(_data);
или если у вас есть программа чтения исходного кода, вы можете напрямую использовать класс MsAccessBulkInsert:
using (var bulkInsert = new MsAccessBulkInsert("connection string"))
{
bulkInsert.Insert(sourceReader);
}
KORM доступен из nuget Kros.KORM.MsAccess и открывает его на GitHub.
Спасибо Marc за примеры.
В моей системе производительность DAO не так хороша, как предлагается здесь:
TestADONET_Insert_TransferToAccess(): 68 секунд
TestDAOTransferToAccess(): 29 секунд
Так как в моей системе использование межсетевых библиотек Office не является вариантом, я попробовал новый метод, включающий запись CSV файла, а затем импортировав его через 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;
}
Анализ результатов TestADONET_Insert_FromCsv(): 1,9 секунды
Подобно примеру Marc TestTextTransferToAccess(), этот метод также является хрупким по ряду причин, связанных с использованием файлов CSV.
Надеюсь, что это поможет.
Lorenzo
Еще один способ рассмотрения, связанный с привязкой таблиц через DAO или ADOX, затем выполняет следующие выражения:
SELECT * INTO Table1 FROM _LINKED_Table1
Пожалуйста, см. мой полный ответ здесь:
Пакетное обновление MS Access через ADO.Net и COM-совместимость
Сначала убедитесь, что столбцы таблицы доступа имеют одинаковые имена столбцов и аналогичные типы. Тогда вы можете использовать эту функцию, которая, я считаю, очень быстрая и элегантная.
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);
}
Чтобы добавить в Марк ответ:
Обратите внимание на [STAThread] атрибута [STAThread] над вашим методом Main. сделает вашу программу легко способной взаимодействовать с COM-объектами, увеличивая скорость. Я знаю это не для каждого приложения, но если вы сильно зависите от DAO, я бы порекомендовал это.
Более того, используя метод вставки DAO. Если у вас есть столбец, который не требуется, и вы хотите вставить ноль, даже не устанавливайте его значение. Установка стоимости стоимости времени, даже если она равна нулю.
Обратите внимание на положение компонента DAO здесь. Это помогает объяснить повышение эффективности.