Office: Geschwindigkeit der Abfrage

\@jadatcoder:

Nein, es lohnt sich nicht, alle SQL-Quellen auch in Abfragen zu speichern. Aus zwei Gründen:
- Bei einfachen Abfragen der Art
SELECT Feld1, Feld2, Feld3 FROM Tabelle ORDER BY Feld1
gibt es für Jet nicht viel am Queryplan zu rechnen. Der Ablaufplan ist schnell ermittelt und besteht in nur einem einzigen Schritt. (Bzw. zwei, falls Feld1 indiziert. Zu Queryplan siehe http://www.microsoft.com/accessdev/articles/v35perf.htm , letzter Absatz zu JETSHOWPLAN .) Solche Abfragen kann man als SQL-String stehen lassen.
Komplexere Abfragen, vor allem solche, die aus mehreren Tabellen bestehen, sollten gespeichert werden.
- Ich habe bisher unterschlagen, dass unbemerkt SQL-Strings als Datenquellen für z.B. Kombos automatisch auch gespeichert werden. Das macht Access, indem es temporäre Abfragen erstellt, die man in der Systemtabelle MSYSQueries finden kann; die haben den Namen ~sqlxyz. Ob diese dann die selbe Effizienz haben und statistisch upgedatet werden, wie andere gespeicherte Abfragen, weiß ich nicht. Ich würde mich aber nicht darauf verlassen.
Was aber auch jeden Fall nicht gespeichert wird sind Abfragen, die aus VBA-Code heraus ausgeführt werden, also Recordsets auf SQL-Statements. Da sollte man eher gespeicherte Abfragen nehmen und sie per Querydef.OpenRecordset öffnen.


@Mario:
Das mit dem Cache ist klar. Deshalb will ich das auch noch mal genauer testen. Wichtig ist, dass die Tabellen sehr groß sind, so dass nur ein kleiner Teil im Cache Platz findet, und dass die Engine immer wieder neu "initialisiert" wird. Im Netzbetrieb mit eingeschaltetem Locking verhält sich die Engine zudem anders. Ich werde auch mal per DBEngine.SetOption ein paar Parameter verändern (Cache-Größe etc.)

Ciao, Sascha

 

\@Sascha: Was bin ich froh, dass ich noch einmal nachgefragt habe (s. meinen Beitrag vom 21.10.2003 um 17:38 Uhr). Ich bin mal gespannt, was Deine Tests mit Änderungen der diversen Einstellungen bringen.

@all: ohne viel über die Netzeinstellungen,-Geschwindigkeit und die Netzlast während des Testes sagen zu können, kommen hier mal meine "Forschungsergebnisse".

Ausgegangen bin ich von der beschriebenen Tabelle mit 500000 Datensätzen (s. meinen Beitrag vom 21.10.2003 um 21:17 Uhr). Der Index (falls angelegt) steht übrigens auf "Ja (Duplikate zulassen)" - obwohl ich den wohl auch auf "Ja (ohne Duplikate)" stellen könnte.

Saschas Sub namens "TestPerformance" habe ich etwas geändert, damit ich gleich die gespeicherten Abfragen verwenden kann (ausserdem habe ich eine Function daraus gemacht, weil ich ursprünglich etwas anderes damit vorhatte, aber das ist jetzt nebensächlich):

Code:

Aufruf erfolgte dann im Direktfenster mit
?TestPerformance("qryDirektInFeld")
bzw.
?TestPerformance("qryMitFormat")

Inhalt der Abfragen:
qryDirektInFeld
Code:

qryMitFormat
Code:

Die Funktion wurde dann auch noch mehrfach aufgerufen (jeweils 5mal - mit der geänderten For-Next-Schleife wurde also jede Abfrage 25 mal ausgeführt).

Nun endlich die Ergebnisse (in Klammern die "Rankings"), dabei war das Frontend immer lokal. Aufgeführt sind die Durchschnittswerte der Messungen:

<TABLE border="1"><TBODY><TR><TD colspan="3" align="center"><B>Ergebnisse ohne Index</B></TD></TR><TR><TD>Abfrage</TD><TD>Backend lokal</TD><TD>Backend im Netz</TD></TR><TR><TD>qryDirektInFeld</TD><TD align="right">9712 (3)</TD><TD align="right">11680 (4)</TD></TR><TR><TD>qryMitFormat</TD><TD align="right">52461 (5)</TD><TD align="right">53929 (7)</TD></TR><TR><TD colspan="3"><HR></TD></TR><TR><TD colspan="3" align="center"><B>Ergebnisse mit Index</B></TD></TR><TR><TD>Abfrage</TD><TD>Backend lokal</TD><TD>Backend im Netz</TD></TR><TR><TD>qryDirektInFeld</TD><TD align="right">150 (1)</TD><TD align="right">436 (2)</TD></TR><TR><TD>qryMitFormat</TD><TD align="right">52598 (6)</TD><TD align="right">53953 (8)</TD></TR></TBODY></TABLE>

Meine Interpretation dieser Ergebnisse (deckt sich ziemlich mit meinem Fazit in meinem Beitrag vom 21.10.2003 um 21:17 Uhr):
- Indizes scheinen sich doch positiv auf die Performance auszuwirken, wenn sie bei einer Suche genutzt werden können (s. Platz 1 und 2).
- Man sollte möglichst in Feldwerten und nicht formatierten Ausdrücken der Feldwerte suchen (Platz 3 und 4 im Vergleich zu 5 und 7 bzw. 1 und 2 im Vergleich zu 6 und 8).
- Ausdrücke verhindern die Verwendung der Indizes (s. 5 und 6 bzw. 7 und 8).
- Der Zugriff auf ein Backend im Netz ist natürlich langsamer als auf ein lokal gespeichertes (welch Wunder *wink.gif*)

 

Prima!

Um das (meinerseits) abzuschließen:

Ich habe jetzt ebenfalls nochmal ausführliche Tests gemacht, so ziemlich mit den gleichen Konfigurationen wie Arne. (Tabelle mit 260TSD "richtigen" Datensätzen)
Die Ergebnisse sind witzigerweise auch exakt dieselben wie bei Arne.
Die mit Datums-Index versehenen Abfragen waren also ca. 50-mal schneller!

Die Cachegröße und das Locking spielten eine untergeordnete Rolle und ändert die Performance im Bereich von max. 5-10%.

Daneben konnte ich feststellen, dass dieselbe Abfrage in gespeicherter Version (Recordset per QueryDef.OpenRecordset geöffnet) nochmal um 10-15% schneller war, als die direkte Ausführung des SQL-Strings. (Was erstaunlich ist, weil der Queryplan nur aus einem einzigen Schritt bestand.)

Ciao, Sascha

 

\@Arne und Sascha

Danke für Eure Tipps.
Es ist immer wieder prima zu erfahren, in welcher kompetenten
Weise man hier im Forum unterstützt wird.

 

\@Arne und Sascha:
Ich kann mich Henners Meinung nur anschließen *top
Dieses Thema ist höchst interessant und gut zu wissen, was man so alles
beachten sollte.

Grüsse
heini

 

Ich habe versucht den Test mit ADO zu machen. (ich habe keine erfahrung mit ADO)
Da liest man doch immer das ADO schneller ist, doch es scheint das ich mache etwas falsch. Kann mir jemand helfen? Wo mache ich den Fehler?

Meine Test-Routinen:
Code:

Die Abragen:
Mit Index
Code:

 

\@Ruedi:

Was soll da falsch sein?
Es ist ein Märchen, dass ADO schneller als DAO sei.
Wie soll es auch? Es verwendet ja gleichfalls die Jet-Engine. Nur der Umgang mit den Resultsets von Jet ist anders programmiert. Es gibt einige wenige Fälle, wo es schneller ist. (Siehe auch den Thread: http://www.ms-office-forum.net/forum...hlight=ADO+DAO)
Wenn ADO soviel schneller wäre, dann würde es auch öfters in Access verwendet werden. Ich sehe es nur im Umgang mit externen Datenquellen und in Verbindung mit SQL-Servern im Vorteil.

Übrigens wäre es auch günstiger für deinen Test, wenn du nicht die ActiveProject.Connection nehmen würdest, sondern eine neue ADO.Connection zu einer anderen Datenbank aufbauen würdest, die die Tabellen enthält. Da kämen dann wohl weniger Daten aus dem Cache.

Ciao, Sascha

 

Hallo
@Sascha
Vielen Dank für deine Ausfürungen.
Ich habe mein test eigendlich unterbrochen da ich dachte das ich etwas FALSCH mache, die Ide war schon das ich das von einer aderen MDB her hole.
ich denke ich werde wider weiter machen wenn ich zeit habe.
Nochmals vielen Dank