Sql-Anfrage mit WHERE langsamer als ohne trotz Index?

[gmc616]

Moin Delphianer,

ich hätte da mal einige Verständnisfragen.

Ich greife über ADS auf Clipper-dBase-Dateien zu. In meinem Fall eine adress.dbf, in der ca. 50.000 Adressen bespeichert sind. Zu dieser adress.dbf existiert eine Indexdatei adress.cdx!

Nun öffne ich die adress.dbf mit dem SQL-Befehl SELECT * FROM adress und zeige das Ergebnis in einem DBGrid an. Das Ergebnis ist in 0,nix da.

Als nächtes frage ich mit SELECT * FROM adress WHERE name <'S%' an. Das Ergebnis läst allerdings ca. 10 sec auf sich warten.

Über AdsQuery.IndexDefs.Items[i].FieldExpression habe ich festgestellt, dass unter anderem auch ein Index über 'name' existiert. Das zeigt doch auch, das die Indices geladen werden.

Nun sollte man doch meinen, dass die Anfrage über die Where-Clausel mindestens genauso schnell ist als ohne, jedenfalls keine 10 sec dauert?

Werden die Indices überhaupt benutzt? Kann man überprüfen ob die Indices auch wirklich greifen?

Meine Erfahrungen mit anderen DBs haben mich gelehrt, dass (in den meisten Fällen); je mehr man das Result mittels WHERE-Clauseln einschränkt, desto schnell kommt das Ergebnis zurück.
Das stimmt doch? (für die meisten Fälle)

Ähnliche Sorgen habe ich auch mit AdsQuery.Locate.
Frage ich die DB mit ersterem SQL-Befehl an und möchte nur zu einem Namen springen der z.b. mit 'W' beginnt, dauert es wieder ca. 10 sec bis der Datensatz gefunden wurde.

Werden auch beim Locate die Indices benutz? Wieso dauert das so lange?
Was macht das Locate genau? Durchsucht es den Speicher oder die DB?

Selbst wenn ich vor dem Locate-Aufruf den AdsQuery.IndexNamen auf meinen Index über 'Name' lege, ändert sich nichts an der Performance.

Das das Anzeigen von 50.000 Adressen in einem Fenster nicht grade sinnvoll ist weiß ich selbst, allerdings ist mein Chef davon nicht weg zubekommen.

Bin ich hoffnungslos verloren oder gibt es doch noch Möglichkeiten um die Sache zu beschleunigen?

Gruß
gmc

[edit=r_kerber]Einige Delphi- durch SQL-Tags ersetzt. Mfg, r_kerber[/edit]

[r_kerber]

Zitat von gmc616:

Als nächtes frage ich mit SELECT * FROM adress WHERE name <'S%' an. Das Ergebnis läst allerdings ca. 10 sec auf sich warten.

Eine Wildcard-Suche dauert IMHO immer relativ lange, vor allem dann, wenn kein "echter" Datenbank-Server zur Anwendung kommt, der mit optimierten Indizes arbeitet. Teste mal, wie lange es dauert, exakt nach einem Namen zu suchen.

[gmc616]

Die exacte Suche nach einem Namen (z.B. WHERE name ='gmc') ist erwartungsgemäß schnell.

Nur hilft mir das nicht weiter.
Das Fenster soll als Recherche dienen. Alle Adressen sollen angezeigt werden und dann soll Buchstabe für Buchstabe zur Adresse gehüpft werden.

so in der art:

Delphi-Quellcode:

procedure TFormSuche.Edit1KeyUp(Sender: TObject; var Key: Word;  Shift: TShiftState);
begin
  AdsQuery.Locate('name',Edit1.Text ,[loCaseInsensitive, loPartialKey]);

Und das dauert bei 50.000 Adressen sehr lange. Obwohl das Laden der Adressen ins Grid ziemlich fix ging.

*verzweifel*
gmc

[alzaimar]

Wenn der SQL-Parser und Interpreter auch nur ansatzweise die Prämisse erfüllen will, halbwegs brauchbar zu sein, sollte er denn doch erkennen, das der Ausdruck "name < 'S%'" mit einem Index wesentlich schneller verarbeitet werden kann, weil der Ausdruck einem "name < 'T'" entspricht. Mein Interpreter jedenfalls kann das.

Aber, ich denke mal, das dBase-Teil (ADS?) ist einfach zu einfach gestrickt, um das zu erkennen. Ich würde mich mal mit FireBird oder der MSDE auseinandersetzen. Ich bevorzuge die MSDE aus Gewohnheit, habe aber über FireBird nur Gutes gehört.

Abhilfe: Wenn das Laden denn schnell geht, dann filtere doch selbst. Es gibt reichlich Klassen für reguläre Ausdrücke oder Wildcard Patternmatching. Dann schreibst Du einen kleinen OnFilter-Event für dein Dataset und fertig. Das sollte doch schnell genug gehen.

[Union]

Zitat von gmc616:

Moin Delphianer,

ich hätte da mal einige Verständnisfragen.

Ich greife über ADS auf Clipper-dBase-Dateien zu. In meinem Fall eine adress.dbf, in der ca. 50.000 Adressen bespeichert sind. Zu dieser adress.dbf existiert eine Indexdatei adress.cdx!

Nun öffne ich die adress.dbf mit dem SQL-Befehl SELECT * FROM adress und zeige das Ergebnis in einem DBGrid an. Das Ergebnis ist in 0,nix da.

Als nächtes frage ich mit SELECT * FROM adress WHERE name <'S%' an. Das Ergebnis läst allerdings ca. 10 sec auf sich warten.

Über AdsQuery.IndexDefs.Items[i].FieldExpression habe ich festgestellt, dass unter anderem auch ein Index über 'name' existiert. Das zeigt doch auch, das die Indices geladen werden.

