Excel ODBC数据连接查询刷新每个查询所花费的时间 [英] Excel ODBC Data Connection Query time taken to refresh each query

问题描述

我正在尝试测试从Excel数据连接运行的查询的三种变体.

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.

每个查询的连接字符串相同，只有命令文本(Oracle SQL)不同.

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

Excel中是否可以查看每个查询的执行时间?

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

我专门使用版本Excel 2016 MSO 16.0.4456.1003 64bit

推荐答案

可能是这样的(假设所有连接将其结果放置在工作表中，而不是在数据透视表中):

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

要运行此命令:

1. Alt + F11 进入VBA编辑器.
2. 从菜单:插入模块.
3. 在窗口中粘贴代码.
4. 关闭VBA编辑器.
5. Alt + F8 会显示宏列表.选择新的，然后单击运行.
再次将
6. Alt + F11 转到VBA编辑器.
7. Ctr l + G 打开立即窗格，显示结果.

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

这篇关于Excel ODBC数据连接查询刷新每个查询所花费的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！