Performance-Steigerung bei DB Zugriff aus VBA

Computer: Software & Programmierung Microsoft Access
#1

Hi Forum,
es funktoniert! Nach rund 800 Zeilen Code saugt meine DB nun fehlerfrei Daten von einem Marktdatenanbieter. Und zwar rund 90 Felder je Aktie bei z.Zt 600 Aktien pro Monat.

Mein Problem liegt bei der Verarbeitungsgeschwindigkeit. Am Ende sollen 10 Jahre (sprich 120 Monate) eingeladen werden. Bei einem aktuellen Schnitt von 8sec je Aktie dauert das allerdings 160std oder 6.6 Tage.

Habe den load für zwei Monate protokolliert. Die Verarbeitungszeit je Aktie schwankt beträchtlich zwischen 1sec und 26sec. Ich möchte den Durchschnittswert auf 3-4 sec senken.

Auf die Programmstruktur bin ich einigermaßen stolz. Alle Parameter für den Datendownload (jedes Feld muss einzeln geladen werden) werden in Abh. von Aktie und dem Feldbezeichner während der Laufzeit aus der Datenbank ermittelt. Daraus ergeben sich zahlreiche Datenbankzugriffe. Ihre Anzahl kann von Aktie zu Aktie und von Feld zu Feld schwanken. Ich gehe daher davon aus, dass meine Zugriffsmethode ineffizient ist.

Ich arbeite aktuell mit recordset objects, die mit SQL-Abfrageergebnissen bestückt werden. Hier ein Beispiel:
'==============================================================
Public Function getFormulaType(strFieldname As String) As Integer
'==============================================================
Set rst = CurrentDb.OpenRecordset("SELECT [typeFlag] " & _
"FROM tblJCFFormula " & _
"WHERE [Factor Name] = " & Chr(34) & strFieldname & Chr(34) & „;“)
getFormulaType = rst![typeFlag]
rst.Close
End Function

Kennt Ihr Methoden, die beim DB Zugriff schneller (am schnellsten) arbeiten? Bin für alle Anregungen Dankbar!

schönen Gruß
Dom

#2

Hallo, Dom!

Grundsätzlich ist die Arbeit mit Recordsets schon mal schneller als z. B. DoCmd.RunSQL.

Allerdings kostet logischerweise jeder Datenbankzugriff Zeit. Insofern solltest Du mal irgendwie mitloggen, was genau passiert. So kriegst Du die Stellen raus, die oft durchlaufen werden. Und vielleicht kann man das senken.

Beispiel: Sicherlich ist es einfach und übersichtlich, in einer Schleife über x Datensätze in y Feldern einen bestimmten Datentyp zu lesen. Aber diese y Felder werden x mal ausgelesen, und das macht’s wieder langsam. Hier wäre es besser, diese nur ein Mal auszulesen und in einer internen Struktur zu halten…

Gruß, Manfred

#3

Hi Manfred,
vielen Dank für Deine Antwort.

Insofern solltest Du mal irgendwie mitloggen

-> schon geschehen

Beispiel: Sicherlich ist es einfach und übersichtlich

-> ja ist es. Ich muss die Ergebniswerte, die vom Datenanbieter geliefert werden manipulieren, bevor ich sie in die DB schreiben kann. Daher muss ich neben dem Datentyp des Ziefeldes (Column-Eigenschaft) z.B. noch Min und Max Grenzen und dergleichen mehr ermitteln. (90 Felder mit unterschiedlichen Parameterausprägungen)

Hier wäre es besser, diese nur ein Mal auszulesen und in einer
internen Struktur zu halten…

-> Da die Parameter auch Null-Werte enthalten, wäre ich gezwungen, sie in einen 2D Variant Array zu schreiben. Anstatt der DB Abfragen müsste dann der Array durchsucht werden. Glaubst Du, dass das wirklich schneller ist? Kennst Du einen effizienten Weg einen Variant-Array zu durchsuchen. Kann man diesen indizieren?

Gruß Dom

#4

Hier wäre es besser, diese nur ein Mal auszulesen und in einer
internen Struktur zu halten…

Also verfolgen wir das mal weiter. In der Spitze werden je 10 Abfragen für 90 Felder ausgeführt. Einige sind bedingt. So lassen sich wohl die Schwankungen in der Durchlaufzeit je Aktie erklären. Gehen wir vom worst case aus, haben wir je 900 Abfragen für 600 Datensätze. Ich gebe Dir vollkommen recht, dass das ein gewichtiger Bremsklotz ist.

ARRAY:

  • muss nur einmal angelegt werden
  • ist global verfügbar
  • muss vom Typ Variant sein - Felder werden mit double, string und boolean bestückt
  • enthält 90 x 11 Felder
  • über Feld 1 müssen zur Laufzeit die Feldwerte für 2-11 gelesen werden
  • muss deutlich schneller sein als die Abfragevariante

Recordset:

  • anstatt eines Arrays wird ein 11x90 Recordset angelegt
  • eine Abfrage zur Erstellung
  • hier würde zur Laufzeit auf das Recordset zugegriffen um die Werte zu ermitteln
  • ist diese Variante schneller?
  • Vorteil: am aktuellen Code müssten weniger änderungen vorgenommen werden

Collection:

  • 10 Collections erforderlich
  • Reihenfolge der Felder muss in allen gleich sein
  • logische Verbindung zw. Feldnamen und Collection.Item muss zusätzlich programmiert werden

Die Array-Lösung klingt am besten - wenn sie denn schneller ist

Gruß Dom

#5

Hi DOM,

Nach rund 800 Zeilen Code saugt meine DB nun
fehlerfrei Daten von einem Marktdatenanbieter.

und diese Daten liegen wo? auf deiner Festplatte?
du greifst wie auf die Daten bzw. Datenbank zu? ODBC, OLE, DDE?

Mein Problem liegt bei der Verarbeitungsgeschwindigkeit.

ok, dann ändere die Basiseinstellungen von Access (Extras/Optionen)
z.B. keine (Datensatz)Sperrungen
z.B. OLE/DDE - Timeout
usw.

und lege die DB mit den einzulesenden Werten auf eine SATA2 Platte. Vorausgesetzt deine MDB befindet sich auch dort.
UND bei Datenbanken ganz wichtig: regelmäßiges DEFRAG nicht vergessen!! (bitte nicht das von MS verwenden)

Ich arbeite aktuell mit recordset objects, die mit
SQL-Abfrageergebnissen bestückt werden. Hier ein Beispiel:
'==============================================================
Public Function getFormulaType(strFieldname As String) As
Integer
'==============================================================
Set rst = CurrentDb.OpenRecordset("SELECT [typeFlag] " & _
"FROM tblJCFFormula " & _
"WHERE [Factor Name] = " & Chr(34) & strFieldname & Chr(34)
& „;“)
getFormulaType = rst![typeFlag]
rst.Close
End Function

Kennt Ihr Methoden, die beim DB Zugriff schneller (am
schnellsten) arbeiten? Bin für alle Anregungen Dankbar!

nun ja, dein Code entspricht der Funktion DLOOKUP die das gleiche liefert.

Dein Code ist zu aufwändig:

  • Recordset öffnen
  • filtern
  • Ergebnis = nur eine Information
  • Ergebnis an Funktion übergeben
  • Recordset schließen
  • Funktion beenden

Wenn du das pro Feld machst, ist das ja Wahnsinn…

Besser wäre:

  • Recordset öffnen
  • Info suchen (nicht filtern)
  • ggf. weitersuchen (nächste Info)
  • ggf. weitersuchen (nächste Info)
  • ggf. weitersuchen (nächste Info)
  • Recordset schließen

Das Zusammenfassen der Informationen in den Feldern wäre auch nicht schlecht. Kommt halt drauf an, was du daraus brauchst, und was drinsteht.
Beispiel: FELD3Info = Feld1 & Feld2 & Feld3 = z. B. „ABC“

wie gesagt, um Einzelwerte aus einer Tabelle zu holen ist DLOOKUP immer noch der beste Weg. Diese Funktion solltest du sofort verwenden und nicht noch eine eigene Funktion drumherum basteln.

Grüße aus Schwerin
Wolfgang
(Netwolf)

#6

Hi DOM,

Nach rund 800 Zeilen Code saugt meine DB nun
fehlerfrei Daten von einem Marktdatenanbieter.

und diese Daten liegen wo? auf deiner Festplatte?
du greifst wie auf die Daten bzw. Datenbank zu? ODBC, OLE,
DDE?

