为什么打开到我的数据库的连接要花这么长时间？

Question

我已经创建了一些代码，通过一个自定义函数将信息从sql数据库拉到excel中。目前，代码仅打开连接就需要大约5-7秒。代码的所有其他部分，包括查询本身，都非常快。知道为什么要花这么长时间吗？

这是我在本地机器上使用的sqlsever。

Function Lob_amt(sp_name As String, l_date As String) As Double


Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Dim recset As ADODB.Recordset
Set recset = New ADODB.Recordset

Dim l_year As Double
Dim l_name As String


l_year = Year(l_date)



Dim sqlQry As String, sConnect As String

sqlQry = "SELECT sum(lob_lobbying.Amount)FROM lob_lobbying" & _
    " WHERE lob_lobbying.Latest = 'Y'" & _
    " AND lob_lobbying.IndTot ='Y'" & _
    " AND UltOrg = '" & sp_name & "'" & _
    " AND CycleYear in ('" & l_year & "')"

sConnect = "Driver={SQL Server};Server=DESKTOP-L9CVIVP;Database=lobbying;Trusted_Connection=yes;"


Conn.Open sConnect

recset.Open sqlQry, Conn

If IsNull(recset.Fields(0).Value) Then
    Lob_amt = 0
    Else

    Lob_amt = recset.Fields(0).Value

    End If
recset.Close
Conn.Close
Set recset = Nothing
Set Conn = Nothing


End Function

我预计连接需要毫秒，但打开连接需要5-7秒。使用我当前的设置作为自定义函数，我将拉入大约80,000个查询。我可以稍微改变一下设置，这样我就不再使用自定义函数了，但是连接时间似乎异常长。

任何建议都是非常感谢的！

Answer 1

尝试通过执行以下命令来分析代码，并研究即时窗口(在VBA编辑器中通过Ctrl + G打开：

    Function Lob_amt(sp_name As String, l_date As String) As Double

    Debug.Print "begin Lob_amt function: " & Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection
    Dim recset As ADODB.Recordset
    Set recset = New ADODB.Recordset

    Dim l_year As Double
    Dim l_name As String


    l_year = Year(l_date)



    Dim sqlQry As String, sConnect As String

    sqlQry = "SELECT sum(lob_lobbying.Amount)FROM lob_lobbying" & _
        " WHERE lob_lobbying.Latest = 'Y'" & _
        " AND lob_lobbying.IndTot ='Y'" & _
        " AND UltOrg = '" & sp_name & "'" & _
        " AND CycleYear in ('" & l_year & "')"
    Debug.Print vbNewLine & sqlQry & vbNewLine
    sConnect = "Driver={SQL Server};Server=DESKTOP-L9CVIVP;Database=lobbying;Trusted_Connection=yes;"


    Conn.Open sConnect
        Debug.Print "Lob_amt function Conn.Open: " & Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
    recset.Open sqlQry, Conn

    If IsNull(recset.Fields(0).Value) Then
        Lob_amt = 0
           Debug.Print "Lob_amt Lob_amt = 0: " & Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
        Else

        Lob_amt = recset.Fields(0).Value
                    Debug.Print "Lob_amt recset.Fields(0).Valu: " & Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
        End If

    recset.Close
    Debug.Print "Lob_amt recset closed: " & Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
    Conn.Close
    Debug.Print "Lob_amt conn closed: " & Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
    Set recset = Nothing
    Set Conn = Nothing


    End Function

问题可能出现在模块的其他功能中

更新：SELECT sum(lob_lobbying.Amount)FROM lob_lobbying WHERE lob_lobbying.Latest = 'Y' AND lob_lobbying.IndTot ='Y' AND UltOrg = 'Cigna Corp' AND CycleYear in ('2006')此生成的查询被传递给Open方法，因此这些冻结(长达5秒)不是由于Open方法本身，而是由于在SQL Server上执行此查询，其中包含条件和组操作(SUM)，所以这是数据库的问题。这个问题，如果它非常不舒服，应该通过其他方法来解决(索引、数据分割、OLAP等)