Meine Webseite ist umgezogen. Diese bisherigen Seiten sind nur noch abrufbar, bis alle Inhalte übertragen sind. Zur neuen Seite ...
Autor: Helma Spona
Betrifft: Access, Datenbanken, SQL, Datenabfragen, Datenmanipulation
Systemanforderungen: Access 97 oder höher
Download: PDF-Version
Große Datenmengen lassen sich mit Access auf vielfältige Weise bearbeiten. Die zwei am häufigsten Verwendeten sind das DAO-Objekt-Modell und SQL. Darüber hinaus gibt es aber auch noch die Möglichkeit, Abfragen auszuführen, um Daten zu manipulieren. Die Frage, welches die schnellste Möglichkeit ist, werden viele Entwickler prompt mit "SQL" beantworten. Aber ist das wirklich in jedem Fall so?
Die Frage nach der schnellsten Methode zur Datenmanipulation ist nicht so leicht zu beantworten, wie es auf den ersten Blick scheint. Nicht immer entspricht das Ergebnis nämlich den Erwartungen. Zu erwarten wäre beispielsweise, dass die Nutzung von Aktionsabfragen oder SQL-Anweisungen gleich schnell ist, da in beiden Fällen von Access die gleiche SQL-Anweisung ausgeführt wird. Dennoch ist dies nur beim Ändern vorhandener Datensätze der Fall. Ganz anders sieht es beim Erzeugen und Löschen von Tabellen und beim Löschen und Einfügen der Datensätze aus.
Am besten lässt sich die Geschwindigkeit der einzelnen Methoden vergleichen, wenn Sie mehrmals hintereinander die gleiche Aktion durchführen und die dazu notwendige Zeit messen und berechnen. Dabei hat sich ergeben, dass vor allem beim Erstellen von Tabellen SQL die Nase vorn hat. Um 1000 mal eine Tabelle zu erstellen und wieder zu löschen, werden mit Hilfe von SQL nur 9 Sekunden, bei Nutzung der CreateTableDef-Methode jedoch ganz 15 Sekunden benötigt. DAO ist somit über 33% langsamer. Auch wenn Sie eine Tabellenerstellungsabfrage verwenden, die die gleiche SQL-Anweisung verwendet, wie sie beim Test mit SQL verwendet wurde, ergibt sich noch eine Zeitdifferenz von drei Sekunden gegenüber SQL.
Wenn Sie in eine Tabelle, die aus drei Feldern, einem numerischen, einem Textfeld und einem Memo-Feld besteht, 10.000 Datensätze einfügen, ergibt sich ein ganz anderes Bild. Hier ist SQL mit Abstand am langsamsten. Mit einer SQL-Anweisung benötigen Sie nämlich 38 Sekunden, während DAO hier mit 31 Sekunden auskommt und eine Anfügeabfrage, die 10.000 Mal aufgerufen wird, immer noch mit 30 Sekunden auskommt.
Wenn Sie vorhandene Datensätze ändern möchten, werden Sie dabei feststellen, dass hier SQL und Aktionsabfragen gleich schnell sind und DAO am langsamsten. Das lässt sich allerdings dadurch erklären, dass Sie mit DAO alle Datensätze durchlaufen und einzeln ändern müssen, während das mit SQL und somit auch in Aktionsabfragen mit einer einzigen SQL-Anweisung für alle Datensätze möglich ist. Das gleiche Ergebnis ergibt sich, wenn Sie nur bestimmte Datensätze abhängig von einem Kriterium ändern möchten. Allerdings wäre hier zu erwarten, dass das länger dauert als ohne Kriterium. Dem ist aber nicht so. Die Geschwindigkeit ist zumindest bei 20.000 Datensätzen noch gleich.
Wenn Sie allerdings mit der gleichen VBA-Schleife alle Datensätze nacheinander löschen, gibt es die nächste Überraschung. Bei 20.000 Datensätzen brauchen Sie mit DAO dazu weniger als eine Sekunde, mit SQL hingegen zwei Sekunden und eine Löschabfrage braucht immer noch eine Sekunde dafür. Hier ist DAO also am schnellsten.
| Aktion |
SQL |
DAO |
Abfragen |
|---|---|---|---|
Tabellen erstellen und löschen 1000 x |
9 |
15 |
12 |
Datensätze einfügen 10.000 Stk. |
38 |
31 |
30 |
Datensätze ändern (20.000) |
1 |
2 |
1 |
Datensätze abhängig von einer Bedingung ändern (20.000) |
1 |
2 |
1 |
Datensätze löschen (20.000) |
2 |
0 |
1 |
Jeder Performance-Test ist natürlich relativ. Er ist nicht nur abhängig von der Hardware auf der getestet wurde, sondern auch vom Betriebssystem, der Access-Version und dem Code, der zum Testen verwendet wurde.
Für die vorstehenden Vergleiche wurde ein Rechner mit Intel Pentium III Mobile mit 500 Mhz verwendet. Als Betriebssystem wurde Windows XP Service Pack 1 eingesetzt und der Code wurde unter Access 2002 ausgeführt.
Wichtig ist aber natürlich auch, welcher Code für die Zeitmessung und die Ausführung der SQL-Anweisungen und Abfragen eingesetzt wurde. Listing 1 zeigt die Prozedur für die Zeitmessung. An diese Prozedur werden Anfangszeit und aktuelle Zeit als Parameter übergeben. Prozedur berechnet die Differenz und gibt die Zeit im Testfenster aus.
Diese Prozedur wird dann in einer weiteren Prozedur aufgerufen, die in einer Schleife, die von 1 bis zu einem per Variablen definierten Endwert läuft, die Testprozeduren aufruft. Am Ende der Schleife wird die Prozedur Zeitmessung aufgerufen. Nach der Ausgabe der Zeit, wird die gleiche Schleife für eine andere Prozedur ausgeführt, die dann eine andere Methode bspw. DAO verwendet.
Sub Zeitmessung(datAnf As Date, datEnde As Date)
Dim datZeit As Date
datZeit = datEnde - datAnf
Debug.Print "Zeit: " & Format(datZeit, "")
End Sub
Sub testTabellen()
Dim datAnfang As Date
Dim lngAnz As Long
lngAnz = 1000
'SQL
Debug.Print "SQL"
datAnfang = Now
For lngI = 1 To lngAnz
TabelleErstellenSQL
TabelleLoeschenSQL
Next lngI
Zeitmessung datAnfang, Now
...
End Sub
Wie die SQL-Anweisungen und Abfragen ausgeführt werden, spielt natürlich auch eine Rolle. Listing 2 zeigt beispielhaft zwei Prozeduren zum Ändern der Datensätze. Die Prozedur DatenAendernSQL verwendet dazu eine UPDATE-Anweisung, die über die Execute-Methode des Database-Objekts ausgeführt wird. Auf gleiche Weise verfährt die Prozedur DatenAendernAbfrage. Sie übergibt an die Execute-Methode aber keine SQL-Anweisung, sondern den Namen der auszuführenden Abfrage. Analog werden auch die SQL-Anweisungen bzw. die Abfragen zum Löschen und Erstellen der Daten definiert. Da beide Prozeduren sich so ähnlich sind, ist es natürlich um so verwunderlicher, dass es solche erheblichen Unterschiede beim Einfügen von Datensätzen gibt.
Sub DatenAendernSQL()
Const strSQL = "UPDATE test SET test.ID = 2, test.Zeichen = ""XYZ"""
Dim objDB As DAO.Database
Set objDB = Application.CurrentDb
objDB.Execute strSQL
Set objDB = Nothing
End Sub
Sub DatenAendernAbfrage()
Dim objDB As DAO.Database
Set objDB = Application.CurrentDb
objDB.Execute "DatAendern"
Set objDB = Nothing
End Sub
Sub DatenAendernDAO()
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Set objDB = Application.CurrentDb
Set objRS = objDB.OpenRecordset("test3")
objRS.MoveFirst
Do While objRS.EOF = False
objRS.Edit
objRS.Fields("ID") = 2
objRS.Fields("Zeichen") = "XYZ"
objRS.Fields("langerText") = _
"Text im Memofeld"
objRS.Update
objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing
Set objDB = Nothing
End Sub
Die Prozedur DatenAendernDAO öffnet die Tabelle als Recordset-Objekt und durchläuft die einzelnen Datensätze in einer While-Schleife. Obwohl der Code daher wesentlich aufwändiger aussieht, ist die Zeitdifferenz verhältnismäßig gering.
Wenn Sie Datenzugriffe und Anweisungen zur Datenbankmanipulation hinsichtlich Performance optimieren möchten, sollten Sie SQL-Anweisungen verwenden, wenn Sie Tabellen erstellen, manipulieren und löschen möchten. Zum Einfügen von Datensätzen sind hingegen Aktionsabfragen optimal. Wo dies nicht möglich ist, weil komplexe Berechnungen erforderlich sind, die nicht mit SQL zu bewältigen ist, stellt DAO eine Alternative dar. Zum Ändern von Datensätzen sollten Sie wahlweise SQL oder Aktionsabfragen einsetzen, zum Löschen hingegen wieder DAO.
Alle Inhalte wurden nach bestem Wissen und Gewissen zusammengestellt. Ich übernehme für Fehlerfreiheit allerdings keine Gewähr und hafte keinesfalls für Folgen, die sich aus Fehlern oder unsachgemäßem Gebrauch des Codes und der Inhalte ergeben.
Code und Beispiele dürfen für den privaten Gebrauch frei verwendet werden. Eine Veröffentlichung (auch auszugsweise) sowohl online wie auch auf klassischen Medien ist nur nach meiner ausdrücklichen Zustimmung möglich. Verlinkung der Artikel ist jedoch erlaubt und erwünscht, solange immer auf die Webseite und nicht die PDF-Datei verlinkt wird.