Laufen mehrere asynchrone Abfragen mit ADODB - callbacks nicht immer brennen

Ich habe eine Excel-Arbeitsmappe, feuert drei Abfragen zu einer Datenbank zu füllen drei Tabellen auf ausgeblendeten Blätter, und läuft dann drei "aktualisieren" - Skripte zu ziehen diese Daten durch drei sichtbare Präsentation Blatt (eine pro Abfrage). Läuft dieser synchron ist ziemlich langsam: Die Gesamt-Zeit zu aktualisieren, ist die Summe der Zeit jeder der drei Abfragen, plus die Summe der Zeit für jede "aktualisieren" - Skript auszuführen.

Ich bin mir bewusst, dass VBA ist nicht multi-threaded ist, aber ich dachte, es würde sein möglich, um die Dinge zu beschleunigen ein wenig, indem er die Anfragen aus asynchron (so dass einige clean-up-Arbeit zu tun, während Sie ausgeführt wurden), und dann die Bevölkerung /aktualisieren Sie für jedes Blatt, die Daten kommen zurück.

Schrieb ich mein Skript wie folgt aus (beachten Sie, dass ich hatte, um entfernen Sie die Verbindungszeichenfolgen, query-strings etc und machen Sie die Variablen generische):

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

Typische erwartet debugger-Ausgabe:

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

Den drei Abfragen zurückkommen können in unterschiedlicher Reihenfolge je nachdem, welche zuerst fertig ist, natürlich, so dass manchmal die typische Ausgabe sortiert ist anders - dies ist zu erwarten.

Manchmal jedoch ein oder zwei der cnX_ExecuteComplete Rückrufe nicht Feuer überhaupt. Nach einiger Zeit mit Debuggen, ich bin ziemlich sicher der Grund für dieses ist, dass, wenn ein recordset zurückgibt, während eine der Rückrufe wird derzeit ausgeführt wird, wird der Anruf nicht auftreten. Zum Beispiel:

Abfrage A, B und C alle Feuer zum Zeitpunkt 0
Abfrage Eine schließt das erste in time 3 cnA_ExecuteComplete feuert
Abfrage-B abgeschlossen, die zweite zum Zeitpunkt 5
cnA_ExecuteComplete noch läuft, so cnB_ExecuteComplete nie feuert
cnA_ExecuteComplete schließt bei Zeit 8
Abfrage-C schließt bei Zeit 10, cnC_ExecuteComplete feuert
Abfrage-C schließt bei Zeit 15

Bin ich richtig in meine Theorie, dass dies das Problem ist? Wenn dem so ist, ist es möglich, dies zu umgehen, oder den Anruf auf 'warten' bis der aktuelle code ausgeführt wurde, anstatt Sie einfach verschwinden?

Eine Lösung wäre, etwas zu tun, extrem schnell, während die cnX_ExecuteComplete Rückrufe (zB ein one-liner Set sheet1RS = pRecordset und einen check, um zu sehen, wenn Sie fertig sind, die noch vor der Ausführung der Skripte auffrischen synchron), so dass die chance für überschneidungen ist etwa null, sondern wollen wissen, ob es eine bessere Lösung gibt ersten.

+1 für gut geschriebene Frage
Schauen Sie auf Ihre Typical Expected debugger output:, wenn die cnA läuft 5 Sekunden dann die erste erwartete log-Vergangenheit Tables Cleared sollte eigentlich Debug.Print "cnA records received: " & Now. Du hast cnB records received ist ein bisschen irreführend nach deiner Logik. Ich bemerkt, Sie sagte, 5sec, 10sec, 20sec, aber nach, dass die erwartete sein sollte cnA, cnB, cnC. Können Sie dies erläutern?
Die Schätzungen sind grob, am besten - kann es am besten zu ignorieren diese Kommentare. Die Ausführungszeit der Abfragen zu variieren -, wo es heißt "ungefähr 5 Sekunden" können Sie nehmen, dass bedeutet, überall von 3 Sekunden bis 8, und "etwa 10 Sekunden" kann überall von 7 Sekunden auf 15. Es ist durchaus möglich, dass cnB beendet, bevor cnA oder cnC beendet, bevor cnB (oder etwas wirklich verlangsamt cnA und cnC beendet ist, Ehe es) - es werden alle Abfragen gegen einen externen server bedeutet, es ist völlig abhängig von der Netzwerk -, Gegenwart-server zu laden, record-locking, etc etc.
vielen Dank für die Klarstellung. Ich habe für Sie beantwortet sehen, ob das hilft

