使用ADODB记录集的UpdateBatch方法更新SQL Server

huangapple go评论62阅读模式
英文:

Updating SQL Server using ADODB recordset UpdateBatch method

问题

我正在尝试使用Excel VBA中的ADO在SQL Server中填充单列临时表。我的代码速度很慢,即对于1000行需要145秒。

有没有一种更快的方式,涉及可维护的优雅代码,而不涉及操作原始SQL字符串?我喜欢.AddNew和.UpdateBatch...如果不行,最快的方法是什么?

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
        
    '创建临时表
    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
    
    '将主键添加到这个排除表
    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
英文:

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 

答案1

得分: 0

以下是您提供的代码部分的中文翻译:

一个答案(尽管它看起来有点粗糙)似乎是将所有的INSERT语句放入一个字符串中。这需要1秒。

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

    '创建一个临时表
    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

    '将主键添加到这个排除表
    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
英文:

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

huangapple
  • 本文由 发表于 2023年5月18日 00:50:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76274448.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定