sql - Excel CopyFromRecordset method has suddenly gotten very slow but still fast in debug mode - Stack Overflow

admin管理员组

文章数量:1401650

The code below has worked very well for the last 6 or 7 years. The database is SQL Server 2008 R2, the client is Excel 365 64-bit (v.2502). Over the past few months, some users are seeing execution times as high as 8 minutes when 45 seconds is more common for a query returning 250K records. Standard speed optimizations are in place: calculation is set to manual and screenupdating is off.

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

I had been looking at server or network latency, but have discovered that the CopyFromRecordset method appears to be the problem. What is very strange is that when stepping through the code in debug mode, if I execute the CopyFromRecordset line with the F8 key, it takes 19 seconds to populate the worksheet range. If I just run it normally, it can take 7 minutes (according to the debug.print statements).

What would explain the huge time difference between the two execution methods?

Thanks!

本文标签： sqlExcel CopyFromRecordset method has suddenly gotten very slow but still fast in debug modeStack Overflow