InformationsquelleAutor Kai | 2014-02-21

Ich denke, ich bin nicht in der Lage zu erklären, warum manche Ihre "Skripte auffrischen" nicht immer Feuer. Es ist ein seltsames Verhalten, manchmal laufen Sie und manchmal Sie nicht. Ich kann nicht wirklich sehen, Ihre gesamte Skript, aber ich kann Ihnen zeigen, wie ich angenommen haben, Ihren code und machte es jedes mal zu arbeiten.

Hinweis: Ihre Frage irgendwie mit ExecuteComplete Connection-ADODB-Ereignis nicht ausgelöst, mit adAsyncExecute-parameter

Ich habe 3 gespeicherte Prozeduren auf meinem SQL-server; sp_WaitFor5, sp_WaitFor10, sp_WaitFor20 zu simulieren, die Verzögerung der Ausführungszeit von Abfragen.

So einfach wie

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

für alle 3 delays.

Dann in meinem Module1 ich habe einen sehr einfachen code, der zum Aufruf der benutzerdefinierten Klasse

Option Explicit

Private clsTest As TestEvents

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

Dann habe ich Sie umbenannt in Klasse Modul TestEvents und fügte hinzu, eine leicht modifizierte version des Codes

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

Habe ich nicht wirklich viel geändert, außer das zusätzliche parameter für Execute und einige code füllt activesheet einfach zu die Zeit nehmen.

Nun, ich bin in der Lage, verschiedene Varianten/Konfigurationen. Ich kann drehen Sie die Ausführungszeit für connection-Objekte. Ich kann cnA 5 sec cnB 10sec cnC 20sec. Ich kann swap/Anpassung der Ausführungszeiten für jede der _ExecuteComplete Veranstaltungen.

Vom testen auf meinem eigenen ich kann Ihnen versichern, alle 3 sind immer ausgeführt.

Hier einige Protokolle, basierend auf einer Konfiguration ähnlich zu verkaufen

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

Im obigen Beispiel können Sie sehen, alle 3 Anfragen werden asynchron abgefeuert.

cnA gibt das handle nach 5 Sekunden, das macht cnB der erste, der das Ereignis ('refresh script') in der Hierarchie als cnC dauert am längsten.

Seit cnB kommt zurück erste, feuert es cnB_ExecuteComplete Ereignis-Prozedur. Die cnB_ExecuteComplete selbst wenn es sich auf nehmen Sie sich einige Zeit ausführen (iteriert 1 million mal und füllt in Spalte B mit Zufallszahlen. Hinweis: cnA füllt Spalte A, cnB col B -, cnC-col-C). Wenn man die oben log, dauert es genau 30 Sekunden zu laufen.

Während der cnB_ExecuteComplete Ihren job macht /Aufnahme von Ressourcen (und wie Sie wissen, VBA ist single threaded) die cnA_ExecuteComplete Ereignis wird Hinzugefügt, bis die Warteschlange der TODO-Prozesse. Also, man kann sich das wie eine Warteschlange. Während etwas aufgepasst, das nächste Ding muss nur warten, bis die Wendung am Ende.

Wenn ich die Konfiguration ändern; cnA 5 sec cnB 10 sec cnC 20 s und jede der 'Skripte auffrischen' Iteration 1 Millionen mal, dann

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

