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

问题描述

这是怎么样的一个后续<一个href="http://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c">this螺纹。这一切是 NET 2.0 ;对我来说，至少。

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

从本质上讲，马克（从上面OP）尝试了几种不同的方法来更新MS Access表有10万条记录，发现使用DAO连接大致 10 - 30倍的速度更快比使用ADO.Net。我去了几乎相同的路径（下面的示例），并得出了同样的结论。

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年。我真的preFER避免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秒10万条记录/ 10列：

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:

• 开关出OLEDB的ODBC
• 遍历数据表的每一行运行INSERT语句
  • 这是.Update做反正
  • Switching out OleDB for ODBC
  • Looping through the data table and running an INSERT statement for each row
    • This is what .Update does anyway
    • 出于无奈;这是热闹。

    最后，我尝试使用DAO。在code应该基本上可以做同样的事情;但它显然不是，因为这个运行在约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
    

    其他一些注意事项：

    A few other notes:

    • 在一切都被转换为字符串的所有实例，试图让事情变得简单和一致地
      • 例外：在我的第一个例子中，使用Table.Load功能，我这样做不是因为......嗯，我真的不能，但我也基本上是相同的事情时，我通过循环读者和内置插入命令（这是它在做什么，反正）。它并没有帮助。
      • Everything is converted to Strings in all examples to try to keep things as simple and consistent as possible
        • Exception: In my first example, using the Table.Load function, I don't because... well, I really can't, but I did basically the same thing when I looped through the reader and built insert commands (which is what it's doing, anyway). It didn't help.

        希望有人能够阐明一些这...这只是奇怪。 在此先感谢！

        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 和更新委托直接到MS Access当量，在任何时候，它生成SQL，所以没有SQL由MS访问被解析。

        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的code生成一个Update语句的每一行，该更新语句被传递到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 和更新 命令来访问数据库或
        2. 传递SQL到MS Access，这是不解析SQL优化， 并且一旦解析，最终将转换成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，然后有一些跨preT即SQL，这里的DAO方法绕过SQL生成/间pretation，并径直向金属。

        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.

        解决此问题的方法是创建自己的数据库服务与Access数据库的计算机上运行。这乘警您的选择和放大器;更新并能在远程处理，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.

        搞清楚的正确名称数据库驱动程序（如喷或其他）是一个练习留给读者的

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