Ответ 1
Я обнаружил, что использование DAO определенным образом примерно в 30 раз быстрее, чем использование ADO.NET. Я разделяю код и получаю этот ответ. В качестве фона, в нижеследующем, тест состоит в том, чтобы записать 100 000 записей таблицы с 20 столбцами.
Резюме техники и времени - от лучшего к худшему:
- 02.8 секунд: Используйте DAO, используйте
DAO.Fieldдля ссылки на столбцы таблицы. - 02.8 секунд: Записываем в текстовый файл, используйте Automation для импорта текста в Access
- 11.0 секунд: Используйте DAO, используйте индекс столбца, чтобы ссылаться на столбцы таблицы.
- 17.0 секунд: Используйте DAO, обратитесь к столбцу по имени
- 79,0 секунд: Используйте ADO.NET, генерируйте инструкции INSERT для каждой строки
- 86,0 секунд: Используйте ADO.NET, используйте DataTable для DataAdapter для "пакетной" вставки
В качестве фона иногда приходится выполнять анализ достаточно больших объемов данных, и я считаю, что 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