MS Access:为什么 ADODB.Recordset.BatchUpdate 比 Application.ImportXML 慢这么多? [英] MS Access: Why is ADODB.Recordset.BatchUpdate so much slower than Application.ImportXML?

问题描述

我正在尝试运行下面的代码，从 VBA 将大量记录(来自具有奇怪文件格式的文件)插入到我的 Access 2003 数据库中.经过无数次的实验，这段代码是我能想到的最快的代码:它在我的机器上大约 15 秒内完成了 10000 条记录.其中至少 14.5 秒(即几乎所有时间)都在对 UpdateBatch 的单个调用中.

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.

我在别处读到 JET 引擎不支持 UpdateBatch.所以也许有更好的方法来做到这一点.

I've read elsewhere that the JET engine doesn't support UpdateBatch. So maybe there's a better way to do it.

现在，我只是认为 JET 引擎很慢，但事实并非如此.使用下面的代码生成testy"表后，我右键单击它，选择导出"，并将其保存为 XML.然后我右键单击，选择导入，并重新加载 XML.导入 XML 文件的总时间?不到一秒，即.至少快 15 倍.

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.

当然有一种不需要写入临时文件的有效方法可以将数据插入到 Access 中吗?

Surely there's an efficient way to insert data into Access that doesn't require writing a temp file?

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，对吗?

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?

推荐答案

除非您必须使用 ADO 执行此操作，否则请尝试使用 DAO.以下是我的笔记本电脑上显示您的程序和 DAO 版本的时间:

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:
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版本.

This is the DAO version I used.

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

这篇关于MS Access:为什么 ADODB.Recordset.BatchUpdate 比 Application.ImportXML 慢这么多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！