使用ADODB运行多个异步查询 - 回调并不总是触发 [英] Running multiple async queries with ADODB - callbacks not always firing
问题描述
我知道VBA不是多线程的，但是我认为可以通过异步触发查询来加快速度（因此允许一些清理工作完成，正在执行），然后在数据回来时对每张表进行填充/刷新工作。
我重写了我的脚本如下（注意，我不得不删除连接字符串，查询字符串等，并使变量通用）：
Private WithEvents cnA As ADODB.Connection
Private WithEvents cnB As ADODB.Connection
Private WithEvents cnC As ADODB.Connection
Private Sub StartingPoint（）
'为简洁起见，只列出cnA的设置。您可以为cnB和cnC
设置相同的
'设置cnA = New ADODB.Connection
Dim connectionString As String：connectionString =< my conn string>
cnA.connectionString = connectionString
Debug.PrintFiring cnA query：&现在
cnA.Open
cnA.Execute< select query>，adAsyncExecute'需要大约5秒才能执行
Debug.PrintFiring cnB query：&现在
cnB.Open
cnB.Execute< select query>，adAsyncExecute'需要大约10秒才能执行
Debug.PrintFiring cnC query：&现在
cnC.Open
cnC.Execute< select query>，adAsyncExecute'需要大约20秒才能执行
Debug.Print清除工作簿表：&现在
ClearAllTables
TablesCleared = True
Debug.Print清除表：&现在
End Sub
Private Sub cnA_ExecuteComplete（ByVal RecordsAffected As Long，...）
Debug.PrintcnA records received：&现在
'处理记录集的代码，刷新这里的相关演示文稿，
'大致< 1秒完成
Debug.PrintSheet1表已收到：&现在
End Sub
Private Sub cnB_ExecuteComplete（ByVal RecordsAffected As Long，...）
Debug.PrintcnB records received：&现在
'处理记录集的代码，在这里刷新相关的表单，
'大约需要2-3秒才能完成
Debug.Print接收到的Sheet2表：&现在
End Sub
Private Sub cnC_ExecuteComplete（ByVal RecordsAffected As Long，...）
Debug.PrintcnC records received：&现在
'处理记录集的代码，在这里刷新相关的演示文稿，
'大约需要5-7秒完成
Debug.PrintSheet3表接收到：&现在
End Sub
典型的预期调试器输出：
点击cnA查询：21/02/2014 10:34:22
发送cnB查询：21/02/2014 10:34:22
发布cnC查询：21/02/2014 10:34:22
清算表：21/02/2014 10:34:22
清除表：21/02/2014 10:34:22
cnB收到的记录：21/02/2014 10:34:26
Sheet2表格刷新：21/02/2014 10:34:27
cnA收到的记录：21/02/2014 10： 34:28
Sheet1表刷新：21/02/2014 10:34:28
收到的cnC记录：21/02/2014 10:34:34
Sheet3表刷新：21/02 / 2014 10:34:40
三个查询可以以不同的顺序返回，具体取决于首先完成当然，所以有时候典型的输出是不一样的，这是预期的。
然而，有时候，有一个或者两个 cnX_ExecuteComplete 回调根本不触发。经过一段时间的调试，我相当肯定的原因是，如果一个记录集当其中一个回调正在执行时返回，则不会发出调用。例如：
- 查询A，B和C全部在时间0起火
- 查询完成首先在时间3，
cnA_ExecuteCompletefires - 查询B在第5时间完成第二个
-
cnA_ExecuteComplete仍在运行，所以cnB_ExecuteComplete从不触发 -
cnA_ExecuteComplete在8时完成 - 查询C在时间10完成，
cnC_ExecuteCompletefires - 查询C在时间15完成
我的理论是正确的，这是问题吗？如果是这样，是否可以解决这个问题，或者打电话给等待，直到现在的代码执行而不是消失？
一个解决方案是做在 cnX_ExecuteComplete 回调（例如，单行设置sheet1RS = pRecordset 和一个检查以查看是否它们都是在同步运行刷新脚本之前完成的），所以它们重叠的机会大约为零，但是想知道是否有更好的解决方案。
我想我无法解释为什么有些刷新脚本并不总是触发。这是一个奇怪的行为，有时他们运行，有时他们没有。我看不到你的整个脚本，但是我可以告诉你我如何采用你的代码，而且每次使它成功。
注意：您的问题与执行完成ADODB连接事件有关没有使用adAsyncExecute参数触发
我在我的SQL服务器上添加了3个存储过程; sp_WaitFor5 ， sp_WaitFor10 ， sp_WaitFor20 来模拟查询的延迟执行时间。
简单如
CREATE PROCEDURE sp_WaitFor5
AS
WAITFOR DELAY '00：00：05'
所有3个延迟。
然后在我的 Module1 中添加了一个非常简单的代码来调用自定义类
Option Explicit
私有clsTest作为TestEvents
Sub Main（）
Cells.ClearContents
设置clsTest =新的TestEvents
调用clsTest.StartingPoint
End Sub
然后，我将类模块重命名为 TestEvents ，并添加了一个略微修改的代码版本
Option Explicit
Private WithEvents cnA As ADODB.Connection
Private WithEvents cnB As ADODB.Connection
Private WithEvents cnC As ADODB.Co n连接
私人我作为长
公共Sub StartingPoint（）
Dim connectionString As String：connectionString =Driver = {SQL Server}; Server = MYSERVER\INST; UID =用户名; PWD = password！
Debug.PrintFiring cnA query（10 sec）：& Now
Set cnA = New ADODB.Connection
cnA.connectionString = connectionString
cnA.Open
cnA.Executesp_WaitFor10，adExecuteNoRecords，adAsyncExecute
Debug.PrintFiring cnB query（5 sec）：& Now
Set cnB = new ADODB.Connection
cnB.connectionString = connectionString
cnB.Open
cnB.Executesp_WaitFor5，adExecuteNoRecords，adAsyncExecute
Debug.PrintFiring cnC query （20秒）：&现在
设置cnC =新建ADODB.Connection
cnC.connectionString = connectionString
cnC.Open
cnC.Executesp_WaitFor20，adExecuteNoRecords，adAsyncExecute
End 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 A s ADODB.Connection）
Debug.Print vbTab& cnA_executeComplete START，现在
对于i = 1到55
范围（A& i）= Rnd（1）
下一个i
Debug.Print vbTab& cnA_executeComplete ENDED，现在
End 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，现在
对于i = 1到1000000
范围（B& i）= Rnd（1）
下一个i
Debug.Print vbTab& cnB_executeComplete ENDED，现在
End 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，现在
对于i = 1到55
范围（C& i）= Rnd（1）
下一步i
Debug.Print vbTab& cnC_executeComplete ENDED，现在
End Sub
除了< a href =http://msdn.microsoft.com/en-us/library/windows/desktop/ms681559(v=vs.85).aspx =nofollow noreferrer>额外参数 for 执行，一些填充活动表单的代码只需要花费时间。
现在，我可以运行不同的变体/配置。我可以旋转连接对象的执行时间。我可以拥有 cnA 5秒， cnB 10sec， cnC 20秒。我可以交换/调整每个 _ExecuteComplete 事件的执行时间。
从我自己的测试中，我可以向你保证，所有3都执行始终。
这里有一些基于类似于您的配置的日志
cnA查询（10秒）：24/02/2014 12:59:46
发送cnB查询（5秒）：24/02/2014 12:59:46
发送cnC查询（20秒） ：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_execute完成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 第一个在层次结构中运行事件（'刷新脚本'）<？c $ c> cnC 需要最长时间。
由于 cnB 首先回覆，它触发它的 cnB_ExecuteComplete 事件过程。 cnB_ExecuteComplete 本身设置为需要一些时间执行（迭代1百万次，并用随机数字填充列B。注意：cnA填充列A ，cnB col B，cnC col C）。看上述日志，运行正好30秒。
虽然 cnB_ExecuteComplete 正在做其工作/采取资源（，而且您知道VBA是单线程的） cn c＃c> cnA_ExecuteComplete 事件被添加到TODO进程的队列中。所以，你可以像排队一样想到它。当一些事情被照顾下一件事情时，只好等待轮到最后。
如果我更改配置; cnA 5秒， cnB 10秒， cnC 20秒并且每个'刷新脚本'迭代100万次，然后
点击cnA查询（5秒）：24/02/2014 13:17:10
发射cnB查询（10秒）：24/02/2014 13:17:10
发射cnC查询（20秒）：24/02 / 2014 13:17:10
每百万次迭代
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
从第一个例子中清楚地证明了我的观点。 / p>
另外，尝试使用 cnA 5秒， cnB 5秒， cnC 5秒
启动cnA查询y（5秒）：24/02/2014 13:20:56
发射cnB查询（5秒）：24/02/2014 13:20:56
发射cnC查询（5秒）： 24/02/2014 13:20:56
每百万次迭代
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 根本没有执行。尝试更改您的代码以反映我给您的代码，并自行运行一些文本。我会期待您的反馈。
I have an Excel workbook that fires three queries to a database to populate three tables on hidden sheets, and then runs three 'refresh' scripts to pull this data through to three visible presentation sheets (one per query). Running this synchronously is quite slow: The total time to refresh is the sum of the time of each of the three queries, plus the sum of the time for each 'refresh' script to run.
I'm aware that VBA isn't multi-threaded, but I thought it would be possible to speed things up a bit by firing the queries off asynchronously (thus allowing some clean-up work to be done whilst they were executing), and then doing the population / refresh work for each sheet as the data comes back.
I rewrote my script as follows (note that I've had to remove the connection strings, query strings etc and make the variables generic):
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
Typical expected debugger output:
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
The three queries can come back in different orders depending on which finishes first, of course, so sometimes the typical output is ordered differently - this is expected.
Sometimes however, one or two of the cnX_ExecuteComplete callbacks don't fire at all. After some time debugging, I'm fairly certain the reason for this is that if a recordset returns whilst one of the callbacks is currently executing, the call does not occur. For example:
- query A, B and C all fire at time 0
- query A completes first at time 3,
cnA_ExecuteCompletefires - query B completes second at time 5
cnA_ExecuteCompleteis still running, socnB_ExecuteCompletenever firescnA_ExecuteCompletecompletes at time 8- query C completes at time 10,
cnC_ExecuteCompletefires - query C completes at time 15
Am I right in my theory that this is the issue? If so, is it possible to work around this, or get the call to 'wait' until current code has executed rather than just disappearing?
One solution would be to do something extremely quick during the cnX_ExecuteComplete callbacks (eg, a one-liner Set sheet1RS = pRecordset and a check to see if they're all done yet before running the refresh scripts synchronously) so the chance of them overlapping is about zero, but want to know if there's a better solution first.
I guess I am not able to explain why some your 'refresh scripts' don't always fire. It's a strange behavior that sometimes they run and sometimes they don't. I can't really see your entire script but I can show you how I have adopted your code and made it work each time.
Note: your question is somehow related to ExecuteComplete ADODB Connection event not fired with adAsyncExecute parameter
I have added 3 stored procedures on my SQL server; sp_WaitFor5, sp_WaitFor10, sp_WaitFor20 to simulate the delay of query execution time.
As simple as
CREATE PROCEDURE sp_WaitFor5
AS
WAITFOR DELAY '00:00:05'
for all 3 delays.
Then in my Module1 I added a very simple code to call the custom class
Option Explicit
Private clsTest As TestEvents
Sub Main()
Cells.ClearContents
Set clsTest = New TestEvents
Call clsTest.StartingPoint
End Sub
Then I have renamed the class module to TestEvents and added a slightly modified version of your code
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
I have not really changed much except the extra parameter for Execute and some code which populates activesheet just to take the time.
Now, I am able to run different variations/configurations. I can rotate the execution time for connection objects. I can have cnA 5 sec, cnB 10sec, cnC 20sec. I can swap/adjust the execution times for each of the _ExecuteComplete events.
From testing on my own I can assure you all 3 are always executed.
Here's some logs based on a configuration similar to yours
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
In the above example as you can see, all 3 queries are fired asynchronously.
cnA returns the handle after 5 secs which makes cnB the first one to have the event ('refresh script') run in the hierarchy as cnC takes the longest.
Since cnB comes back first, it fires it's cnB_ExecuteComplete event procedure. The cnB_ExecuteComplete itself it's set to take some time execute (iterates 1 million times and fills in column B with random numbers. Note: cnA populates column A, cnB col B, cnC col C). Looking at the above log it takes exactly 30 seconds to run.
While the cnB_ExecuteComplete is doing its job /taking up resources (and as you know VBA is single threaded) the cnA_ExecuteComplete event is added up to the queue of TODO processes. So, you can think of it like a queue. While something is being taken care of the next thing has to just wait for its turn in the end.
If I change the configuration; cnA 5 sec, cnB 10 sec, cnC 20 sec and have each of the 'refresh scripts' iterate 1 million times then
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
Clearly proved my point from the first example.
Also, tried with cnA 5 sec, cnB 5 sec, cnC 5 sec
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
Which also completes/executes all 3.
Like I've said I can't see your entire code, maybe you're having an unhandled error somewhere in your code, maybe there is something misleading you to think that one _ExecuteComplete is not executing at all. Try to make changes to your code to reflect the one I have given you and run a few more text on our own. I will be looking forward to your feedback.
这篇关于使用ADODB运行多个异步查询 - 回调并不总是触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
