5

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.

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

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.

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

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?

apenwarr
  • 10,352
  • 5
  • 44
  • 58
  • Oh also, this query runs ridiculously fast: "insert into testy select top 10000 * from testy". Way less than one second. So obviously inserting 10000 rows isn't something JET has a lot of trouble with. – apenwarr Jun 07 '10 at 03:03

1 Answers1

4

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

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
HansUp
  • 92,185
  • 11
  • 67
  • 122
  • Holy cow, I can't believe it! You're right! I thought I had tried *everything*, but I forgot this one! BTW, "insert into..." queries (one per row) with DAO are super slow, but your method is indeed massively faster. Thanks! – apenwarr Jun 07 '10 at 06:10
  • 1
    Another note: the above method is slow if you're talking to an MSACCESS.EXE process from another process (like a C program) because of COM marshalling, but it's fast if you're running inside the MSACCESS.EXE process (like Access VBA). You can get around the slowness by using CreateObject("DAO.DBEngine.36").OpenDatabase(), which creates the DAO instance inside your current process and avoids marshalling. The difference is about a 30x speedup! In a C program, that means 800 inserts/sec vs. 24000 inserts/sec for me. – apenwarr Jun 07 '10 at 16:29
  • @HansUp why is this faster? – symbiont Jun 27 '19 at 10:48
  • I can't explain why it's faster, @symbiont – HansUp Jun 27 '19 at 11:47