VBA: Abfragen von Access mit Excel. Warum so langsam?

Fand ich diesen code online Abfragen-Zugang und Eingabe der Daten in excel (2003), aber es ist viel langsamer als es sein sollte:

Sub DataPull(SQLQuery, CellPaste)
Dim Con As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim DBlocation As String, DBName As String
Dim ContractingQuery As String

If SQLQuery = "" Then

Else
    DBName = Range("DBName")
    If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb"

    DBlocation = ActiveWorkbook.Path
    If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\"

    Con.ConnectionString = DBlocation + DBName
    Con.Provider = "Microsoft.Jet.OLEDB.4.0"
    Con.Open

    Set RST = Con.Execute(SQLQuery)
    Range(CellPaste).CopyFromRecordset RST

    Con.Close
End If

End Sub

Das problem ist, dass dieser code dauert sehr lange. Wenn ich den Zugriff und führen Sie die Abfrage, dort dauert es etwa 1/10tel der Zeit. Gibt es trotzdem, um diese Fahrt? Oder aus irgendeinem anderen Grund könnte dies so lange? Alle meine Fragen sind einfache select-Abfragen mit einfachen where-statements und keine Verknüpfungen. Auch ein "select * from [test]" Abfrage dauert viel länger als es sollte.

EDIT: ich sollte angeben, dass die Zeile "Range(CellPaste).CopyFromRecordset RST" war das eine lange Zeit.

Beim laufen im Schritt durch den Modus, die Zeile code, die länger dauert?
Range(CellPaste).CopyFromRecordset RST
Wie viele Datensätze Sie abrufen?
Hmm. Das ändert alles. Ich glaube nicht, du wirst schneller als CopyFromRecorset. Auch, sollten Sie ernsthaft überlegen, diesen thread umbenennen und löschen alle irrelevanten Sachen in deinem post. Zugang hat eindeutig nichts mit dem problem zu tun, IMHO.

InformationsquelleAutor Dan | 2009-10-15

3

Ich bin kein Experte, aber ich Lauf fast genau den gleichen code mit guten Ergebnissen. Ein Unterschied ist, dass ich die Command Objekt sowie die Connection Objekt. Wo Sie
```
Set RST = Con.Execute(SQLQuery)
```
Ich
```
Dim cmd As ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = SQLQuery
Set RST = cmd.Execute
```
Ich weiß nicht, ob oder warum das helfen könnte, aber vielleicht wird es? 🙂

InformationsquelleAutor Ryan Shannon
1

Da Sie mit Access 2003, verwenden Sie DAO statt, es wird schneller mit der Jet-engine.

Sehen http://www.erlandsendata.no/english/index.php?d=envbadacexportdao für Beispielcode.

Beachten Sie, dass Sie sollten verwenden Sie niemals die "Neue" Schlüsselwort, so wird es zu unerwarteten Ergebnissen führen.

Dies ist problematisch, denn nicht jeder hat DAO aktiviert, so es wird es schwieriger machen, übergeben Sie diese Datei an andere Personen.
Das gleiche könnte gelten für ADO. Mein Verständnis ist, dass DAO ist Teil von, oder eng in Zusammenhang mit der Jet-engine von Access 2003. Es ist also höchst unwahrscheinlich, dass Sie würde nicht vorhanden sein, zusammen mit dem Zugang. Aber Sie können jederzeit überprüfen Sie die folgenden Speicherort: C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll (vorausgesetzt, DAO 3.6) Sie würden auch brauchen, um konvertieren Sie Ihre code zu spät ist gebunden, und nur die DAO-code, falls die Datei vorhanden war.
Es kommt auch mit Excel, da die Jet-db-engine ist eine Komponente von Office -, und Excel ist ziemlich eng.
Der OP sagte, "die Abfrage dort dauert es etwa 1/10tel der Zeit". Sie glaube nicht wirklich, dass der Wechsel zu DAO produzieren eine 1000% performance profitieren, tun Sie?
Nein, aber alle Dinge gleich sind, wird es schneller.

InformationsquelleAutor JimmyPena
1

Ich würde empfehlen, Sie zu erstellen, die Recordset explizit als vielmehr implizit durch die
Execute Methode.
Beim erstellen explizit können Sie seine CursorType und LockType-Eigenschaften, die Auswirkungen auf die Leistung.

Was ich sehe, sind Sie das laden der Daten in Excel, dann schließen Sie das recordset. Sie brauchen nicht zu aktualisieren, Datensätze zählen, etc... Also mein Rat wäre eine Recordset mit CursorType = adOpenForwardOnly & LockType = adLockReadOnly:
```
...
RST.Open SQLQuery, Con, adOpenForwardOnly, adLockReadOnly
Range(CellPaste).CopyFromRecordset RST
...
```
Recordset-Objekt (ADO)

Ich gab diesem einen Versuch. Es funktionierte aber nicht dazu führen, einen Unterschied in der Laufzeit. Trotzdem danke.

InformationsquelleAutor manji
1

Ich Ihren code benutzt, und zog in eine Tabelle 38 Spalten und 63780 Zeilen in weniger als 7 Sekunden - über das, was ich erwarten würde - und kleinere recordsets abgeschlossen fast sofort.

Ist das die Art von Leistung, die Sie erleben? Wenn dem so ist, ist es konsistent mit dem, was ich erwarten würde, mit einer ADO-Verbindung in Excel zu einer MDB-Backend.

Wenn Sie sehen viel langsamer als diese, dann muss es einige lokale Umwelt-Bedingungen, die Auswirkungen auf die Dinge.

InformationsquelleAutor Lunatik
1

Ich glaube nicht, dass du vergleichen wie mit wie.

In Access, wenn Sie eine Abfrage dataview, was passiert ist:
- eine bestehende offene Verbindung verwendet wird
  (und offen gelassen);
- ein recordset ist teilweise gefüllt
  mit den ersten paar Zeilen (und
  offen gehalten);
- die teilweise resultset angezeigt
  raster, die der Aufgabe gewidmet und
  optimiert für die systemeigene Daten zugreifen
  Methode Access beschäftigt (direkte Nutzung von
  die Access-Datenbank-Engine-DLLs,
  wahrscheinlich).
In Ihren VBA-code:
- eine neue Verbindung geöffnet wird (dann
  später geschlossen und freigegeben);
- das recordset vollständig aufgefüllt
  über alle Zeilen (dann später geschlossen und
  veröffentlicht);
- die gesamte Ergebnismenge wird in Lesen einen
  Excel ist generic UI mit non-native
  data access-Komponenten.
Ich denke, der wichtigste Punkt ist, dass die Datenansicht in Access nicht Holen die gesamte Ergebnismenge, bis Sie Sie Fragen, in der Regel, indem Sie die Navigation in die Letzte Zeile im resultset. ADO wird immer abrufen aller Zeilen in der Ergebnismenge.

Zweitwichtigsten wäre die Zeit zum Lesen der abgerufenen Zeilen (vorausgesetzt, dass eine vollständige resultset) in die UI-element und die Tatsache Excel ist nicht optimiert für den job.

Öffnen, schließen und lösen der verbindungen und recordsets sollte unerheblich sein, sind aber immer noch ein Faktor.

Ich glaube, Sie brauchen, um einige der timings auf jedem Schritt des Prozesses zu finden, die den Engpass. Beim Vergleich Zugreifen, sicherzustellen, Sie werden immer eine vollständige Ergebnismenge, z.B. prüfen Sie die Anzahl der zurückgegebenen Zeilen.

InformationsquelleAutor onedaywhen
1

Viele Formeln-Referenz der query. Versuchen temporarially einschalten manuelle Berechnung im makro-und ausschalten, wenn alle Ihre Abfragen fertig sind, aktualisieren.

Dieser Geschwindigkeit sollte es ein bisschen, aber noch nicht fix das zugrunde liegende problem.

InformationsquelleAutor Dan
0

Wenn Sie rufen Sie eine Menge von Datensätzen, es würde erklären, warum die Range(CellPaste)so lange dauert. (Wenn Sie die Abfrage ausführen in Access wäre es nicht abrufen aller Datensätze, aber wenn Sie die CopyFromRecordset-Methode es erfordert, dass alle Datensätze.)

Es ist ein MaxRows-parameter für die Methode CopyFromRecordset:
```
Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer
```
Versuchen, wenn die Einstellungen dies auf einen niedrigen Wert (etwa 10 oder so) ändert sich die Leistung.

InformationsquelleAutor Thorsten
0

Was über die folgenden Sanierungen oder Verbesserungen:
1. Einmal geöffnet, speichern Sie das recordset als xml-Datei (rst.saveToFile xxx) und dann Excel wieder öffnen.
2. Einmal geöffnet, legen Sie die recordset-Daten in ein array (rst.getRows-xxx), und kopieren Sie das array auf das aktive Blatt
3. Und, jederzeit, minimieren Sie alle Speicher - /Zugangsvoraussetzungen: öffnen Sie das recordset als read-only, forward-only, die Verbindung schließen, nachdem Sie die Daten auf Ihrer Seite, etc.
InformationsquelleAutor Philippe Grondier
0

Ich weiß nicht, ob es helfen wird, aber ich bin mit VBA und ADO-Verbindung zu einer Excel-Tabelle.

War es das abrufen der Datensätze Blitz-schnell (<5 Sekunden), aber dann plötzlich, es war schrecklich langsam (15 Sekunden zum abrufen eines Datensatzes). Dies ist, was führt mich zu Ihrem Beitrag.

Ich merkte, ich hatte ausversehen die Excel-Datei öffne mich (ich hatte es Bearbeiten).

Sobald ich es geschlossen, alles war Blitz schnell wieder.

InformationsquelleAutor RIF
0

Das problem 9 mal out of 10 ist zu tun mit den Cursor-Typ/Speicherort, den Sie verwenden.

Verwenden Sie dynamische Cursor über Netzwerkverbindungen können verlangsamen den Abruf von Daten, selbst wenn die Abfrage ausgeführt, sehr schnell.

WENN Sie möchten, um große Mengen von Daten sehr schnell, müssen Sie mit CursorLocation = adUseClient auf Ihre Verbindung. Dies bedeutet das Sie nur eine statische lokale cursor, so dass Sie nicht live aktualisiert, von anderen Benutzern.

Jedoch - wenn Sie nur die Daten zu Lesen, speichern Sie ADO gehen zurück in die DB für jeden einzelnen Datensatz, um änderungen zu prüfen.

Ich vor kurzem änderte sich dies als ich hatte eine einfache Schleife, füllen einer list-Element, und jede Schleife, die rund 0,3 s. Nicht zu langsam, aber auch auf 1.000 Datensätze, das ist 30 Sekunden! Ändern Sie nur den cursor Lage lässt sich der gesamte Prozess fertig in unter 1 Sekunde.

InformationsquelleAutor SwiftJr

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.