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

Kai 发表于 Dev

凯

我有一个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完成

我的理论认为这是问题所在吗？如果是这样，是否有可能解决此问题，或者调用'wait'直到当前代码执行完毕而不仅仅是消失？

一种解决方案是在cnX_ExecuteComplete回调期间非常快速地执行某些操作（例如，单行代码Set sheet1RS = pRecordset并在同步运行刷新脚本之前检查它们是否都已完成），因此它们重叠的机会大约为零，但希望首先要知道是否有更好的解决方案。

用户名

我想我无法解释为什么您的某些“刷新脚本”不总是触发。有时他们运行而有时却不运行，这是一种奇怪的行为。我不能真正看到您的整个脚本，但是我可以向您展示我是如何采纳您的代码并使它每次都能工作的。

注意：您的问题与不使用adAsyncExecute参数触发的ExecuteComplete ADODB Connection事件有关

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

就像

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

对于所有3个延迟。

然后在我中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

我真的没有太大的改变，除了额外的参数进行Execute一些代码填充activesheet只是花时间。

现在，我可以运行不同的版本/配置。我可以旋转连接对象的执行时间。我可以有cnA5秒，cnB10秒，cnC20秒。我可以交换/调整每个_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个查询都是异步触发的。

cnA5秒钟后返回句柄，这使cnB第一个使事件（'refresh script'）在层次结构中运行cnC的时间最长。

由于首先cnB 返回，因此将触发它的cnB_ExecuteComplete事件过程。在cnB_ExecuteComplete本身它的设置需要一定的时间执行（迭代100万次，并填充与随机数B栏注：CNA填充列A，CNB列B，数控山坳C）。查看上面的日志，只需花费30秒即可运行。

在完成cnB_ExecuteComplete工作/占用资源（并且您知道VBA是单线程的）时，该cnA_ExecuteComplete事件将添加到TODO进程队列中。因此，您可以将其视为一个队列。在处理某件事时，下一件事必须等待最后的转变。

如果我更改配置；cnA5秒，cnB10秒，cnC20秒，然后让每个“刷新脚本”进行1百万次迭代

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

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

另外，尝试了cnA5秒，cnB5秒，cnC5秒

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根本没有执行。尝试对您的代码进行更改以反映我给您的代码，并自己运行更多文本。我将期待您的反馈。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-02-7

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章

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

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

如何准备并完成回调以触发多个MTAudioProcessingTaps？

$同时观察多个属性并仅触发一次回调

处理多个呼叫异步回调

回调触发后如何使用/存储JSON数据？

异步回调循环

Swift：异步回调

了解异步回调

使用相同的回调函数异步地处理多个请求

Scrapy请求回调不触发

jQuery Ajax成功回调不触发也不返回错误

ContinueWith是否不处理异步回调？

Yeoman递归提示，回调不运行

异步回调并等待

使用Facebook登录时不会触发onLoginFinished回调

使用线程时不会触发Pyttsx3回调

不使用rxjs observables触发发布回调

管道多个文件时，触发了Nodejs Fstream多个“结束”回调

JavaScript异步回调

使用相同的回调函数异步地处理多个请求

使用异步运行递归函数时回调错误

多个查询的节点MySql回调

iPhone不触发Ajax错误回调

在AJAX回调中使用间隔，变量不更新

Android“使用 Facebook 登录”回调没有被触发

节点 - 数据库查询回调未触发

在服务中注册一个回调函数，当服务使用 TypeScript 触发 AngularJs 中的回调时，该回调函数会被调用

在异步中运行回调函数

传递异步回调

(异步 () => { })(); VS回调