Nun sollte man doch meinen, dass die Anfrage über die Where-Clausel mindestens genauso schnell ist als ohne, jedenfalls keine 10 sec dauert?

Werden die Indices überhaupt benutzt? Kann man überprüfen ob die Indices auch wirklich greifen?

Meine Erfahrungen mit anderen DBs haben mich gelehrt, dass (in den meisten Fällen); je mehr man das Result mittels WHERE-Clauseln einschränkt, desto schnell kommt das Ergebnis zurück.
Das stimmt doch? (für die meisten Fälle)

Ähnliche Sorgen habe ich auch mit AdsQuery.Locate.
Frage ich die DB mit ersterem SQL-Befehl an und möchte nur zu einem Namen springen der z.b. mit 'W' beginnt, dauert es wieder ca. 10 sec bis der Datensatz gefunden wurde.

Werden auch beim Locate die Indices benutz? Wieso dauert das so lange?
Was macht das Locate genau? Durchsucht es den Speicher oder die DB?

Selbst wenn ich vor dem Locate-Aufruf den AdsQuery.IndexNamen auf meinen Index über 'Name' lege, ändert sich nichts an der Performance.

Das das Anzeigen von 50.000 Adressen in einem Fenster nicht grade sinnvoll ist weiß ich selbst, allerdings ist mein Chef davon nicht weg zubekommen.

Bin ich hoffnungslos verloren oder gibt es doch noch Möglichkeiten um die Sache zu beschleunigen?

Gruß
gmc

[edit=r_kerber]Einige Delphi- durch SQL-Tags ersetzt. Mfg, r_kerber[/edit]

Hi,

Du erzählst uns wahrscheinlich nicht wirklich alles. Ich habe hier eine Adressentabelle im DBF Format mit knapp 600.000 Einträgen. Und hier sind meine Ergebnisse:

Select * from adress where name < 'S%' -> zwischen 47ms und 120ms
Select count(*) from adress where name < '%S' -> zwischen 46ms und 98ms (418000 Treffer)

Hast Du vielleicht die Datenbankverbindung geschlossen? Der allererste Aufruf nach dem Öffnen kann tatsächlich lange dauern. Aber wenn eine Tabelle einmal gecached wurde, geht das blitzschnell. Und noch weitere Fragen:

• Welche ADS-Version verwendest Du
• Wie greifst Du auf die Tabelle zu (Direkt oder AdsConnection)
• Wie sind die Indexausdrücke genau aufgebaut
• Verwendest Du ADSSettings, und wenn ja welche
• Befindet sich eine grosse Anzahl von Deleted-Records in der Tabelle

[gmc616]

Zitat von Union:

Du erzählst uns wahrscheinlich nicht wirklich alles. Ich habe hier eine Adressentabelle im DBF Format mit knapp 600.000 Einträgen. Und hier sind meine Ergebnisse:

Select * from adress where name < 'S%' -> zwischen 47ms und 120ms
Select count(*) from adress where name < '%S' -> zwischen 46ms und 98ms (418000 Treffer)

Das sind ja Traumzeiten

Zitat von Union:

Hast Du vielleicht die Datenbankverbindung geschlossen?

AdsQuery.Close? Natürlich, bei jedem Schließen meines SucheFensters.

Zitat von Union:

Der allererste Aufruf nach dem Öffnen kann tatsächlich lange dauern. Aber wenn eine Tabelle einmal gecached wurde, geht das blitzschnell.

Stimmt! Das bringt mich auf eine Idee!

Zitat von Union:

• Welche ADS-Version verwendest Du

• TDataSet Descendant for Delphi/C++Builder 7.0
  

  Zitat von Union:

• Wie greifst Du auf die Tabelle zu (Direkt oder AdsConnection)

[Union]

Zitat von gmc616:

Zitat von Union:

Hast Du vielleicht die Datenbankverbindung geschlossen?

AdsQuery.Close? Natürlich, bei jedem Schließen meines SucheFensters.

Ich meinte eigentlich die AdsConnection

Zitat von gmc616:

aus AdsQuery.IndexDefs.Items[i].FieldExpression erfahre ich nur die einzelnen Indices zB. 'name' oder 'plz;ort'. Anders komme ich z.Z.nicht rann, da mir die Tools zu den Clipper-DBs fehlen.

Zitat von Union:

[*]Verwendest Du ADSSettings, und wenn ja welche

die Standarteinstellungen, außer ShowDeleted := false

Zitat von Union:

[*]Befindet sich eine grosse Anzahl von Deleted-Records in der Tabelle[/list]

Unteranderem mit sicherheit ja

Dann liegt da unter anderem das Geschwindigkeitsproblem. XBase-Tabellen haben ein Deleted-Flag. Wenn Du gelöschte Sätze nicht anzeigen läßt, so müssen die immer gefiltert werden. Das dauert lange. Du solltest Die Tabelle mal reorganisieren mit Packtable.

Zitat von gmc616:

ich hab jetzt mit

Delphi-Quellcode:

AdsQuery.SetKey;
AdsQuery.FieldByName('name').AsString := 'g';
AdsQuery.GotoNearest;

rum experimentiert, aber so richtig funktioniert das auch nicht.
Mal findet "er" einen Datensatzen, einen anderen wieder nicht ...

Ist ja auch klar, wenn Du mit Kleinbuchstaben arbeitest. Du solltest zum Suchen ein Feld aufnehmen wie
UCase(Name) as Suchname und dann nach diesem Feld mit AnsiUppercase suchen.

Für die direkte Bearbeitung der Tabellen und Db gibt es das kostenlose Tool ARC, das kannst Du Dir von ExtendedSystem runterladen.