使用VBA运行多个SQL查询（Oracle） - 为什么这么慢？

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.

1 个解决方案

#1

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

#1