Excel VBA连接Oracle查询过慢问题求助

我们日常使用Excel及公司应用，Oracle作为数据库。为替代通过公司应用获取数据，我编写了VBA代码直接从Oracle查询统计数据：选中含client_id的Excel单元格后运行代码，执行3条Count统计查询并将结果写入对应单元格。代码可正常运行，但总耗时约7-8秒，无法用于批量数据获取。不过使用Microsoft Query手动执行相同查询速度很快，怀疑代码存在优化空间，现有代码如下：

Sub ConnectToOracle(my_query As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mtxData As Variant

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open ("User ID=**my_obdc**;Password=**mypassword**; Data Source=**mydatasource**; Provider=oraOLEDB.Oracle")

rs.CursorType = adOpenForwardOnly
rs.Open (my_query), cn

mtxData = rs.GetRows

Worksheets(1).Activate

'write down query results in selected excel cell names 
Select Case my_column
    Case 1
    ActiveSheet.Range("Query1_cell") = mtxData
    Case 2
    ActiveSheet.Range("Query2_cell") = mtxData
    Case 3
    ActiveSheet.Range("Query3_cell") = mtxData
    
End Select

'cleanup in the end
Set rs = Nothing
Set cn = Nothing

End Sub



Sub ConnectTest()

Dim client_id
client_id = ActiveCell.Value 'get client id from excel cell

Call ConnectToOracle("SELECT Count(*) FROM TABLE_1 " & _
                     "WHERE (TABLE_1.CALL_YEAR='2023') AND (TABLE_1.client_id=" & call_seq & ") AND (TABLE_1.ACTUAL_TIME Is Null)")
Call ConnectToOracle("SELECT Count(*) FROM TABLE_2 " & _
                     "WHERE (TABLE_2.CALL_YEAR='2023') AND (TABLE_2.client_id=" & call_seq & ") AND (TABLE_2.city='NY')")
Call ConnectToOracle("SELECT Count(*) FROM TABLE_2 " & _
                     "WHERE (TABLE_2.CALL_YEAR='2023') AND (TABLE_2.client_id=" & call_seq & ") AND (TABLE_2.country='USA')")

End Sub

1. 复用数据库连接，消除重复连接开销 #

原代码每次调用ConnectToOracle都会新建并销毁数据库连接，这是耗时的核心原因——连接建立和销毁的开销远大于查询本身。改为只建立一次连接，执行所有查询后再关闭。

2. 合并查询，减少数据库交互次数 #

将3条独立的Count查询合并为1条SQL语句，一次获取所有统计结果，大幅减少往返数据库的次数。

3. 关闭Excel界面刷新与自动计算 #

代码执行期间关闭屏幕刷新和自动计算，避免Excel频繁更新界面拖慢运行速度。

4. 修正变量名错误 #

原代码ConnectTest中误用了未定义的call_seq变量，实际应使用定义好的client_id，这是必须修复的bug。

优化后的完整代码 #

Sub ConnectTest()
    Dim client_id As Variant
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ws As Worksheet
    
    ' 关闭界面刷新和自动计算，提升运行速度
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' 获取选中单元格的client_id
    client_id = ActiveCell.Value
    ' 直接指定工作表，避免激活操作
    Set ws = ThisWorkbook.Worksheets(1)
    
    ' 建立单次数据库连接
    Set cn = New ADODB.Connection
    cn.Open "User ID=**my_obdc**;Password=**mypassword**; Data Source=**mydatasource**; Provider=oraOLEDB.Oracle"
    
    ' 合并3条Count查询为1条，一次性返回所有结果
    Dim combinedQuery As String
    combinedQuery = "SELECT " & _
                    "(SELECT COUNT(*) FROM TABLE_1 WHERE CALL_YEAR='2023' AND client_id=" & client_id & " AND ACTUAL_TIME IS NULL) AS Count1, " & _
                    "(SELECT COUNT(*) FROM TABLE_2 WHERE CALL_YEAR='2023' AND client_id=" & client_id & " AND city='NY') AS Count2, " & _
                    "(SELECT COUNT(*) FROM TABLE_2 WHERE CALL_YEAR='2023' AND client_id=" & client_id & " AND country='USA') AS Count3 FROM DUAL"
    
    ' 执行查询
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenForwardOnly
    rs.Open combinedQuery, cn
    
    ' 将结果写入对应单元格
    If Not rs.EOF Then
        ws.Range("Query1_cell").Value = rs("Count1").Value
        ws.Range("Query2_cell").Value = rs("Count2").Value
        ws.Range("Query3_cell").Value = rs("Count3").Value
    End If
    
    ' 清理资源
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    Set ws = Nothing
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

额外优化建议 #

• 使用参数化查询：避免SQL注入风险，同时让Oracle缓存执行计划，提升重复查询的速度。示例代码如下：

' 参数化查询改造部分
combinedQuery = "SELECT " & _
                "(SELECT COUNT(*) FROM TABLE_1 WHERE CALL_YEAR='2023' AND client_id=:clientId AND ACTUAL_TIME IS NULL) AS Count1, " & _
                "(SELECT COUNT(*) FROM TABLE_2 WHERE CALL_YEAR='2023' AND client_id=:clientId AND city='NY') AS Count2, " & _
                "(SELECT COUNT(*) FROM TABLE_2 WHERE CALL_YEAR='2023' AND client_id=:clientId AND country='USA') AS Count3 FROM DUAL"

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = combinedQuery
' 根据client_id字段的实际类型调整参数类型和长度，这里假设是字符串类型
cmd.Parameters.Append cmd.CreateParameter("clientId", adVarChar, adParamInput, 50, client_id)
Set rs = cmd.Execute

• 检查数据库索引：确保Oracle表的client_id、CALL_YEAR等查询条件字段存在合适的索引，这是让查询速度和Microsoft Query一致的关键——手动查询时可能自动用到了索引，而VBA代码的执行计划可能需要索引优化。

内容的提问来源于stack exchange，提问作者danibus