通过 ADO.Net 和 COM 互操作性进行 MS Access 批量更新 [英] MS Access Batch Update via ADO.Net and COM Interoperability

问题描述

这是对 这个线程.这就是 .Net 2.0 的全部内容；至少对我来说.

This is kind of a follow-up to this thread. This is all with .Net 2.0; for me, at least.

基本上，Marc(来自上面的 OP)尝试了几种不同的方法来更新具有 100,000 条记录的 MS Access 表，并发现使用 DAO 连接比使用 ADO.Net 大约快 10 到 30 倍.我走了几乎相同的路径(下面的示例)并得出了相同的结论.

Essentially, Marc (OP from above) tried several different approaches to update an MS Access table with 100,000 records and found that using a DAO connection was roughly 10 - 30x faster than using ADO.Net. I went down virtually the same path (examples below) and came to the same conclusion.

我想我只是想了解 为什么 OleDB 和 ODBC 慢得多，我很想知道是否有人找到了比 DAO 更好的答案自 2011 年那篇文章以来.我真的更愿意避免使用 DAO 和/或自动化，因为它们将要求客户端计算机具有可再分发的 Access 或数据库引擎(或者我坚持使用不支持的 DAO 3.6支持.ACCDB).

I guess I'm just trying to understand why OleDB and ODBC are so much slower and I'd love to hear if anyone has found a better answer than DAO since that post in 2011. I would really prefer to avoid DAO and/or Automation, since they're going to require the client machine to either have Access or the database engine redistributable (or I'm stuck with DAO 3.6 which doesn't support .ACCDB).

最初的尝试；100,000 条记录/10 列约 100 秒:

Original attempt; ~100 seconds for 100,000 records/10 columns:

Dim accessDB As New OleDb.OleDbConnection( _ 
                      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                accessPath & ";Persist Security Info=True;")
accessDB.Open()

Dim accessCommand As OleDb.OleDbCommand = accessDB.CreateCommand
Dim accessDataAdapter As New OleDb.OleDbDataAdapter( _
                                   "SELECT * FROM " & tableName, accessDB)
Dim accessCommandBuilder As New OleDb.OleDbCommandBuilder(accessDataAdapter)

Dim accessDataTable As New DataTable
accessDataTable.Load(_Reader, System.Data.LoadOption.Upsert)

//This command is what takes 99% of the runtime; loops through each row and runs 
//the update command that is built by the command builder. The problem seems to 
//be that you can't change the UpdateBatchSize property with MS Access
accessDataAdapter.Update(accessDataTable)

无论如何，我觉得这很奇怪，所以我尝试了几种相同的口味:

Anyway, I thought this was really odd so I tried several flavors of the same thing:

• 为 ODBC 切换 OleDB
• 循环遍历数据表并为每一行运行一个 INSERT 语句
  • 这就是 .Update 的作用
  • 出于沮丧；太搞笑了.

  最后，我尝试使用 DAO.代码基本上应该做同样的事情；但显然不是，因为它会在大约 10 秒内运行.

  Finally, I tried using DAO. The code should basically be doing the same thing; except it clearly isn't, because it this runs in ~10 seconds.

   Dim dbEngine As New DAO.DBEngine
   Dim accessDB As DAO.Database = dbEngine.OpenDatabase(accessPath)
   Dim accessTable As DAO.Recordset = accessDB.OpenRecordset(tableName)
  
  While _Reader.Read
      accessTable.AddNew()
        For i = 0 To _Reader.FieldCount - 1
          accessTable.Fields(i).Value = _Reader.Item(i).ToString
        Next
      accessTable.Update()
  End While
  

  其他几点说明:

  • 在所有示例中，所有内容都转换为字符串，以尽量保持简单和一致
    • 例外:在我的第一个示例中，我不使用 Table.Load 函数，因为...好吧，我真的不能，但是当我循环浏览阅读器并构建插入命令时，我做了基本相同的事情(无论如何，这就是它正在做的事情).它没有帮助.

    希望有人能够对此有所了解……这很奇怪.提前致谢！

    Hopefully someone will be able to shed some light on this... it's just strange. Thanks in advance!

    推荐答案

    这里的原因是，DAO 驱动比 ODBC 驱动更接近 MS Access 数据库引擎.

    The reason here is that the DAO driver sits much closer to the MS Access Database engine than the ODBC driver.

    DAO 方法 AddNew 和 Update 直接委托给 MS Access 等效项，它绝不会生成 SQL，因此 MS Access 不会解析 SQL.

    The DAO methods AddNew and Update delegate directly to MS Access equivalents, at no point does it generate SQL, so there's no SQL to be parsed by the MS Access.

    另一方面，DataAdapter 代码为每一行生成一个更新语句，该更新语句被传递给 ODBC，然后由它传递给 MSAccess 驱动程序，该驱动程序要么

    On the other hand, the DataAdapter code generates an Update statement for each row, that update statement gets passed to ODBC, which then passes this to a MSAccess driver, which either

    1. 独立解析SQL并发出AddNew和UpdateAccess 数据库的命令或
    2. 将 SQL 传递给未针对解析 SQL 进行优化的 MS Access，并且一旦解析，最终将 SQL 转换为 AddNew 和更新命令.
    1. independently parses the SQL and issues AddNew and Update commands to the Access database or
    2. passes the SQL to MS Access, which isn't optimised for parsing SQL, and which once parsed, ends up translating the SQL into AddNew and Update commands.

    无论哪种方式，您都需要花费时间生成 SQL，然后让某些东西解释该 SQL，其中 DAO 方法绕过 SQL 生成/解释并直接进入金属.

    either way, your time is taken generating SQL and then having something interpret that SQL, where the DAO approach bypasses SQL generation / interpretation and goes straight to the metal.

    解决此问题的一种方法是创建您自己的数据库服务"，该服务在具有访问数据库的机器上运行.这会整理您的选择和更新并可以通过远程处理、WCF(http 或其他)与客户端通信.这是很多工作，并且会极大地改变您的应用程序逻辑.

    One way around this is to create your own "database service" running on the machine with the access db. This marshals your selects & updates and could communicate with the client over remoting, WCF (http, or whatever). This is a lot of work and changes your application logic considerably.

    找出数据库驱动程序的正确名称(例如 Jet 或其他)是留给读者的练习

    这篇关于通过 ADO.Net 和 COM 互操作性进行 MS Access 批量更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！