Office: Geschwindigkeit der Abfrage

Hallo,
also ich verstehe meine kleine Acceswelt nicht mehr.
Ich habe das Feld Termine in einer Abfrage, über das Kriterium Datum() werden ca.200 Termine für den Tag abgefragt.

Jetzt habe ich ein weiteres Feld eingefügt, Dat: Format([Termine];"tt.mm.jj"), um die Terminwerte zu erhalten, die mit Uhrzeitangaben sind.

Das klappt auch alles, leider ist die Abfrageschwindigkeit 4x länger – zu lange...
Was kann man in so einem Fall tun?

Danke für die Hilfe
Jan

:)

 

Hallo,

ich habe das so verstanden, daß Du eine Abfrage laufen läßt.

Diese Abfrage wird Access-intern in SQL übersetzt.
Die Übersetzungszeit kannst Du einsparen, wenn Du die Abfrage
von vorn herein direkt als SQL Statement (VBA Code) ausführen läßt.

 

Hallo
Wo sind Deine Daten (in einem Backend ?)
Hatt die die Tabelle Indexe ?
mach einmal ein Index auf das Datum
Das es etwas länger dauer ist glabe ich normal er mus ja auch zuerst das Datum Formaieren.

 

IMHO wird bei der Verwendung des tatsächlichen Feldinhalts ein evtl. vorhandener Index verwendet.
Sobald dieses Feld mittels Funktion in ein anderes Format gebracht wird (Uhrzeit entfernt), kann dieser Index nicht mehr verwendet werden.

Testen könnte man mal, wie es aussieht, wenn Du als Kriterium für das unbehandelte Feld:
>=Datum() Und < Datum()+1
verwendest.

 

Mein Test ging über 15000 Datensätze. Die Zeit liegt bei ca. 1/2 sec, also nichts aufregendes. Ich habe dabei keinen Unterschied zwischen beiden Versionen festgestellt.

Das Problem liegt offensichtlich woanders und lässt sich wohl mit der Beschreibung so nicht beantworten.

Mike

 

\@jadatcoder:

Ist genau anders rum:
Eine gespeicherte Abfrage muss nicht mehr "übersetzt" werden.
Ein SQL-Statement hingegen muss erst geparst, dann ein Queryplan erstellt werden und dann kann Access bloß hoffen, dass dieser Plan auch optimal ist.
Zu jeder gespeicherten Abfrage wird der Queryplan bei jeder Ausführung auf Performance getestet, also eine Statistik erstellt, und ggfls. der Plan geändert. Das hängt dabei nicht nur von dem SQL-Statement selbst ab, sondern auch von den Daten; denn je nach Datenvolumen oder -art kann sich der Sinn von einem Index beispielsweise ins Gegenteil verkehren und die Query langsamer machen.

Ich habe Jan so verstanden, dass er das Kriterium auf das Datumsfeld anwendet und nur zur Anzeige ein zusätzliches formatiertes Feld ausgibt.
Dann kann es nicht an der Berechnung des Rowsets selbst liegen.
(Übrigens halte ich von Indizierung von Datumsfelder gar nichts. Macht mal einen Test bei größeren Tabellen. Bzw. (DistinctCount des Datumsindex)/RecordCount abfragen: alles unter 0.3 bringt IMHO nichts.)

Ich glaube eher, dass es sich da um ein VB-Problem handelt, dass also die Format-Funktion spinnt. Warum ist aber von hier aus nicht zu sagen.

Ciao, sascha

 

\@Sascha:

Könntest Du das näher erläutern? *confused.gif*

Schaden kann ein Index auf ein Datumsfeld (ausser bei vielen Updates des Datumsfeldes, nach denen der Index angepasst werden müsste) wohl bestimmt nicht, oder?

 

Also Ihr seit wirklich SPITZE!!!

Ich habe durch die vielen Vorschläge sehr viel gelernt, interessant was alles möglich sein kann.
Ich habe mein Problem mit dem Hinweis von Arne gelöst:

>=Datum() Und < Datum()+1

D.h. die Format-Funktion war nicht mehr nötig, mit dem obigen Kriterium wurden auch meine Datumswerte angezeigt, die noch einen Uhrzeitteil haben.

Nochmals DANKE für all die anderen Erfahrungen
Jan

 

\@Arne:

Das ist nicht philosophisch gefragt? *wink.gif*
Ich seh das so, dass jeder Index, der weder relational notwendig ist, noch in Abfragen oder Joins Vorteile bringt, auch überflüssig ist.
In Access kann er dann definitiv kontraproduktiv sein, weil der Netztraffic mit jeden Index anwächst und er außerdem Speicher benötigt.
Sonst könnte man ja grundsätzlich alle Felder indizieren? (Deshalb gibt es ja auch in der OLH zu den Indizes Ausführungen darüber, wann und unter welchen Umständen ein Index sinnvoll ist.)

DistinctCount ist eine schöne Funktion, mit der man die Effizienz von Indizes messen kann. Beispiel:
Code:

Wenn der Faktor ziemlich groß oder ziemlich klein ist, so bringt der Index nichts, denn im einen Fall muss Jet lange im Index suchen, im anderen muss es trotz Index in vielen Datensätzen suchen.

