英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论