vba MS Access：为什么 ADODB.Recordset.BatchUpdate 比 Application.ImportXML 慢这么多？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2986831/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS Access: Why is ADODB.Recordset.BatchUpdate so much slower than Application.ImportXML?
提问by apenwarr
I'm trying to run the code below to insert a whole lot of records (from a file with a weird file format) into my Access 2003 database from VBA. After many, many experiments, this code is the fastest I've been able to come up with: it does 10000 records in about 15 seconds on my machine. At least 14.5 of those seconds (ie. almost all the time) is in the single call to UpdateBatch.
我正在尝试运行下面的代码，将大量记录（来自具有奇怪文件格式的文件）从 VBA 插入到我的 Access 2003 数据库中。经过无数次的实验，这段代码是我能想到的最快的代码：它在我的机器上大约 15 秒内完成了 10000 条记录。其中至少 14.5 秒（即几乎所有时间）都在对 UpdateBatch 的单个调用中。
I've read elsewhere that the JET engine doesn't support UpdateBatch. So maybe there's a better way to do it.
我在别处读到 JET 引擎不支持 UpdateBatch。所以也许有更好的方法来做到这一点。
Now, I would just think the JET engine is plain slow, but that can't be it. After generating the 'testy' table with the code below, I right clicked it, picked Export, and saved it as XML. Then I right clicked, picked Import, and reloaded the XML. Total time to import the XML file? Less than one second, ie. at least 15x faster.
现在，我只是认为 JET 引擎很慢，但事实并非如此。使用下面的代码生成“testy”表后，我右键单击它，选择“导出”，并将其保存为 XML。然后我右键单击，选择导入，并重新加载 XML。导入 XML 文件的总时间？不到一秒，即。至少快 15 倍。
Surely there's an efficient way to insert data into Access that doesn't require writing a temp file?
肯定有一种无需编写临时文件即可将数据插入 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
I would be willing to resort to C/C++ if there's some API that would let me do fast inserts that way. But I can't seem to find it. It can't be that Application.ImportXML is using undocumented APIs, can it?
如果有一些 API 可以让我以这种方式进行快速插入，我愿意求助于 C/C++。但是我好像找不到。不可能是 Application.ImportXML 正在使用未记录的 API，是吗？
采纳答案by HansUp
Unless you must do this with ADO, try DAO instead. Here are the times on my laptop with your procedure and a DAO version:
除非您必须使用 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
This is the DAO version I used.
这是我使用的 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
