使用ADODB运行多个异步查询-回调并不总是触发

Question

我有一个Excel工作簿，它向数据库发送三个查询，在隐藏的工作表上填充三个表，然后运行三个“刷新”脚本将数据拖到三个可见的表示表(每个查询一个)。同步运行此操作非常慢:刷新的总时间是三个查询中每个查询的时间之和，再加上每个“刷新”脚本运行的时间之和。

我知道VBA不是多线程的，但我认为可以通过异步启动查询(从而允许在执行时完成一些清理工作)来加快速度，然后在数据返回时为每个工作表执行填充/刷新工作。

我重写了脚本，如下所示(请注意，我必须删除连接字符串、查询字符串等，并使变量成为通用变量)：

Private WithEvents cnA As ADODB.Connection
Private WithEvents cnB As ADODB.Connection
Private WithEvents cnC As ADODB.Connection

Private Sub StartingPoint()
    'For brevity, only listing set-up of cnA here. You can assume identical
    'set-up for cnB and cnC
    Set cnA = New ADODB.Connection

    Dim connectionString As String: connectionString = "<my conn string>"
    cnA.connectionString = connectionString

    Debug.Print "Firing cnA query: " & Now
    cnA.Open
    cnA.Execute "<select query>", adAsyncExecute  'takes roughly 5 seconds to execute

    Debug.Print "Firing cnB query: " & Now
    cnB.Open
    cnB.Execute "<select query>", adAsyncExecute  'takes roughly 10 seconds to execute

    Debug.Print "Firing cnC query: " & Now
    cnC.Open
    cnC.Execute "<select query>", adAsyncExecute  'takes roughly 20 seconds to execute

    Debug.Print "Clearing workbook tables: " & Now
    ClearAllTables
    TablesCleared = True
    Debug.Print "Tables cleared: " & Now
End Sub

Private Sub cnA_ExecuteComplete(ByVal RecordsAffected As Long, ...)
    Debug.Print "cnA records received: " & Now
    'Code to handle the recordset, refresh the relevant presentation sheet here, 
    'takes roughly < 1 seconds to complete
    Debug.Print "Sheet1 tables received: " & Now
End Sub

Private Sub cnB_ExecuteComplete(ByVal RecordsAffected As Long, ...)
    Debug.Print "cnB records received: " & Now
    'Code to handle the recordset, refresh the relevant presentation sheet here, 
    'takes roughly 2-3 seconds to complete
    Debug.Print "Sheet2 tables received: " & Now
End Sub

Private Sub cnC_ExecuteComplete(ByVal RecordsAffected As Long, ...)
    Debug.Print "cnC records received: " & Now
    'Code to handle the recordset, refresh the relevant presentation sheet here, 
    'takes roughly 5-7 seconds to complete
    Debug.Print "Sheet3 tables received: " & Now
End Sub

典型的预期调试器输出：

Firing cnA query: 21/02/2014 10:34:22
Firing cnB query: 21/02/2014 10:34:22
Firing cnC query: 21/02/2014 10:34:22
Clearing tables: 21/02/2014 10:34:22
Tables cleared: 21/02/2014 10:34:22
cnB records received: 21/02/2014 10:34:26
Sheet2 tables refreshed: 21/02/2014 10:34:27
cnA records received: 21/02/2014 10:34:28
Sheet1 tables refreshed: 21/02/2014 10:34:28
cnC records received: 21/02/2014 10:34:34
Sheet3 tables refreshed: 21/02/2014 10:34:40

当然，根据首先完成的顺序，这三个查询可以以不同的顺序返回，因此有时典型的输出排序是不同的--这是预期的。

然而，有时候，有一两个cnX_ExecuteComplete回调根本不触发。经过一段时间的调试后，我相当肯定的原因是，如果一个记录集在当前执行回调时返回，则不会发生调用。例如：

