从.NET向MS Access数据库执行批量插入的最佳方法是什么?使用ADO.NET,写一个大型数据集耗时一个多小时。
请注意,在“重构”之前,我的原始帖子在问题部分同时包含了问题和答案。 我接受了Igor Turman的建议,并将其分为两部分- 上面的问题,然后是我的答案。
我发现以特定方式使用DAO的速度大约比使用ADO.NET快30倍。我正在共享代码并得到这个答案。作为背景,下面的测试是写出20列的表的100000条记录。
技术和时间的摘要-从好到坏:
DAO.Field
作为背景,有时我需要对相当大量的数据进行分析,我发现Access是最好的平台。该分析涉及许多查询,并且经常涉及许多VBA代码。
由于各种原因,我想使用C#代替VBA。典型的方法是使用OleDB连接到Access。我曾经使用OleDbDataReader来抓取数百万条记录,并且效果很好。但是,将结果输出到表时,需要花费很长时间。一个多小时。
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秒。我使用的代码是:
UpdateBatchSize
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”定界符。在下面的示例中,只有整数字段,不需要导入规范。
someDate.ToString("yyyy- MM-dd HH:mm")
文本文件对于“国际化”也很脆弱,在十进制分隔符,不同的日期格式以及可能使用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秒。
myFields[k]
rs.Fields["Field" + (k + 1).ToString()].Value = i + k;
rs.Fields[k].Value = i + k;
最后,为了完整起见,所有这些代码都在一个简单的静态类中,并且using语句如下:
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