Komisches SQL Sortier-Verhalten

[HCB]

Hallo, ich habe gerade was kurioses festgestellt. Mit dieser SQL Abfrage dauert das Ergebnis ca. 8 sek.:

Delphi-Quellcode:

select BestDocErfNr, BestDocDatum, BestDocLSNr, BestDocID
from BestDok
where BestDocType = 0 and BestDocLiefNr = 'K000100' order by BestDocDatum desc;

Das ist ja eine 08/15 Standard Query. Ja, ist indiziert.

Seltsamerweise läuft diese Abfrage ratz fatz:

Delphi-Quellcode:

select BestDocErfNr, BestDocDatum, BestDocLSNr, BestDocID
from BestDok
where BestDocType = 0 and BestDocLiefNr = 'K000100' 
group by BestDocDatum,BestDocErfNr,BestDocLSNr, BestDocID 
order by BestDocDatum desc ;

Jetzt frage ich mich natürlich woran das liegen könnte. Außerhalb Delphi mit einem externen Programm (MSA Query von MiTec) laufen beide Abfragen im ms Bereich.
Hat jemand eine Idee oder weiß, wo der Hund begraben liegt?

LG Harry

[fisipjm]

Hi

Ohne den Index Aufbau zu kennen, wird es wahrscheinlich schwierig eine Aussage zu treffen.
Ein Group By führt ja immer automatisch dazu, dass deine Datenmenge auch erst mal nach diesen Werten Sortiert wird.
Kann es sein, dass dein Index nicht über das eigentliche Sortierfeld geht, sondern nur über die anderen Felder im Group By und deshalb bei der 2. Abfrage deine Datenmenge die noch sortiert werden muss kleiner ist?

[HCB]

Das mit dem Index ist so eine Sache, die ich glaube noch nicht richtig geschnallt habe.

In der .mdb Access Datei ist u.a. die Tabelle BestDok. Da sind 7 Index für die Tabelle hinterlegt. Keiner davon hat das Feld BestDocDatum inkludiert.

Im Delphi Datenmodul liegt die FDQuery die die Abfrage ausführt. In dieser ist der IndexFieldName 'BestDocDatum' als Feld eingeschlossen.
Wenn hier von "Index auf..." die Rede ist, ist dann der Index der in Access angelegt ist oder der Index im FDQuery gemeint?

LG Harry

[Phoenix]

Der Index wird auf der Datenbank abgelegt, das hat nichts mit der Query zu tun.

Was mich wundert ist dass Access an sich - wenn Du extern darauf zugreifst - schnell ist.
Kann es sein, dass erst die komplette Datenmenge an den Client (also das Programm) übertragen wird, dann dort im Speicher sortiert und erst dann ausgefiltert wird?

[Delphi.Narium]

Genau das ist der Fall, wenn in IndexFieldName etwas steht, für das es auf der Datenbank keinen Index gibt. Dann muss der Client die Sortierung vornehmen und das geht bei wenigen Datensätze (group by) halt schneller als bei vielen Datensätzen.

Wobei mir ein Order By im SQL und eine zusätzliche Angabe von Spalten in IndexFieldName nicht wirklich sinnvoll erscheint. Zuerst sortiert die Datenbank entsprechend dem Order By und anschließend sortiert der Client das Ergebnis entsprechend IndexFieldName. Sprich: Man sortiert dann ein sortiertes Ergebnis. Und sind dann noch beide Sortierungen gleich, ist's einfach nur Resourcen- bzw. Zeitverschwendung.

Meiner Meinung nach sollte man die Sortierarbeit immer die Datenbank machen lassen, dafür ist sie da und das kann sie vermutlich deutlich besser als der Client.

IndexFieldName nutze ich nur bei 'ner Memorytable, wenn die nicht selbst die Möglichkeit der Indexerstellung mitliefert. Kann sie selbst 'nen Index (oder mehrere) anlegen, nutze ich immer IndexName.

[HCB]

