Excel+vba+Oracle连接太慢，可能是我的代码出错了

2lpgd968 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(126)

我们每天使用Excel，也使用公司应用程序。Oracle是数据库。我想直接从Oracle数据库中获取数据，以避免从公司应用程序中获取数据，但它太慢了。
此时此刻，这就是我所拥有的：首先，我选择一个Excel单元格，有client_id。然后启动VBA代码。做3个查询，只需要解决多少个“x”，数量，而不是数据。最后把结果写在不同的单元格里。
这是我的代码：

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

这是工作得很好，查询完成，结果写下来。但是太慢了。总共7-8秒。这意味着我不能用它来获取大量数据。
但是请注意，如果我建立了一个连接，然后使用旧的Microsoft Query，我可以非常快地手动进行任何这些查询。所以我认为我的代码不是最好的。

oracle

来源：https://stackoverflow.com/questions/77139218/excelvbaoracle-too-slow-connections-maybe-something-wrong-in-my-code

1条答案

按热度按时间

guicsvcw1#

打开连接一次，使用它进行多个查询，然后关闭连接。
例如：

（注意：这是未经测试的，但它应该给予你一个方法的想法，即使你必须修复语法。

Private Function ConnectToOracle()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open ("User ID=**my_obdc**;Password=**mypassword**; Data Source=**mydatasource**; Provider=oraOLEDB.Oracle")
    ConnectToOracle = cn
End Function

Private Sub ExecuteQuery( _
    cn As ADODB.Connection, _
    my_query AS String, _
    mtxData As Range _
)
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenForwardOnly
    rs.Open (my_query), cn

    mtxData = rs.GetRows

    rs.close()
    'cleanup in the end
    Set rs = Nothing
End Sub

Sub ConnectTest()
    Dim client_id
    Dim cn As ADODB.Connection
    client_id = ActiveCell.Value 'get client id from excel cell

    Worksheets(1).Activate

    Set cn = ConnectToOracle()

    Call ExecuteQuery( _
        cn, _
        "SELECT Count(*) FROM TABLE_1" _
        & " WHERE call_year = '2023'"
        & " AND   client_id = " & call_seq _
        & " AND   actual_time IS NULL", _
        ActiveSheet.Range("Query1_cell") _
    )

    Call ExecuteQuery( _
        cn, _
        "SELECT Count(*) FROM TABLE_2" _
        & " WHERE call_year = '2023'"
        & " AND   client_id = " & call_seq _
        & " AND   city      = 'NY'", _
        ActiveSheet.Range("Query2_cell") _
    )

    Call ExecuteQuery( _
        cn, _
        "SELECT Count(*) FROM TABLE_3" _
        & " WHERE call_year = '2023'"
        & " AND   client_id = " & call_seq _
        & " AND   country   = 'USA'", _
        ActiveSheet.Range("Query3_cell") _
    )

    cn.close()
    Set cn = Nothing
End Sub

赞(0）回复(0）举报 2023-10-16

我来回答

Excel+vba+Oracle连接太慢，可能是我的代码出错了

1条答案

相关问题

热门标签

最新问答