Updating SQL Server using ADODB recordset UpdateBatch method

Question

I am trying to populate a single column temporary table in SQL Server using ADO in Excel VBA. My code is very slow, namely 145 seconds for 1,000 rows.

Is there a faster way involving maintainable elegant code that doesn't involve manipulating raw SQL strings? I like .AddNew and .UpdateBatch... Failing that, what is the fastest way?

Private Sub StackOverflowPopTempTable(cn As ADODB.Connection)

Dim rs As ADODB.Recordset
Dim row As Long
Dim strSql As String
Dim row_count As Long
Dim t As Single
t = Timer

Set rs = New ADODB.Recordset
    
'Create a temporary table
strSql = "IF OBJECT_ID('tempdb..##tbl_dummy_data', 'U') IS NOT NULL " & _
            "DROP TABLE ##tbl_dummy_data; " & _
            "CREATE TABLE ##tbl_dummy_data ( " & _
            "dummy_data VARCHAR(20) PRIMARY KEY " & _
            ");"
cn.Execute strSql

'Add masterkeys to this exclusion table
strSql = "SELECT * FROM ##tbl_dummy_data"
rs.Open strSql, cn, adOpenStatic, adLockBatchOptimistic


row_count = 1000
For row = 1 To row_count
    rs.AddNew
    rs("dummy_data") = "a" & row
Next row
Debug.Print Timer - t
rs.UpdateBatch
Set rs = Nothing

Debug.Print Timer - t
    
End Sub

Up to the UpdateBatch statement only takes half a second. The remaining 2 minutes or so is the UpdateBatch statement.

Many thanks - I will update with anything I find in the meantime...

Update 1

This takes the same amount of time

cn.BeginTrans
row_count = 1000
For row = 1 To row_count
    strSql = "INSERT INTO ##tbl_dummy_data VALUES ('a" & row & "')"
    cn.Execute strSql
Next row
Debug.Print Timer - t
cn.CommitTrans
Debug.Print Timer - t`

Update 2

IF OBJECT_ID('tempdb..##tbl_dummy_data', 'U') IS NOT NULL
    DROP TABLE ##tbl_dummy_data;
CREATE TABLE ##tbl_dummy_data (
    dummy_data VARCHAR(20) PRIMARY KEY
);

Insert Into ##tbl_dummy_data
    Select Cast(rand(checksum(newid()))*1000000  as int);
go 1000

This takes 2 minutes on the server side

Update 3

This takes a fraction of a second, so I suspect VBA is doing individual transactions? Even when I use .BeginTrans and .CommitTrans?

DECLARE @counter INT 
        
SET @counter = 0 
BEGIN TRANSACTION
WHILE @counter < 1000
BEGIN 
   Insert Into ##tbl_dummy_data
    Select Cast(rand(checksum(newid()))*1000000  as int);
   SET @counter = @counter + 1
END
COMMIT TRANSACTION

Update 4

Checked that the provider supports transactions. It apparently does!

?cn.Properties("Transaction DDL").Value
 8 

Answer 1

One answer (although it is v hacky) appears to be to put all the INSERTs into one string. This takes 1 second

Private Sub StackOverflowPopTempTableRaw(cn As ADODB.Connection)
    Dim row As Long
    Dim strSql As String
    Dim row_count As Long
    Dim t As Single
    t = Timer

    'Create a temporary table
    strSql = "IF OBJECT_ID('tempdb..##tbl_dummy_data', 'U') IS NOT NULL " & _
                "DROP TABLE ##tbl_dummy_data; " & _
                "CREATE TABLE ##tbl_dummy_data ( " & _
                "dummy_data VARCHAR(20) PRIMARY KEY " & _
                ");"
    cn.Execute strSql
    
    'Add masterkeys to this exclusion table
    row_count = 1000
    For row = 1 To row_count
        strSql = strSql & "INSERT INTO ##tbl_dummy_data VALUES ('a" & row & "'); "
    Next row
    cn.Execute strSql
    Debug.Print Timer - t
End Sub