Die Frage ist:
Muss ich denn überhaupt bei der FDQuery etwas bei Indexes außer IndexesActive=true eintragen, wenn doch schon in der Datenbank die Index angelegt sind?
Denn unter IndexName kann ich nichts auswählen. Müssten da nicht die Indices der Access-Tabelle stehen?

[Uwe Raabe]

Der IndexName bezieht sich auf die Indizes, die in der TFDQuery angelegt sind. In der Hilfe ist der Unterschied zwischen IndexFieldNames und IndexName beschrieben: Sortieren von Datensätzen (FireDAC)

[Delphi.Narium]

Bei 'nem Order by im SQL ist jede Angabe von irgendwelchen Indexspalten ... im Quelltext unsinnig. Order by heißt: Liebe Datenbank, bitte sortiere mir das Ergebnis so und so. Und das macht sie dann.

Jede weitere Indexangabe ist überflüssig, ggfls. aber auch kontraproduktiv.

Wüsste nicht, wieso IndexesActive=true für die Beschleunigung einer Datenbankabfrage angegeben werden soll / muss.

Beim Order By schaut die Datenbank nach, ob sie 'nen Index hat, der ihr die Sortierung vereinfacht, wenn ja, dann nutzt sie ihn, andernfalls sortiert sie ohne Index (was dann eben etwas bis viel länger dauert). Von außen lässt sich das nicht steuern.

IndexesActive dient der Nutzung innerhalb der Delphianwendung, siehe Hilfe dazu: FireDAC.Comp.DataSet.TFDDataSet.IndexesActive

Sehr flappsig formuliert: Mit IndexName und IndexFieldName kannst Du im Delphiprogramm das nachbilden, was eine Datenbank an Sortieroptionen im Order By sowieso schon mitbringt.

IndexName und IndexFieldName nutzt Du im Programm dann, wenn Du auf eine Sortierung durch die Datenbank (sprich: order by) verzichten möchtest oder im Programm die Daten anders sortieren willst, als sie von der Datenbank geliefert werden.

[Uwe Raabe]

Ergänzend dazu: Die Indizes der TFDQuery arbeiten nur auf den Daten im lokalen Puffer der TFDQuery. Insbesondere erfordern sie ein Lesen der gesamten Datenmenge, während ohne lokale Indizes das Paging für eine schnelle Reaktion nach Öffnen und Nachladen des nächsten Block Datensätze bei Bedarf erlaubt. Die eventuell vorhandenen Indizes der Datenbank sind der TFDQuery vollkommen egal.

[Blup]

Zitat von Delphi.Narium:

Bei 'nem Order by im SQL ist jede Angabe von irgendwelchen Indexspalten ... im Quelltext unsinnig. Order by heißt: Liebe Datenbank, bitte sortiere mir das Ergebnis so und so. Und das macht sie dann.

Jede weitere Indexangabe ist überflüssig, ggfls. aber auch kontraproduktiv.

In den meisten Fällen ist der vom Optimierer erstellte Indexplan ausreichend schnell.
Als Enwickler der Anwendung sollte man sich da raus halten.

Es gibt Ausnahmefälle, in denen man als Entwickler der Datenbank die Laufzeit der Abfrage selbst optimieren kann.
Z.B. wenn ein Index mit geringer Selectivität zusätzlich mit einbezogen wird, obwohl das Ergebnis durch einen anderen Index bereits hinreichend eingeschränkt ist.
Angenommen eine Abfrage über 2 Felder.
Index1 liefert für Feld1 3 übereinstimmende Datensätze
Index2 liefert für Feld2 500000 übereinstimmende Datensätze
Es ist schneller Feld2 in den 3 Datensätzen zu vergleichen, als die 3 Datensätze in den 500000 Datensätzen zu suchen.
Deshalb soll nur Index1 verwendet werden.
Für solche Anwendungsfälle würde ich die Daten in eine VIEW oder PROCEDURE bereitstellen und den Plan dort festlegen.
Alternativ könnte man aber auch einen Index anlegen der speziell für diesen Anwendungsfall beide Felder kombiniert.