Eindeutig bewiesen, mein Punkt aus dem ersten Beispiel.

Auch versucht mit cnA 5 sec cnB 5 sec cnC 5 Sek.

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

Die auch abgeschlossen ist/führt alle 3.

Wie ich schon sagte, ich kann nicht sehen Sie Ihre gesamte code, vielleicht hast du eine nicht behandelte Fehler irgendwo in deinem code, vielleicht gibt es etwas, täuschen Sie zu denken, dass man _ExecuteComplete wird nicht ausgeführt überhaupt. Versuchen, die änderungen an Ihrem code zu reflektieren, die ich Euch gegeben haben, und führen Sie ein paar mehr text auf unsere eigenen. Ich werde freuen uns auf Ihr feedback.

Interessant..., ich habe meinen code wieder in diesem format, wo jeder _ExecuteComplete nimmt Zeit in Anspruch, und es scheint jetzt zu funktionieren, wie beworben ('queueing' jeden Rückruf, wenn man die derzeit ausgeführt werden). Ich werde weiter Debuggen, um zu sehen, ob das problem wieder Auftritt. Es kann gut etwas gewesen sein, so dumm wie eine fehlende Debug.Print in einem meiner _ExecuteCompletes, das bedeutete, ich war nicht zu sehen, einer von Ihnen auf output, oder einen anderen Freitag Nachmittag WTF. Verlassen Sie die Frage offen, bis ich bin zuversichtlich, was das problem war Weg gegangen ist, und wenn ja, ich nehme an diese Antwort. Danke!
Ich bin froh, dass es Sie gab einige Einblicke in das Thema. Können Sie erklären, was meinst du mit I changed my code back to this format where each _ExecuteComplete takes a while to complete?
Sicher. In meiner Frage, jeder _ExecuteComplete dauert 'x' Sekunden komplett durch den code zur Aktualisierung der Präsentation Blatt. Ich dachte, das war es, was kann die Ursache von Rückrufen 'drop', also entfernte ich diesen code und ersetzt es mit einem quick-check, um zu sehen, wenn alle 3 _ExecuteComplete berufen worden und habe dann die refresh-code danach (txt.do/1gvj). Dies machte jede _ExecuteComplete braucht fast keine Zeit überhaupt zu vervollständigen, so könnte ich zu 99% überzeugt, Sie waren nicht 'überlappen'. Etwas hacky, aber es schien eine anständige Lösung auf Zeit.
richtig, ich möchte nicht sagen, dass das für Sie sicher noch nicht, aber ich bin still denken Sie vielleicht fehlte ein Debug.Print in einem der _ExecuteCompletes
Es ist durchaus möglich,

InformationsquelleAutor

1

Ich bin mir auch nicht sicher, warum das Ereignis nicht immer entlassen werden, für Sie.

Für mich ist der test immer funktioniert (getestet mit 100 000 Zeilen und 14 Spalten), aber ich bin nicht sicher über die Größe der Datenbank und Komplexität der Abfragen, die Sie ausführen.

Habe ich eine Bemerkung aber.

Es gibt einen wichtigen Unterschied zwischen den ExecuteComplete und die FetchComplete Veranstaltung.

Den ExecuteComplete feuert, nachdem ein Befehl beendet die Ausführung (in Ihrem Beispiel wird das command-Objekt wird intern erstellt von ADO). Dies bedeutet nicht zwangsläufig, dass alle Datensätze die geholt wurden, von der Zeit, die dieser callback wird ausgelöst.

Daher, wenn Sie das zurückgegebene recordset zu arbeiten, sollten Sie hören die fetchComplete Rückruf, dass nur ausgelöst, wenn das recordset wurde vollständig abgerufen.

InformationsquelleAutor Trace
0

Kann ich Ihnen eine Antwort geben, die Ihnen helfen, einige der Zeit, aber nicht die ganze Zeit.

