Performance-Optimierung: DAO versus SQL

Performance-Optimierung: DAO versus SQL
http://www.helma-spona.de
Performance-Optimierung: DAO versus
SQL
von Helma Spona
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?
ist somit über 33% langsamer. Auch wenn
Sie
eine
Tabellenerstellungsabfrage
verwenden, die die gleiche SQLAnweisung verwendet, wie sie beim Test
mit SQL verwendet wurde, ergibt sich
noch eine Zeitdifferenz von drei Sekunden
gegenüber SQL.
Info
Betrifft:
Access, Datenbanken, SQL,
Datenabfragen, Datenmanipulation
Systemanforderungen:
Access 97 und
höher
Seitenzahl: 4
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 SQLAnweisungen gleich schnell ist, da in
beiden Fällen von Access die gleiche SQLAnweisung 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.
Bild 1: Geschwindigkeitsvergleiche beim Erstellen und
Löschen von Tabellen
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 SQLAnweisung 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.
PERFORMANCE VERGLEICHEN
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
1
Performance-Optimierung: DAO versus SQL
http://www.helma-spona.de
Aktion
Tabellen
erstellen
und
löschen
1000 x
Datensätze
einfügen
10.000 Stk.
Datensätze
ändern
(20.000)
Datensätze
abhängig
von einer
Bedingung
ändern
(20.000)
Datensätze
löschen
(20.000)
Bild 2: Am schnellsten lassen sich Datensätze mit DAO
erzeugen
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 SQLAnweisung 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.
SQL
9
DAO
15
Abfragen
12
38
31
30
1
2
1
1
2
1
2
0
1
Tabelle 1: Übersicht über den Zeitbedarf für definierte
Aktionen
WIE WURDE GETESTET?
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 AccessVersion 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.
Bild 3: Performance der verschiedenen Methoden beim
Ändern und Löschen von Datensätzen
Wenn Sie allerdings mit der gleichen VBASchleife 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.
Diese Prozedur wird dann in einer
weiteren Prozedur aufgerufen, die in einer
Schleife, die von 1 bis zu einem per
2
Performance-Optimierung: DAO versus SQL
http://www.helma-spona.de
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.
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.
Listing 1: Die Zeitmessung
Sub Zeitmessung(datAnf As Date, datEnde As
Date)
Listing 2: SQL-Anweisungen und
Abfragen ausführen
Dim datZeit As Date
Sub DatenAendernSQL()
datZeit = datEnde - datAnf
Const strSQL = "UPDATE test SET test.ID =
2, test.Zeichen = ""XYZ"""
Debug.Print "Zeit: " & Format(datZeit, "")
End Sub
Dim objDB As DAO.Database
Set objDB = Application.CurrentDb
Sub testTabellen()
objDB.Execute strSQL
Dim datAnfang As Date
Set objDB = Nothing
Dim lngAnz As Long
End Sub
lngAnz = 1000
'SQL
Sub DatenAendernAbfrage()
Debug.Print "SQL"
Dim objDB As DAO.Database
datAnfang = Now
Set objDB = Application.CurrentDb
For lngI = 1 To lngAnz
objDB.Execute "DatAendern"
TabelleErstellenSQL
Set objDB = Nothing
TabelleLoeschenSQL
End Sub
Next lngI
Zeitmessung datAnfang, Now
Sub DatenAendernDAO()
Dim objDB As DAO.Database
...
Dim objRS As DAO.Recordset
End Sub
Set objDB = Application.CurrentDb
Set objRS = objDB.OpenRecordset("test3")
SQL-ANWEISUNGEN UND
ABFRAGEN AUSFÜHREN
objRS.MoveFirst
Do While objRS.EOF = False
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
UPDATEAnweisung, 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-
objRS.Edit
objRS.Fields("ID") = 2
objRS.Fields("Zeichen") = "XYZ"
objRS.Fields("langerText") = _
"Text im Memofeld"
objRS.Update
objRS.MoveNext
Loop
3
Performance-Optimierung: DAO versus SQL
http://www.helma-spona.de
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.
FAZIT
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. <HS>
Rechtliche Rahmenbedingungen
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.
4