• 查询A、B和C时间均为0
• 查询A首先在第3时间完成，cnA_ExecuteComplete触发
• 查询B在第5时间完成第二次查询
• cnA_ExecuteComplete仍在运行，因此cnB_ExecuteComplete从不触发
• cnA_ExecuteComplete在8点完成
• 查询C在第10时间完成，cnC_ExecuteComplete触发
• 查询C于时间15完成

我的理论正确吗，这就是问题所在？如果是这样的话，是否有可能解决这个问题，或者调用“等待”，直到当前代码已经执行，而不是消失？

一种解决方案是在cnX_ExecuteComplete回调期间(例如，一行Set sheet1RS = pRecordset和检查它们是否在同步运行刷新脚本之前完成)，因此它们重叠的可能性大约为零，但想知道是否有更好的解决方案。

Answer 1

我想我无法解释为什么你的一些‘刷新脚本’不总是开火。这是一种奇怪的行为，有时他们运行，有时他们不运行。我不能真正看到你的整个脚本，但我可以告诉你，我是如何采用你的代码，并使它工作的每一次。

注意:你的问题与https://stackoverflow.com/questions/16167478/executecomplete-adodb-connection-event-not-fired-with-adasyncexecute-parameter有某种联系

我在我的SQL服务器上添加了3个存储过程：sp_WaitFor5、sp_WaitFor10、sp_WaitFor20来模拟查询执行时间的延迟。

很简单

CREATE PROCEDURE sp_WaitFor5
AS
WAITFOR DELAY '00:00:05'

所有三次延误。

然后在我的Module1中添加了一个非常简单的代码来调用自定义类

Option Explicit

Private clsTest As TestEvents

Sub Main()
    Cells.ClearContents
    Set clsTest = New TestEvents
    Call clsTest.StartingPoint
End Sub

然后，我将类模块重命名为TestEvents，并添加了稍微修改过的代码版本。

Option Explicit

Private WithEvents cnA As ADODB.Connection
Private WithEvents cnB As ADODB.Connection
Private WithEvents cnC As ADODB.Connection

Private i as Long

Public Sub StartingPoint()

    Dim connectionString As String: connectionString = "Driver={SQL Server};Server=MYSERVER\INST; UID=username; PWD=password!"

    Debug.Print "Firing cnA query(10 sec): " & Now
    Set cnA = New ADODB.Connection
    cnA.connectionString = connectionString
    cnA.Open
    cnA.Execute "sp_WaitFor10", adExecuteNoRecords, adAsyncExecute

    Debug.Print "Firing cnB query(5 sec): " & Now
    Set cnB = New ADODB.Connection
    cnB.connectionString = connectionString
    cnB.Open
    cnB.Execute "sp_WaitFor5", adExecuteNoRecords, adAsyncExecute

    Debug.Print "Firing cnC query(20 sec): " & Now
    Set cnC = New ADODB.Connection
    cnC.connectionString = connectionString
    cnC.Open
    cnC.Execute "sp_WaitFor20", adExecuteNoRecords, adAsyncExecute

End Sub


Private Sub cnA_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print vbTab & "cnA_executeComplete START", Now
    For i = 1 To 55
        Range("A" & i) = Rnd(1)
    Next i
    Debug.Print vbTab & "cnA_executeComplete ENDED", Now
End Sub

Private Sub cnB_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print vbTab & "cnB_executeComplete START", Now
    For i = 1 To 1000000
        Range("B" & i) = Rnd(1)
    Next i
    Debug.Print vbTab & "cnB_executeComplete ENDED", Now
End Sub

Private Sub cnC_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print vbTab & "cnC_executeComplete START", Now
    For i = 1 To 55
        Range("C" & i) = Rnd(1)
    Next i
    Debug.Print vbTab & "cnC_executeComplete ENDED", Now
End Sub

除了附加参数 for Execute和一些只为占用时间而填充活动表的代码之外，我并没有做太多的改变。

现在，我能够运行不同的变体/配置。我可以旋转连接对象的执行时间。我可以有cnA 5秒，cnB 10秒，cnC 20秒。我可以交换/调整每个_ExecuteComplete事件的执行时间。