Soweit die Theorie .
Ich hatte das aber mal vor längerer Teit als Anlass genommen, die Wirksamkeit von Indexen in Abfragen (Kriterien, Joins) auf große Tabellen (>100000DS) zu untersuchen und zu messen. Das Ergebnis war ernüchternd: In Jet-Datenbanken gab es fast keine Performance-Unterschiede. Der Vorteil von Indizes ist rudimentär und lag im günstigsten Fall um die 15%. In einem langsamen Netz wird dieser kleine Vorteil in etwa wieder ausgeglichen durch den höheren Traffic, den der Index verursacht.

Für die Anbindung an SQL-Server gilt das aber ganz klar nicht! Dort kann die Indizierung die Geschwindigkeit um ein Vielfaches steigern.

Ciao, Sascha

 

Hallo zusammen
also nur noch etwas zu Indes, als was hier geschrieben worden ist, ist sehr Interesant, nur möchte ich darauf aufmerksamm machen das meine Frage vor dem Vorschlag mit den Indes auch war "Wo sind Deine Daten " und alles was hier für oder gegen einen Index ausgesagt wird ist doch schlussentlich davon abhänig und im weieren habe ich damit scho einmal eine verbeserung erzielt.

 

Hi Sascha,

immer wieder interessant, was Du so rausfindest bzw. schon rausgefunden hast. Ich wollte auch mal die Geschichte mit DistinctCount testen. Leider erhalte ich bei der Zeile

nRec = tdf.RecordCount

die Fehlermeldung: Laufzeitfehler 3420. "Das Objekt ist ungültig, oder es ist nicht mehr festgelegt". Verweis auf DAO musste ich wohl aktivieren, oder? Was mache ich sonst noch falsch?
[edit]hat sich erledigt. Unter A2k läuft es bei mir nur so: Code:

[/edit]


Ich habe dann mal einfach so getestet: Lokale Tabelle in einer MDB mit 500000 Datensätzen. Darin enthalten nur ein Datumsfeld, gefüllt mit Werten ab dem 01.10.2003 (Startdatum) bis zum 12.09.2004 05:19:00 (also ab Startdatum immer eine Minute weiter)

Das Datumsfeld war zunächst nicht indiziert.

Danach einmal eine Abfrage ausgeführt (sobald es möglich war, habe ich auf die Navigations-Schaltfläche "Gehe zum letzten Datensatz" geklickt und gewartet, bis der letzte Datensatz endlic da war).:

Code:

Die war auf jeden Fall schneller als diese Abfrage:
Code:

Nachdem ich das Datumsfeld indiziert habe - Indizierungsfaktor betrug 1 (Maximalwert), da die Daten fortlaufend immer um eine Minute erhöht wurden, ist das anscheinend auch logisch-, lief die 1. Abfrage noch einmal um einiges schneller. Die 2. Abfrage war immer noch ziemlich lahm (evtl. wohl genauso langsam). Dieses "Empfinden" habe ich bisher noch nicht gemessen (aber ich kenne jemanden, der dieses bestätigen konnte *wink.gif* ).

Mein Fazit bisher: Suche möglichst nur direkt in Feldwerten. Zumindest bei Datenbanken, die auf der eigenen Festplatte liegen, verwende Indizes.

Morgen mache ich evtl. mal einen etwas gründlicheren Test (mit Datenbank im Netzwerk).

 

Nä? In diesem Fall aber war es Quatsch !!
Ich wollte das auch nochmal verifizieren und hab folgenden Code verwendet:

Code:

Es sind 40000 DS in der Tabelle. (Lokal; "Echte" Daten aus einer Bestelldatenbank)
Wenn das Feld [vom] nicht indiziert war, so kamen 2400 ms raus.
Wenn das Feld [vom] indiziert war, so kamen 282 ms raus.
(Der Indizierungsfaktor war 0,16.)

Mit Index also 8 mal schneller!

Also kann man meine Aussagen zur Indizierung von Datumsfelder in den Wind schießen!

Nun werde ich mal ganz schnell meine DBs umstellen! Im Netz dürfte der Effekt zwar nicht ganz so stark ausfallen, aber der Gewinn ist sicher trotzdem erheblich.

Ich frage mich bloß, wie ich das seinerzeit gemessen hatte? Es war ein ähnlicher Test gewesen und ich hatte mich einen ganzen Nachmittag damit beschäftigt. Krass! Ergebnis war, dass ich ab da wesentlich sparsamer indiziert habe. Das muss ich nun überdenken und noch ein paar Tests mehr mit Netz und Mehrfachzugriff durchführen.

Auf was einen das Forum nicht so stößt...! *angel

Gruß, Sascha

 

Hallo ihr,

kleine Anmerkung: bei diesen Tests muss man aber auch immer bedenken, dass diverse Caches die Daten puffern, so dass nachfolgende Versuche erheblich schneller ablaufen könnten.

Die Ergebnisse würden mich aber auch interessieren. *Smilie

 

Hallo,

@Sascha

Danke für den Hinweis. Man(n) lernt ja nie aus ;-)

Ich war bislang immer der Meinung, dass direktes SQL
schneller ist als eine 'fertige' Abfrage.
(ist mir mal so vermittelt worden)


Ist es dann ratsam, in meinen Anwendungen
alle SQL Statements durch Abfragen zu ersetzen und diese
dann durch DoCmd.OpenQuery aufrufen zu lassen ?
Drüber werde ich mir dann mal Gedanken machen...

 

Hi Henner,

ich antworte mal für Sascha: zu den Grundlagen s. noch einmal Arnos (A.S.) und Manuelas Ausführungen und in diesem Thread.