Hi NetWolf,
besten Dank zunächst. Ich werde jetzt ne Weile in meinen Code abtauchen. Aber zu Deiner Information:
Es handelt sich um einen professionellen Marktdatenanbieter (MDA). Dieser stellt über eine .dll einen Direktzugriff zur Verfügung. Die Datenbasis liegt inhouse auf einem Server. Der Zugriff ist ausgesprochen schnell und stellt kein Problem dar. Die VBA function des MDA liefert genau einen Wert und muss mit entsprechenden Parametern aufgerufen werden, die sich von Feld zu Feld ändern. Daher war es für mich naheliegend, eine entsprechende Parametertabelle in meiner DB anzulegen. Klappt, wie gesagt. Ist aber z.Zt noch langsam.

Zu meiner Historie: DB Grundlagen und Theorie zur genüge, MySQL Vorlesungsreihe, VBA Praxis in MS Excel. Das aktuelle Projekt kann als mein erstes ernsthaftes bezeichnet werden. Erstes Ziel: Dataload ans Laufen kriegen -ok. Zweites Ziel: Geschwindigkeit erhöhen -i.A. :wink:

Werde morgen an dieser Stelle berichten, wie es voran geht

Greetz Dom

#7

Mein Problem liegt bei der Verarbeitungsgeschwindigkeit.

ok, dann ändere die Basiseinstellungen von Access
(Extras/Optionen)
z.B. keine (Datensatz)Sperrungen
z.B. OLE/DDE - Timeout
usw.

Das prüfe ich auch noch

nun ja, dein Code entspricht der Funktion DLOOKUP die das
gleiche liefert.

Habe die rst.-functions durch dlookup ersetzt. Das macht sich in der Geschwindigkeit schon bemerkbar. Sie liegt aktuell bei 4.2 sec.

Besser wäre:

  • Recordset öffnen
  • Info suchen (nicht filtern)
  • ggf. weitersuchen (nächste Info)
  • ggf. weitersuchen (nächste Info)
  • ggf. weitersuchen (nächste Info)
  • Recordset schließen

Das hier bietet sich evtl. für die Parametertabelle an. Anstatt für jedes benötigte Feld einen DLOOKUP auszuführen, könnte man tatsächlich ein recordset anlegen, das die Tabelle komplett abbildet.
Vermutung:

  • Da das rst temporär ist und im Arbeitspeicher liegt, könnte der Zugriff schneller sein als bei einem Tabellenzugriff (richtig?)
  • Kann das Suchfeld im rst indiziert werden um eine weitere Beschleunigung zu realisieren?

Oder macht es mehr Sinn für diesen Zweck ein Array zu verwenden?

Schon mal vielen Dank und Gruß aus Düsseldorf
Dom

#8

Das hier bietet sich evtl. für die Parametertabelle an.
Anstatt für jedes benötigte Feld einen DLOOKUP auszuführen,
könnte man tatsächlich ein recordset anlegen, das die Tabelle
komplett abbildet.

ja, das sehe ich auch so

Vermutung:

  • Da das rst temporär ist und im Arbeitspeicher liegt, könnte
    der Zugriff schneller sein als bei einem Tabellenzugriff
    (richtig?)

ja

  • Kann das Suchfeld im rst indiziert werden um eine weitere
    Beschleunigung zu realisieren?

ja

Oder macht es mehr Sinn für diesen Zweck ein Array zu
verwenden?

eigentlich nicht, da du ja auf die Daten des rst direkt zugreifen kannst. Warum dann noch erst irgendwo zwischenspeichern?

Grüße aus Schwerin
Wolfgang
(Netwolf)

#9

Hi Wolfgang,
das war tatsächlich die Lösung! Habe es gestern umgesetzt und getestet. Ich bin von anfangs 8sec bei vielen RSTs über 4sec bei vielen DLOOKUPs auf nun 0.6sec mit einem globalen RST (seek-Zugriff)und vereinzelten DLOOKUPs runter. Bei der Syntax - vor allem beim Index - hab ich noch ein paar mal laut geflucht :wink: Jetzt bügle ich gerade den Code glatt.

Herzlichen Dank für die guten Hinweise und dass Du Dir die Zeit genommen hast.

sonnigen Gruß aus dem Rheinland
Dom