Excel VBA连接SQL Server的OLEDB连接速度过慢问题排查

我有两台SQL Server服务器，在Excel VBA中使用以下连接字符串建立连接：

• Provider=sqloledb;Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI
• Provider=SQLOLEDB;Data Source=myserver2,1433;Initial Catalog=mydb;User ID=login; Password=pass!

用于将SQL Server表数据导入Excel的VBA代码如下：

Dim sql As Variant

Dim conn As New ADODB.Connection

With conn
    .ConnectionTimeout = 120
    .Open SQL_CONN0
    strQuery = "select * from refGlbAccess"
    sql = SQL_ReturnArray(conn, strQuery, True)
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Resize(UBound(sql, 1), UBound(sql, 2)).Value = sql

    sql = SQL_ReturnArray(conn, strQuery, True)
    ThisWorkbook.Sheets("Sheet2").Cells(1, 1).Resize(UBound(sql, 1), UBound(sql, 2)).Value = sql
    .Close
End With

执行时间对比 #

• 连接位于新加坡的第一台服务器（使用第一个连接字符串）：conn.Open耗时4.25秒
• 连接位于西欧的第二台服务器（使用第二个连接字符串）：conn.Open耗时仅0.6秒

疑问 #

1. 是否可能是连接第一台服务器时使用了错误的Provider？
2. 服务器地理位置是否会对连接建立时间产生如此大的影响？（我位于莫斯科）
3. 身份认证方式是否会影响连接建立时间？

更新于12.12 #

已将Provider更新为msoledbsql，拆分代码计时后发现SQL_ReturnArray函数的执行耗时也比连接第二台服务器时长。该函数代码如下：

Public Function SQL_ReturnArray(ByVal conn As ADODB.Connection, ByVal strQuery As String, ByVal column_names As Boolean) As Variant

Dim rs As New ADODB.Recordset
Dim n_arr, r, c As Variant

With rs
    .Open strQuery, conn
    If rs.State = 0 Then
        ReDim n_arr(1 To 1, 1 To 1)
        n_arr(1, 1) = "Empty"
        SQL_ReturnArray = n_arr
        Exit Function
    End If
    If .EOF Then
        ReDim n_arr(1 To 1, 1 To 1)
        n_arr(1, 1) = "Empty"
        SQL_ReturnArray = n_arr
        Exit Function
    End If
    If Not InStr(strQuery, "SCOPE_IDENTITY()") > 0 Then .MoveFirst
    If column_names = False Then
        SQL_ReturnArray = Application.Transpose(rs.GetRows)
    Else
        n_arr = rs.GetRows
        m = UBound(n_arr, 1)
        n = UBound(n_arr, 2)
        ReDim Preserve n_arr(0 To m, 0 To n + 1)
        n_arr = Application.Transpose(n_arr)
        m = UBound(n_arr, 1)
        For iCols = 0 To rs.Fields.Count - 1
            n_arr(m, iCols + 1) = rs.Fields(iCols).Name
        Next
        SQL_ReturnArray = n_arr
    End If
    .Close
End With

Set rs = Nothing

End Function

1. Provider是否错误？ #

你最初使用的sqloledb是旧版OLE DB驱动，虽能正常连接SQL Server，但微软已将MSOLEDBSQL列为官方推荐的驱动。sqloledb本身不算“错误”，但旧驱动可能存在性能或兼容性短板，尤其适配较新SQL Server版本时。更新到MSOLEDBSQL后仍有耗时差异，说明Provider不是唯一影响因素，但换用官方推荐驱动是正确选择，可规避旧驱动潜在缺陷。

2. 地理位置对连接时间的影响？ #

完全可能。你位于莫斯科，新加坡与西欧的网络链路差异会直接放大耗时：

• 莫斯科到西欧的网络链路更短、中转节点更少，延迟更低；
• 莫斯科到新加坡的跨洋链路物理距离远，传播延迟本身就高，再加上中转节点多、路由优化差异，会拉长连接建立和数据传输的时间。你更新后发现SQL_ReturnArray耗时更长，就是数据传输阶段的延迟叠加导致的——从新加坡拉取数据到莫斯科的时间远超过西欧。

3. 身份认证方式的影响？ #

是的，Integrated Security=SSPI（Windows集成认证）比SQL Server账号密码认证更耗时：

• 集成认证需要经过Kerberos或NTLM的身份校验流程，若为域环境还需与域控制器交互，额外的验证步骤会增加连接建立时间；
• SQL Server账号认证直接在服务器端校验用户名密码，流程更简洁，耗时更少。这也是第一台服务器连接耗时更长的原因之一。

额外优化建议（针对SQL_ReturnArray耗时） #

你的SQL_ReturnArray函数包含多次Application.Transpose和数组扩容操作，本身就有性能开销，跨地域延迟会进一步放大这个问题：

• 可尝试直接将Recordset数据复制到工作表，跳过数组转换步骤，比如使用rs.CopyFromRecordset ThisWorkbook.Sheets("Sheet1").Cells(1,1)，减少VBA数组操作的开销；
• 若必须使用数组，可优化数组处理逻辑，提前计算数组大小，减少不必要的Transpose和ReDim Preserve操作。

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