Excel ODBC Data Connection Query time taken to ref

问题:

I am trying to test out three variations of a query that is ran from an Excel data connection.

I have three individual data connections and three individual tabs that get the data from each connection respectively.

The connection string is identical for each query, only the command text (Oracle SQL) is different.

Is there a way in Excel to view the execution times for each query?

I am specifically using version Excel 2016 MSO 16.0.4456.1003 64bit

回答1:

Something like this perhaps (assumes all connections place their results in a worksheet table, not in a pivottable):

Sub TimeQueries()
    Dim oSh As Worksheet
    Dim oCn As WorkbookConnection
    Dim dTime As Double
    For Each oCn In ThisWorkbook.Connections
        dTime = Timer
        oCn.Ranges(1).ListObject.QueryTable.Refresh False
        Debug.Print Timer - dTime, oCn.Name, oCn.Ranges(1).Address(external:=True)
    Next
End Sub

To run this:

1. Alt+F11 to go to the VBA editor.
2. From menu: Insert Module.
3. Paste code in the window.
4. Close VBA editor.
5. Alt+F8 brings up list of macro's. Pick the new one and click run.
6. Alt+F11 again to the VBA editor.
7. Ctrl+G opens the immediate pane with the results.

If you want the code to write to a cell, use this version:

Sub TimeQueries()
    Dim oSh As Worksheet
    Dim oCn As WorkbookConnection
    Dim dTime As Double
    Dim lRow As Long
    Set oSh = Worksheets("Sheet4") 'Change to your sheet name!
    oSh.Cells(1,1).Value = "Name of Connection"
    oSh.Cells(1,2).Value = "Location"
    oSh.Cells(1,1).Value = "Refresh time (s)"
    For Each oCn In ThisWorkbook.Connections
        lRow = lRow + 1
        dTime = Timer
        oCn.Ranges(1).ListObject.QueryTable.Refresh False
        oSh.Cells(lRow,3).Value = Timer - dTime
        oSh.Cells(lRow,1).Value = oCn.Name
        oSh.Cells(lRow,2).Value = oCn.Ranges(1).Address(external:=True)
    Next
End Sub