从我自己的测试中，我可以向您保证，所有3个都是始终执行的。

下面是一些基于类似于您的配置的日志

Firing cnA query(10 sec): 24/02/2014 12:59:46
Firing cnB query(5 sec): 24/02/2014 12:59:46
Firing cnC query(20 sec): 24/02/2014 12:59:46
    cnB_executeComplete START             24/02/2014 12:59:51 
    cnB_executeComplete ENDED             24/02/2014 13:00:21 
    cnA_executeComplete START             24/02/2014 13:00:21 
    cnA_executeComplete ENDED             24/02/2014 13:00:21 
    cnC_executeComplete START             24/02/2014 13:00:22 
    cnC_executeComplete ENDED             24/02/2014 13:00:22

在上面的示例中可以看到，所有3个查询都是异步触发的。

cnA在5秒后返回句柄，这使cnB成为第一个在层次结构中运行事件(‘刷新脚本’)的句柄，因为cnC花费的时间最长。

由于cnB首先返回，所以它触发了cnB_ExecuteComplete事件过程。cnB_ExecuteComplete本身--它被设置为需要一些时间执行(迭代100万次并用随机数填充列B)。注: cnA填充列A，cnB col，cnC col )。看看上面的日志，它需要30秒才能运行。

当cnB_ExecuteComplete执行其工作时，/taking up资源(如您所知，VBA是单线程的)，cnA_ExecuteComplete事件被添加到TODO进程的队列中。所以，你可以把它想象成一个队列。当事情正在被处理的时候，下一件事情只能等待它的到来。

如果我更改配置；cnA 5秒，cnB 10秒，cnC 20秒，然后让每个“刷新脚本”迭代100万次

Firing cnA query(5 sec): 24/02/2014 13:17:10
Firing cnB query(10 sec): 24/02/2014 13:17:10
Firing cnC query(20 sec): 24/02/2014 13:17:10
one million iterations each
    cnA_executeComplete START             24/02/2014 13:17:15 
    cnA_executeComplete ENDED             24/02/2014 13:17:45 
    cnB_executeComplete START             24/02/2014 13:17:45 
    cnB_executeComplete ENDED             24/02/2014 13:18:14 
    cnC_executeComplete START             24/02/2014 13:18:14 
    cnC_executeComplete ENDED             24/02/2014 13:18:44 

从第一个例子中可以清楚地证明我的观点。

同时，尝试了cnA 5秒，cnB 5秒，cnC 5秒。

Firing cnA query(5 sec): 24/02/2014 13:20:56
Firing cnB query(5 sec): 24/02/2014 13:20:56
Firing cnC query(5 sec): 24/02/2014 13:20:56
one million iterations each
    cnB_executeComplete START             24/02/2014 13:21:01 
    cnB_executeComplete ENDED             24/02/2014 13:21:31 
    cnA_executeComplete START             24/02/2014 13:21:31 
    cnA_executeComplete ENDED             24/02/2014 13:22:01 
    cnC_executeComplete START             24/02/2014 13:22:01 
    cnC_executeComplete ENDED             24/02/2014 13:22:31

它也完成/执行所有3。

就像我说的那样，我看不到您的整个代码，可能您的代码中有一个未处理的错误，可能有什么东西误导您，认为一个_ExecuteComplete根本没有执行。尝试对您的代码进行更改，以反映我给您的代码，并自己运行更多的文本。我将期待你的反馈。

Answer 2

我也不知道为什么这个事件并不总是为你而被炒鱿鱼。

对我来说，测试总是有效的(测试有100 000行和14列)，但我不确定数据库的大小和正在执行的查询的复杂性。

不过，我有句话要说。

ExecuteComplete和FetchComplete事件之间有一个重要的区别。

ExecuteComplete在命令执行完成后触发(在您的示例中，命令对象是由ADO在内部创建的)。这并不一定意味着在回调触发时所有记录都已被获取。

因此，如果需要使用返回的记录集，则应侦听fetchComplete回调，该回调仅在记录集被完全获取时触发。