使用 VBA 运行多个 SQL 查询 (Oracle) - 为什么这么慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12803795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run multiple SQL queries (Oracle) using VBA - why so slow?
提问by Maciej
I wrote a simple VBA code to run multiple SQL queries. The results are supposed to go to excel sheet. The problem is that the procedure takes ages to finish! In fact it would be much faster to run these queries one by one. Can anyone please tell me how to make it work faster?
我编写了一个简单的 VBA 代码来运行多个 SQL 查询。结果应该去excel表。问题是这个过程需要很长时间才能完成！事实上，一一运行这些查询会快得多。谁能告诉我如何使它工作得更快？
Here is my code:
这是我的代码：
Const strCon As String = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=xxxx)(PORT=xxx))" & _
"(CONNECT_DATA=(SID=RTD))); uid=xxxx; pwd=xxxx;"
Sub RunScripts()
Dim r As Integer
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim q1 As String
Dim q2 As String
Dim q3 As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
q1 = Worksheets("Data").Range("i28").Value
Set rs = con.Execute(q1)
Worksheets("ACCV Query").Range("A2").CopyFromRecordset rs
Set rs = Nothing
r = Worksheets("ACCV Query").Range("A65536").End(xlUp).Row
'Next SQL Query
q2 = Worksheets("Data").Range("j28").Value
Set rs = con.Execute(q2)
Worksheets("ACCV Query").Range("A" & r).CopyFromRecordset rs
Set rs = Nothing
r = Worksheets("ACCV Query").Range("A65536").End(xlUp).Row
'Next SQL Query
q3 = Worksheets("Data").Range("k28").Value
Set rs = con.Execute(q3)
Worksheets("ACCV Query").Range("A" & r).CopyFromRecordset rs
con.Close
Set con = Nothing
Set rs = Nothing
End Sub
The SQL queries are stored in cells and named q1, q2, etc. (all of them work).
SQL 查询存储在单元格中并命名为 q1、q2 等（它们都可以工作）。
When I ran two of such queries it worked fine, but once I launched 15... the macro never finished working. Each SQL script takes couple of seconds to run so it should work quick.
当我运行两个这样的查询时，它运行良好，但是一旦我启动了 15 ......宏就再也没有完成工作。每个 SQL 脚本都需要几秒钟才能运行，因此它应该可以快速运行。
How can I make it work faster? Help please!
我怎样才能让它工作得更快？请帮忙！
Thanks a lot! M.
非常感谢！M。
回答by Chris Latta
Typically what slows Excel down is the constant screen updating and recalculation. Whenever you change the contents of a cell, it recalculates and refreshes the screen. On big insertions, this really slows the operation down. Fortunately you can turn this off.
通常，使 Excel 变慢的原因是不断更新和重新计算屏幕。每当您更改单元格的内容时，它都会重新计算并刷新屏幕。在大插入时，这确实减慢了操作速度。幸运的是，您可以将其关闭。
At the top of your function, put:
在函数的顶部，放置：
Application.Calculation = xlManual
Application.ScreenUpdating = False
Then at the end of your function (and in any error handling), put:
然后在函数的末尾（以及任何错误处理），输入：
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
