网站诊断工具/搜索引擎优化的各种方法

我发现以特定方式使用DAO大约比使用ADO.NET快30倍。 我正在共享代码并得到此答案的结果。 作为背景，下面的测试是写出20列的表的100000条记录。

技术和时间的摘要-从好到坏：

02.8秒：使用DAO，使用myFields[k]引用表格列

02.8秒：写出到文本文件，使用自动化将文本导入Access

11.0秒：使用DAO，使用列索引引用表列。

17.0秒：使用DAO，按名称引用该列

79.0秒：使用ADO.NET，为每一行生成INSERT语句

86.0秒：使用ADO.NET，使用DataTable到DataAdapter进行“批量”插入

作为背景，有时我需要对相当大量的数据进行分析，我发现Access是最好的平台。 该分析涉及许多查询，并且通常涉及许多VBA代码。

由于各种原因，我想使用C＃代替VBA。 典型的方法是使用OleDB连接到Access。 我使用了myFields[k]来获取数百万条记录，并且效果很好。 但是，将结果输出到表时，需要花费很长时间。 一个多小时。

首先，让我们讨论从C＃向Access写入记录的两种典型方法。 两种方式都涉及OleDB和ADO.NET。 第一种是一次生成一个INSERT语句，然后执行它们，花费100秒记录需要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中找不到允许批量插入的方法。

然后，我认为也许将数据表与数据适配器一起使用会很有用。 尤其是因为我以为可以使用数据适配器的myFields[k]属性进行批量插入。 但是，显然只有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秒-并列第一。 但是由于许多原因，我认为它很脆弱：输出日期字段很棘手。 我必须对它们进行特殊格式化(myFields[k])，然后设置一个特殊的“导入规范”以这种格式编码。 导入规范还必须设置“ 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(using)和事务实际上花费了更长的时间，将时间增加到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