Excel CopyFromRecordset正常运行极慢但调试模式速度正常的原因排查

背景信息 #

原有VBA代码稳定运行6-7年，环境配置：

• 数据库：SQL Server 2008 R2
• 客户端：Excel 365 64位（v.2502）
• 常规状态：返回250K条记录的查询执行时间约45秒
• 近期异常：部分用户执行时间飙升至8分钟，已启用手动计算、关闭屏幕更新等标准速度优化措施

涉及代码 #

Sub QueryExecute(rngDta As Range, strCnn As String, strSQL As String)

    Dim cnnADO As New ADODB.Connection
    Dim rstADO As New ADODB.Recordset

    cnnADO.Open strCnn

    cnnADO.CommandTimeout = 600 '10 minutes.

    rstADO.CursorLocation = adUseClient 'Client-side curser allows use of RecordCount property.

    rstADO.Open strSQL, cnnADO, adOpenForwardOnly, adLockReadOnly

    Set rstADO.ActiveConnection = Nothing 'Close connection to server.

    If Not (rstADO.BOF Or rstADO.EOF) Then 'If rows returned.
        rngDta.CopyFromRecordset data:=rstADO
    End If
    
    rstADO.Close
    cnnADO.Close
    
    Set rstADO = Nothing
    Set cnnADO = Nothing

End Sub

异常现象 #

排查后确认耗时差异集中在CopyFromRecordset方法：

• 调试模式（F8单步执行）：仅需19秒完成数据填充
• 正常运行模式：耗时长达7分钟

原因分析 #

• 后台线程资源竞争：正常运行时，Excel会并行处理后台任务（如公式依赖检查、缓存同步、UI调度），与CopyFromRecordset抢占CPU和内存资源，拖慢数据填充速度；调试模式下后台任务暂停，所有资源优先分配给当前代码，效率更高。
• 客户端游标内存管理差异：代码使用adUseClient客户端游标，会将全量查询结果加载到本地内存。正常运行时VBA解释器可能频繁触发垃圾回收，导致填充过程频繁停顿；调试模式下垃圾回收逻辑被抑制，内存分配更顺畅。
• Excel 365自动功能干扰：Excel 365的实时协作、智能感知、数据类型自动检测等新功能，会在批量写入数据时后台扫描新内容；调试模式下这些自动功能被临时禁用，避免了额外处理耗时。
• 64位环境兼容性开销：64位Excel中ADODB的内存交互逻辑与32位不同，正常运行时多线程优化可能带来内存地址转换、数据块拷贝的额外开销；调试模式下执行路径更单一，减少了这类兼容性损耗。

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