änderungen an den Daten in den Tabellen einer Datenbank kann man auf verschiedenste Arten durchführen – zum Beispiel über die Benutzeroberfläche. Gelegentlich werden Sie jedoch auch Daten per VBA ändern wollen. In diesem Falle gibt es eine ganze Reihe von Varianten, die normalerweise mit den Methoden der DAO-Bibliotheken abgebildet werden: Das öffnen eines Recordsets und ändern der enthaltenen Daten mit AddNew/Edit und Update, das Zusammenstellen einer SQL-Aktionsabfrage, die dann mit der Execute-Methode des Database-Objekts ausgeführt wird, oder der Einsatz gespeicherter Aktionsabfragen, die man mit der Execute-Methode des QueryDef-Objekts startet. Wir schauen uns die verschiedenen Varianten an und optimieren diese.
Performance und Stil
Der Grund für diesen Beitrag ist offensichtlich: Die verschiedenen Methoden zum ändern von Daten unterscheiden sich stark in der Performance. Wenn Sie eine Schleife über eine Datensatzgruppe durchlaufen und daraus entnommene Daten mit den DAO-Methoden AddNew und Update zu einem weiteren Recordset hinzufügen, dauert das schlicht und einfach länger als wenn Sie die gesamte Prozedur in einer einzigen Aktionsabfrage unterbringen.
Und das ist nicht nur das Bild, das man bei der Unterstützung anderer Entwickler vorfindet, sondern man selbst neigt ebenso dazu, erstmal den scheinbar einfacheren Weg über DAO zu wählen, bevor man auf den ersten Blick kompliziert erscheinende Aktionsabfragen zusammenstellt.
Und damit landen wir gleich beim anderen Aspekt: In vielen Fällen kostet das Entwickeln einer eleganten, performanten Lösung schlicht und einfach mehr Zeit als das stumpfe Herunterprogrammieren bekannter Codestrukturen – zumindest wenn man nicht über ausreichend Programmiererfahrung verfügt.
Dann hat man nämlich oft genug erst Datensatz für Datensatz mit DAO durchpflügt, bevor man den ersten Entwurf dann in einem oder mehreren Schritten zu einer zufriedenstellenden Lösung weiterentwickelt hat, die sowohl den Anspruch an die Performance als auch an die Eleganz befriedigt.
Eine solche Entwicklung können Sie in der Lösung zum Beitrag Datenmodelle vergleichen (www.access-im-unternehmen.de/916) nachvollziehen. Im vorliegenden Beitrag schauen wir uns ein etwas einfacheres Beispiel an und führen die Schritte vom ersten Ansatz bis zur perfekten Lösung durch.
Beispiel: Kundendaten archivieren
Im Beispiel dieses Beitrags wollen wir einfach bestimmte Kundendaten in eine andere Tabelle kopieren – beispielsweise, um diese zu archivieren. Die Kundendaten sollen nach bestimmten Kriterien ausgewählt werden – beispielsweise nach dem Zeitpunkt der letzten Bestellung. Liegt diese mehr als ein Jahr zurück, soll der Kunde in eine entsprechende Tabelle kopiert werden.
Außerdem soll die neue Tabelle noch ein weiteres Feld mit dem Datum der Archivierung enthalten (s. Bild 1).
Bild 1: Quell- und Zieltabelle
Daten mit bestimmten Kriterien aus einer Tabelle in eine weitere Tabelle kopieren, die überdies noch um weitere Daten ergänzt werden soll – das hört sich sehr kompliziert an. Also gehen wir das Ganze mal schön langsam Schritt für Schritt an – so, dass man zu jeder Zeit kontrollieren kann, was dort geschieht.
Start der Archivierung
Die Archivierung soll über Schaltflächen eines Formulars namens frmKundenArchivieren gestartet werden (s. Bild 2).
Bild 2: Formular mit Schaltflächen zum Starten der verschiedenen Archivierungen
Dieses Formular ruft die verschiedenen Varianten der Archivierung auf und übergibt dabei jeweils das im Textfeld txtStichtag gespeicherte Datum an die Funktionen, für die erste Schaltfläche etwa so:
Private Sub cmd1_Click() Dim intAnzahl As Integer intAnzahl = Datenkopieren_ DAOMitAbfrage(Me!txtStichtag) MsgBox intAnzahl _ & " Datensätze archiviert" End Sub
Die „sichere“ Variante
Wer noch nicht erfahren im Umgang mit Access ist, hat vielleicht noch nicht mit SQL gearbeitet und wird dementsprechend zunächst eine Abfrage wie oben beschrieben erstellen.
Dann wird er eine VBA-Funktion bauen, die wie die in Listing 1 aussieht. Sie sucht nach Kunden, die seit dem per Parameter übergebenen Stichtag keine Bestellung mehr durchgeführt haben und archiviert werden sollen, und liefert die Anzahl der betroffenen Datensätze zurück.
Public Function Datenkopieren_DAOMitAbfrage(datStichtag As Date) As Integer Dim db As DAO.Database Dim rstQuelle As DAO.Recordset Dim rstZiel As DAO.Recordset Dim datLetzteBestellung As Date Dim intAnzahl As Integer Set db = CurrentDb Set rstQuelle = db.OpenRecordset("SELECT * FROM tblKunden", dbOpenDynaset) Set rstZiel = db.OpenRecordset("SELECT * FROM tblKunden_Archiv WHERE 1 = 2", dbOpenDynaset) Do While Not rstQuelle.EOF datLetzteBestellung = Nz(DMax("Bestelldatum", "tblBestellungen", _ "KundeID = " & rstQuelle!KundeID), 0) If datLetzteBestellung < datStichtag Then If IsNull(DLookup("KundeID", "tblKunden_Archiv", "KundeID = " & rstQuelle!KundeID)) Then With rstZiel .AddNew !KundeID = rstQuelle!KundeID !KundenCode = rstQuelle!KundenCode !Firma = rstQuelle!Firma !Kontaktperson = rstQuelle!Kontaktperson !Strasse = rstQuelle!Strasse !Ort = rstQuelle!Ort !Region = rstQuelle!Region !PLZ = rstQuelle!PLZ !Land = rstQuelle!Land !Telefon = rstQuelle!Telefon !Telefax = rstQuelle!Telefax !ArchiviertAm = Now .Update intAnzahl = intAnzahl + 1 End With End If End If rstQuelle.MoveNext Loop Datenkopieren_DAOMitAbfrage = intAnzahl End Function
Listing 1: Die „sichere“ Variante zum Kopieren von Daten
Die Funktion erstellt ein Database-Objekt namens db sowie zwei Datensatzgruppen namens rstQuelle und rstZiel. Das Recordset-Objekt rstQuelle wird mit einem Verweis auf alle Datensätze der Tabelle tblKunden gefüllt, das Recordset-Objekt rstZiel mit einer Datenherkunft, die auf der Zieltabelle tblKunden_Archiv basiert, aber wegen des Kriteriums 1=2 keine Datensätze enthält.
Die Prozedur durchläuft nun alle Datensätze des Recordsets tblKunden. Dabei ermittelt sie zunächst mit dem Aufruf von DLookup das aktuellste Datum einer Bestellung aus der Tabelle tblBestellungen für die Datensätze, deren Feld KundeID mit dem Kunden des aktuellen Datensatzes aus rstQuelle übereinstimmt.
Für den Fall, dass für den Kunden noch gar keine Bestellung vorliegt, soll datLetzteBestellung den Wert 0 erhalten, was die Nz-Funktion mit entsprechendem zweiten Parameter bewerkstelligt.
Die folgende If…Then-Bedingung prüft, ob das in datLetzteBestellung gespeicherte Datum kleiner als datStichtag ist. Nur in diesem Fall geht es weiter, und zwar mit einer weiteren Prüfung: Wenn der Kunde bereits in der Archivtabelle gespeichert ist, soll dieser nicht erneut gespeichert werden.
Erst danach wird der neue Datensatz angelegt, und zwar mit der AddNew-Methode des Recordset-Objekts rstZiel. Die folgenden Anweisungen tragen jeweils den Wert eines Feldes der Quelltabelle in das entsprechende Feld der Zieltabelle ein.
Danach fügt die Funktion noch dem Feld ArchiviertAm das aktuelle Datum und die aktuelle Uhrzeit hinzu. Die Update-Methode sorgt für das Speichern des neu angelegten Datensatzes in der Tabelle tblKunden_Archiv.
Schließlich erhöht die Prozedur den Wert der Variablen intAnzahl, welche die Anzahl der übertragenen Datensätze zählen soll, um 1. Die MoveNext-Methode bewegt den Datensatzzeiger zum nächsten Datensatz, die Loop-Anweisung lässt die Funktion mit einem weiteren Durchlauf der Do While-Schleife beginnen.
Schließlich liefert die Funktion die in intAnzahl gespeicherte Anzahl betroffener Datensätze zurück.
Wenn Sie in Betracht ziehen, dass eine solche Vorgehensweise auf große Datenmengen angewendet wird, sind eine Menge Codezeilen abzuarbeiten, bis das gewünschte Resultat erreicht wird. Also kümmern wir uns darum, dies zu optimieren. Gleichwohl ist zu erwähnen, dass die hier eingesetzte Vorgehensweise nicht völlig abwegig ist. Erstens kommt sie fast komplett ohne den Einsatz von SQL-Anweisungen aus, was für viele Einsteiger erst in späteren Schritten folgt. Somit kommt man mit den grundlegenden DAO-Anweisungen sowie Domänenfunktionen über die Runden.
Das Entscheidende für den Entwickler dieser Zeilen mag aber sein, dass sich zu jeder Zeit im Debug-Modus beobachten lässt, was dort geschieht. Dies ist beim Einsatz etwa von SQL-Aktionsabfragen nur eingeschränkt möglich.
Optimierung 1: DMax auflösen
Die obige Funktion muss wirklich viel Arbeit leisten, weil sie jeden einzelnen Datensatz der Herkunftstabelle tblKunden durchläuft und diese dahingehend prüft, wie lange die letzte Bestellung des Kunden zurückliegt und ob dieser Datensatz bereits in der Tabelle tblKunden_Archiv gespeichert ist. Dabei ist für jeden Datensatz der Aufruf der DMax-Funktion fällig.
Wir wollen in kleinen Schritten optimieren und versuchen zunächst, die DMax-Funktion aus der Schleife herauszunehmen. Die Datenherkunft besteht bisher aus der Tabelle tblKunden. Deren Feld KundeID verwenden wir, um per DMax das Datum der letzten Bestellung zu ermitteln.
Wie bekommen wir nun die DMax-Anweisung aus der Do While-Schleife heraus Der erste Schritt dazu ist einfach: Wir erstellen einfach eine Abfrage auf Basis der Tabelle tblKunden und fügen ein weiteres Feld hinzu, welches das Datum der neuesten Bestellung ermittelt – dies wiederum durch den Einsatz der DMax-Funktion. Der Ausdruck für dieses Feld sieht so aus, die komplette Abfrage qryKundenNachBestelldatum finden Sie in Bild 3:
Bild 3: Abfrage basierend auf der Tabelle tblKunden und der neuesten Bestellung für diesen Kunden
NeuesteBestellung: Nz(DomMax("Bestell-datum";"tblBestellungen";"KundeID = " _
& [KundeID]);0)
Der Clou hierbei ist, dass die DMax-Funktion die ID des Kunden, für den die neueste Bestellung ermittelt werden soll, aus dem Feld KundeID der Datenherkunft bezieht.
Die Abfrage qryKundenNachBestelldatum nutzen wir nun als Datenherkunft für die zu übertragenen Daten. Das Feld NeuesteBestellung nutzen wir nun direkt als Kriterium der If…Then-Bedingung, anstatt diesen Wert erst noch in der Prozedur per DMax ermitteln zu müssen (s. Listing 2).
Public Function Datenkopieren_II(datStichtag As Date) As Integer ... Set rstQuelle = db.OpenRecordset("SELECT * FROM qryKundenNachBestelldatum", dbOpenDynaset) Set rstZiel = db.OpenRecordset("SELECT * FROM tblKunden_Archiv WHERE 1 = 2", dbOpenDynaset) Do While Not rstQuelle.EOF If rstQuelle!NeuesteBestellung < datStichtag Then ... Datenkopieren_II = intAnzahl End Function
Listing 2: Optimierung der Schleife durch übertragen von DMax in die Datenherkunft
Zeit messen
Nachdem wir nun eine erste vermeintliche Verbesserung angewendet haben, wollen wir natürlich auch wissen, ob sich dies auf die Performance auswirkt.
Die Zeit zwischen zwei Ereignissen messen Sie unter Windows am genauesten mit den beiden API-Funktionen QueryPerformanceFrequency und QueryPerformanceCounter. Die Erste liefert die Frequenz, die Zweite den Zählerstand des Prozessors. Wenn Sie diesen einmal beim Start der Messung einlesen und einmal zum Ende, brauchen Sie nur noch die Differenz durch die Frequenz zu teilen und erhalten die Zeit in Sekunden.
In Listing 3 werden zunächst die beiden API-Funktionen deklariert, außerdem drei Currency-Variablen zum Speichern der Frequenz, des Zählerstands beim Start und des Zählerstands nach Beenden der zu messenden Vorgänge. Die Prozedur, die beim Anklicken der Schaltflächen zum Starten der verschiedenen Varianten unseres Vorgangs ausgelöst wird, erledigt nun folgende Aufgaben:
Private Declare Function QueryPerformanceCounter Lib "Kernel32" (X As Currency) As Long Private Declare Function QueryPerformanceFrequency Lib "Kernel32" (Y As Currency) As Long Dim curFreq As Currency Dim curStart As Currency Dim curEnde As Currency Private Sub cmd1_Click() Dim intAnzahl As Integer Dim db As DAO.Database Set db = CurrentDb db.Execute "DELETE FROM tblKunden_Archiv", dbFailOnError QueryPerformanceFrequency curFreq QueryPerformanceCounter curStart intAnzahl = Datenkopieren_I(Me!txtStichtag) QueryPerformanceCounter curEnde Me!txtZeitI = (curEnde - curStart) / curFreq MsgBox intAnzahl & " Datensätze archiviert" End Sub
Listing 3: Einrichtung zum Messen der Zeit
- Leeren der Archivtabelle tblKunden_Archiv, damit immer gleich viele Datensätze übertragen werden
- Einlesen der Taktfrequenz des Prozessors
- Speichern des Zählers vor Beginn des zu messenden Vorgangs
- Start des Vorgangs
- Speichern des Zählers nach dem Vorgang
- Ausgabe der verstrichenen Zeit in Sekunden im Textfeld neben der Schaltfläche
Diese Zeilen fügen wir jeder Schaltfläche hinzu, nur der Aufruf der Funktion sowie das Textfeld für die Ausgabe der Zeit ändert sich. Wie Bild 4 zeigt, hat sich die erste Maßnahme durchaus gelohnt: Wir sparen rund 10% der Zeit gegenüber der ersten Variante ein.
Bild 4: Ergebnis der ersten beiden Varianten
Optimierung II:
Nun führen wir allerdings immer noch für jeden Datensatz einen Aufruf der DMax-Funktion aus. Wie können wir dies verhindern Eine Möglichkeit wäre, statt der DMax-Funktion mit einer Unterabfrage zu arbeiten. Diese formulieren wir in einer neuen Abfrage namens qryKundenNachBestelldatumMitGruppierung wie folgt (siehe auch Bild 5):
Bild 5: Abfrage mit Unterabfrage
NeuesteBestellung: (SELECT TOP 1 Bestelldatum FROM tblBestellungen WHERE tblBestellungen.KundeID = tblKunden.KundeID)
Nach dem Anlegen einer neuen Schaltfläche im Formular frmKundenArchivieren und dem Kopieren der Funktion Datenkopieren_II in eine neue Funktion namens Datenkopieren_III sehen wir uns an, was diese Optimierung bringt.
Und das Ergebnis ist beeindruckend: Wir sparen rund 97% gegenüber der Startkonfiguration! Aber Vorsicht: Zum Glück lassen wir immer noch die Anzahl der gespeicherten Datensätze mitlaufen. Und dieses gibt nun an, statt der vorhandenen 1456 Artikel nur noch 89 Datensätze archiviert zu haben. Haben wir vergessen, die schon archivierten Datensätze vorher zu löschen Nein. Aber wo liegt der Fehler Ganz einfach: Im Gegensatz zum Ausdruck
NeuesteBestellung: Nz(DomMax("Bestell-datum";"tblBestellungen";"KundeID = " & [KundeID]);0)
liefert die Unterabfrage nicht den Wert 0, sondern den Wert Null, wenn noch keine Bestellung für den Kunden vorliegt. Dies müssten wir nun in Form einer IIf-Funktion oder ähnlich in der Abfrage unterbringen.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo