我发现以特定方式使用DAO的速度大约比使用ADO.NET快30倍。我正在共享代码并得到这个答案。作为背景,下面的测试是写出20列的表的100000条记录。
作为背景,有时我需要对相当大量的数据进行分析,我发现Access是最好的平台。该分析涉及许多查询,并且经常涉及许多VBA代码。
由于各种原因,我想使用C#代替VBA。典型的方法是使用OleDB连接到Access。我曾经使用OleDbDataReader
来抓取数百万条记录,并且效果很好。但是,将结果输出到表时,需要花费很长时间。一个多小时。
首先,让我们讨论将记录从C#写入Access的两种典型方法。两种方式都涉及OleDB和ADO.NET。第一种是一次生成一个INSERT语句,然后执行它们,对100000条记录花费79秒。代码是:
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;
}
然后我尝试了非标准方法。首先,我写了一个文本文件,然后使用自动化将其导入。这非常快-2.8秒-并列第一。但是由于许多原因,我认为它很脆弱:输出日期字段很棘手。我必须对它们进行特殊格式化(someDate.ToString("yyyy- MM-dd HH:mm")
),然后设置一个特殊的“导入规范”以这种格式编码。导入规范还必须设置“ quote”定界符。在下面的示例中,只有整数字段,不需要导入规范。
文本文件对于“国际化”也很脆弱,在十进制分隔符,不同的日期格式以及可能使用unicode的地方都使用逗号。
请注意,第一条记录包含字段名称,因此列顺序不依赖于表,并且我们使用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;
}
在这段代码中,我们为每个列(myFields[k]
)创建了DAO.Field变量,然后使用它们。花了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