Manchmal Recordset.Öffnen oder den Befehl.Ausführen ignoriert die AdAsynchFetch parameter.

Ist zu sagen: das problem manifestiert sich sofort, wenn Sie anfordern, und es ist nicht ein Problem mit der Anwendung in einem nicht reagierenden Zustand bei ADODB zurück ruft mit einem recordset gefüllt.

Glücklicherweise ist dies etwas, das Sie aufspüren können, in dem code; und es gibt drei Dinge, die auftreten, wenn AdFetchAsynch ignoriert:
1. Der Execute-oder Open-Methode synchron ausgeführt und füllt ein
  recordset.
2. Die ExecuteComplete Ereignis wird nie ausgelöst.
Können Sie sehen, wo ich gehe mit diesem...

Wenn das recordset-anfordern-code erkennt einen recordset öffnen, bevor es beendet wird, übergeben Sie den recordset öffnen, die gerade in Ihrem bestehenden _FetchComplete Ereignisprozedur:
```
Set m_rst = New ADODB.Recordset  ' declared at module level With Events

With m_rst

    Set .ActiveConnection = ThisWorkbook.MyDBConnection

    .CursorType = adOpenForwardOnly

     Err.Clear
    .Open SQL, , , , adCmdText + adAsyncFetch

End With

If m_rst.State = adStateOpen Then

    ' This block will only run if the adAsyncFetch flag is ignored
    If m_rst.EOF And m_rst.BOF Then
        MsgPopup "No matching data for " & DATASET_NAME, vbExclamation + vbOKOnly, "Empty data set", 90
    ElseIf m_rst.EOF Then
        m_rst.MoveFirst
        m_rst_FetchComplete Nothing, GetStatus(m_rst), m_rst
    Else
        m_rst_FetchComplete Nothing, GetStatus(m_rst), m_rst
    End If

    Set m_rst = Nothing

ElseIf m_rst.ActiveConnection.Errors.Count > 0 Then

    m_rst_FetchComplete m_rst.ActiveConnection.Errors(0), adStatusErrorsOccurred, m_rst
    Set m_rst = Nothing

ElseIf Err.Number <> 0 Then

    MsgPopup "Microsoft Excel returned error &H" & Hex(Err.Number) & ": " & Err.Description, vbCritical + vbOKOnly, "Error requesting " & DATASET_NAME, 60
    Set m_rst = Nothing

ElseIf m_rst.State < adStateOpen Then

    MsgPopup "Microsoft Excel was unable to request data for " & DATASET_NAME & ": no error information is available", vbCritical + vbOKOnly, "Error requesting " & DATASET_NAME, 60
    Set m_rst = Nothing

Else

    ' Fetch progess is not available with the OLEDB driver I am using
    ' m_rst_FetchProgress 0, 100, GetStatus(m_rst), m_rst

End If
```
Offensichtlich wird dies nutzlos sein, wenn die _FetchComplete Ereignis wird nie ausgelöst: 'öffnen' wird asynchron ausgeführt und die Methode beendet sich mit einem recordset in Staat adStateConnecting oder adStateFetching und Sie sind ganz angewiesen auf die m_rst_FetchComplete Ereignisprozedur.

Aber das behebt das Problem für einige Zeit.

Weiter: Sie müssen prüfen, ob Application.EnableEvents ist nie auf false gesetzt, wenn Sie vielleicht ein recordset Anfrage in den äther. Ich vermute, dass du gedacht hast, aber es ist die einzige andere Sache, die ich mir denken kann.

Auch:

Einen Tipp für Leser, die ADODB-Codierung: verwenden Sie adCmdStoredProc aufrufen und Ihre gespeicherten Abfrage oder recordset-Rückkehr-Funktion von Namen anstelle der Verwendung von 'SELECT * FROM' und adCmdText.

Einer spät Antwort hier, aber auch andere Menschen begegnen dem gleichen problem.

InformationsquelleAutor Nigel Heffernan

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.