MS Access：为什么ADODB.Recordset.BatchUpdate比Application.ImportXML慢得多？

Question

我试图运行下面的代码来将一个文件中的大量记录（从一个具有奇怪文件格式的文件）插入到我的Access 2003数据库中。在进行了许多实验之后，我发现这段代码是我能想到的最快的：它可以在我的机器上在大约15秒内处理10000条记录。其中至少14.5秒（即几乎所有时间）都用于单个UpdateBatch调用。

我在其他地方读到JET引擎不支持UpdateBatch。所以可能有更好的方法来做到这一点。

现在，我只认为JET引擎很慢，但这不可能是原因。在使用下面的代码生成“testy”表之后，我右键单击它，选择导出，并将其保存为XML。然后我右键单击，选择导入，并重新加载XML。导入XML文件的总时间？不到一秒钟，即至少快15倍。

肯定有一种有效的方法可以将数据插入到Access中，而不需要编写临时文件吧？

Sub TestBatchUpdate()
    CurrentDb.Execute "create table testy (x int, y int)"

    Dim rs As New ADODB.Recordset
    rs.CursorLocation = adUseServer
    rs.Open "testy", CurrentProject.AccessConnection, _
        adOpenStatic, adLockBatchOptimistic, adCmdTableDirect

    Dim n, v
    n = Array(0, 1)
    v = Array(50, 55)

    Debug.Print "starting loop", Time
    For i = 1 To 10000
        rs.AddNew n, v
    Next i
    Debug.Print "done loop", Time

    rs.UpdateBatch
    Debug.Print "done update", Time

    CurrentDb.Execute "drop table testy"
End Sub

如果有一些API可以让我以这种方式进行快速插入，我愿意使用C / C ++。但我似乎找不到它。 Application.ImportXML难道是使用了未经记录的API吗？

- apenwarr

---

哦，还有，这个查询运行得非常快：“insert into testy select top 10000 * from testy”。不到一秒钟的时间。所以很明显，插入10000行并不是JET遇到很大问题的事情。 - apenwarr

Answer 1

除非你必须使用ADO，否则请尝试使用DAO。以下是在我的笔记本电脑上使用你的程序和DAO版本所需的时间:

ADO:
starting loop 9:51:59 PM
done loop     9:52:00 PM
done update   9:52:54 PM

DAO:
starting loop 9:58:29 PM
done loop     9:58:31 PM
done update   9:58:31 PM

这是我使用的DAO版本。

Sub TestBatchUpdateDAO()

    CurrentDb.Execute "create table testy (x int, y int)"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("testy", dbOpenTable, dbAppendOnly)
    Dim i As Long

    Debug.Print "starting loop", Time
    For i = 1 To 10000
        rs.AddNew
        rs!x = 50
        rs!y = 55
        rs.Update
    Next i
    Debug.Print "done loop", Time

    'rs.UpdateBatch '
    Debug.Print "done update", Time

    rs.Close
    Set rs = Nothing
    CurrentDb.Execute "drop table testy"
End Sub

- HansUp

---

天哪，我简直不敢相信！你是对的！我觉得我已经尝试了“所有”的方法，但我忘记了这个！顺便说一句，使用 DAO 的“插入到…”查询（每行一个）非常慢，但是你的方法确实快得多。谢谢！ - apenwarr

另外需要注意的是，如果你从另一个进程（比如C程序）与MSACCESS.EXE进程通信，由于COM封送，上述方法会很慢。但如果你在MSACCESS.EXE进程内运行（比如Access VBA），它就很快。你可以通过使用CreateObject("DAO.DBEngine.36").OpenDatabase()来避免这种缓慢，它会在当前进程中创建DAO实例并避免封送。这样做可以提高约30倍的速度！在我的C程序中，这意味着我可以达到每秒800次插入，而不是24000次。 - apenwarr

@HansUp 为什么这个更快？ - symbiont

我无法解释为什么它更快，@symbiont - HansUp