Запуск нескольких асинхронных запросов с помощью ADODB - обратные вызовы не всегда срабатывают

У меня есть книга Excel, которая запускает три запроса в базу данных для заполнения трех таблиц на скрытых листах и затем запускает три сценария обновления, чтобы вытащить эти данные до трех видимых презентационных листов (по одному на запрос). Запуск этого синхронно происходит довольно медленно: общее время обновления - это сумма времени каждого из трех запросов плюс сумма времени для каждого "обновления" script для запуска.

Я знаю, что VBA не многопоточен, но я подумал, что можно немного ускорить процесс, уволив запросы асинхронно (таким образом, позволяя выполнять некоторую работу по очистке, пока они выполнялись), а затем выполняйте работу по популяции/обновлению для каждого листа по мере возврата данных.

Я переписал свой script следующим образом (обратите внимание, что мне пришлось удалить строки подключения, строки запроса и т.д. и сделать общие переменные):

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 и проверить, все ли они выполнены до запуска синхронных обновлений), поэтому вероятность из них перекрываются около нуля, но хотят знать, есть ли лучшее решение в первую очередь.

Ответы

Ответ 1

Я думаю, я не могу объяснить, почему некоторые ваши "обновления скриптов" не всегда срабатывают. Это странное поведение, которое иногда происходит, а иногда и нет. Я не могу увидеть весь ваш script, но я могу показать вам, как я принял ваш код и сделал его работу каждый раз.

Примечание: ваш вопрос каким-то образом связан с ExecuteComplete Событие подключения ADODB, не запущенное с помощью параметра adAsyncExecute

Я добавил 3 хранимых процедуры на моем SQL-сервере; 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 только для того, чтобы уделить время.

Теперь я могу запускать различные варианты/конфигурации. Я могу повернуть время выполнения для объектов соединения. Я могу иметь 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 первым, у которого есть событие ('refresh script'), запускается в иерархии, поскольку cnC занимает самое длинное.

Так как cnB возвращается первым, он запускает процедуру cnB_ExecuteComplete события. cnB_ExecuteComplete сам он должен выполнить некоторое время выполнения (итерации 1 миллион раз и заполняет столбец B случайными числами. Примечание: cnA заполняет столбец A, cnB col B, cnC col C). Глядя на приведенный выше журнал, требуется ровно 30 секунд.

Пока cnB_ExecuteComplete выполняет свою работу/занимает ресурсы (и, как вы знаете, VBA однопоточно), событие cnA_ExecuteComplete добавляется в очередь процессов TODO. Таким образом, вы можете думать об этом как о очереди. В то время как кое-что позаботится о следующем, нужно просто ждать своей очереди в конце.

Если я изменю конфигурацию; cnA 5 секунд, cnB 10 секунд, cnC 20 секунд и каждый из "сценариев обновления" повторяется 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

Явно доказал свою точку зрения из первого примера.

Кроме того, с помощью 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 не выполняется вообще. Попытайтесь внести изменения в свой код, чтобы отразить то, что я вам дал, и запустить еще один текст самостоятельно. Я буду с нетерпением ждать ваших отзывов.

Ответ 2

Я также не уверен, почему это событие не всегда увольняется за вас.
Для меня тест всегда работал (тестировался со 100 000 строк и 14 столбцов), но я не уверен в размере вашей базы данных и сложности запросов, которые вы выполняете.

У меня есть замечание.

Существует важная разница между событиями ExecuteComplete и FetchComplete.

ExecuteComplete запускается после завершения выполнения команды (в вашем примере объект команды внутренне создается ADO). Это не обязательно означает, что все записи были получены к моменту срабатывания этого обратного вызова.

Следовательно, если вам нужен возвращенный набор записей для работы, вы должны прослушать обратный вызов FetchComplete, который запускается только тогда, когда набор записей был полностью выбран.

Ответ 3

Я могу дать вам ответ, который поможет вам некоторое время, но не все время.

Иногда ваш Recordset.Open или ваш Command.Execute игнорирует параметр AdAsynchFetch.

То есть: проблема проявляется сразу же, когда вы запрашиваете, и это не проблема с приложением в состоянии безответственности, когда ADODB обращается с заполненным набором записей.

К счастью, это то, что вы можете заманить в код; и есть три вещи, которые происходят, когда AdFetchAsynch игнорируется:

Метод Execute или Open работает синхронно и заполняет записей.
Событие ExecuteComplete никогда не возникает.

Вы можете увидеть, где я собираюсь с этим...

Если ваш запрос на получение набора записей обнаруживает открытый набор записей перед его выходом, пропустите открытый набор записей прямо в существующую процедуру _FetchComplete:

 <Код>
Set m_rst = Новый ADODB.Recordset ', объявленный на уровне модуля. С событиями

Очевидно, что это будет бесполезно, если событие _FetchComplete никогда не возникает: "open" выполняется асинхронно, и метод завершается с помощью набора записей в состоянии adStateConnecting или adStateFetching, и вы полностью полагаетесь на m_rst_FetchComplete.

Но это исправляет проблему некоторое время.

Далее: вам нужно проверить, что Application.EnableEvents никогда не устанавливается в false, если у вас может быть запрос набора записей в эфире. Я предполагаю, что вы об этом подумали, но это единственное, что я могу придумать.

также:

Совет для читателей, которые являются новичками в кодировании ADODB: рассмотрите возможность использования adCmdStoredProc и вызов вашего сохраненного запроса или функции возвращаемого набора записей по имени вместо использования "SELECT * FROM" и adCmdText.

Поздний ответ здесь, но другие люди столкнутся с одной и той же проблемой.