Optimierung Datenbankzugriff Firebird

[IBExpert]

Zitat von tsteinmaurer:

Zitat:

Locate ist meistens ein blödes Verfahren, weil eben alle Records zum Client geliefert werden, bis derjenige kommt, den du haben wolltest

Um Mißverständnissen vorzubeugen. Das war zu Zeiten der BDE. IBObjects zum Beispiel ist da viel cleverer. IBDac eventuell auch. Ist mit der Trace API nun alles schön transparent, was da von den Zugriffskomponenten teilweise verbrochen wird. Aber das Thema Trace API wurde ja bereits erwähnt ...

hier mal ein Ausschnit aus der aktuelle Implementation TCustomSQLDataSet.LocateRecord
in XE4 unit Data.SqlExpr, das sieht mir nicht besonders clever aus.

Der rennt sogar zwei mal durch die Datenmenge, wenn beim ersten Durchlauf keine
Übereinstimmmung gefunden wurde. Und diese Implementation ist leider nicht unüblich,
auch wenn andere Komponenten das ggf irgendwie anders lösen, da sollte man sich aber
nicht drauf verlassen.

Code:

    First;
    while not EOF do
    begin
      if CheckValues(AFields, Values, CaseInsensitive, PartialLength) then
        break;
      Next;
    end;
    { if not found, reset cursor to starting position }
    bFound := not EOF;
    if not bFound then
    begin
      First;
      while not EOF do
      begin
        if CheckValues(SaveFields, StartValues, False, False) then
          break;
        Next;
      end;
    end;
    Result := bFound;

"Das war zu Zeiten der BDE" ist da also doch nicht ganz zutreffend ...

[Furtbichler]

Ich befürchte, Du wirst nicht darum herum kommen, dich zu entscheiden: Entweder hohe Ladezeiten oder einen etwas anderen Aufbau deines UX: Wieso soll der Anwender durch 300.000 Datensätze scrollen dürfen? Wieso muss er nach der (z.B.) Schuhgröße oder anderen, vollkommen unwichtigen, Eigenschaften sortieren können? Kann man sein Anliegen nicht anders lösen? Wir haben z.B. einmal ein ähnliches Problem dadurch gelöst, in dem der Anwender genau befragt wurde, wieso er denn darauf besteht, 100.000 Datensätze im Grid zu sehen: Dabei hat sich dann herausgestellt, das er diese Daten filtert und exportiert, um sie in EXCEL weiter zu verarbeiten. Und dazu hat er die Daten sortiert und bestimmte Bereiche rausgeschnippelt.

Also haben wir ihm kurzerhand ein Export-Tool geschrieben, was nun viel schneller geht und sind erstens unser Problem los (wie sortiert man eine Tabelle mit 50 Spalten und > 1Mio Zeilen performant in der GUI?) und zweitens sein Problem: Die Anwendung lief nur noch auf den aktuellsten PC mit schneller CPU und viel RAM. Unser Exporttool läuft überall.

Die UX haben wir dahingehend verändert, das er zur Darstellung seiner Daten immer einen Filter setzen muss, d.h. es werden nicht alle Daten angezeigt. Lässt er diesen leer, dann weiß er wenigstens, warum das jetzt so lange dauert.

Ich kenne FB nicht so gut, aber i.a. sind Sortieroperationen auf indexierten Spalten wesentlich schneller als auf Spalten ohne Index. Ist auch irgendwie logisch. Ich glaube auch, das FB den Index nicht verwendet, wenn dieser z.B. aufsteigend sortiert ist, man selbst aber absteigend sortieren möchte.

Du könntest allen Spalten deiner Tabelle nun einen Index spendieren, aber das wäre irgendwie bescheuert, denn nun hast Du bei der Datenänderung ein Performanceproblem, wenn es viele sind. Zudem kann man den Index nicht mehr so gut verwenden, wenn man nach mehreren Spalten sortieren will.

[QUOTE=IBExpert;1214261]

Zitat von tsteinmaurer:

hier mal ein Ausschnit aus der aktuelle Implementation TCustomSQLDataSet.LocateRecord in XE4 unit Data.SqlExpr, das sieht mir nicht besonders clever aus.

Der rennt sogar zwei mal durch die Datenmenge...

Also wenn ich das richtig sehe, sucht er ab der aktuellen Cursorposition bis zum Ende. Wie soll man das schneller machen (ok, Index berücksichtigen). Wenn nichts gefunden wurde, fängt er von vorne an. Von 'zwei mal durch die Datenmenge' kann man nun wirklich nur dann reden, wenn man sich ganz vorne befindet und der zu suchende Datensatz nicht vorhanden ist.

Allerdings ist hier ein wirklicher Lapsus: Beim zweiten Durchlauf sollte man aus der Schleife heraus, wenn man den ursprünglichen Record ein zweites Mal untersucht. Da man ab hier eh alles schon geprüft hat, kann man gleich ans Ende springen und die Schleife beenden.

Also: Locate funktioniert, wenn es etwas zu finden gibt und ist dann lahm (ok, und nicht wirklich clever), wenn es nichts zu finden gibt.

[Sir Rufo]

Zitat von Furtbichler:

Zitat von IBExpert:

hier mal ein Ausschnit aus der aktuelle Implementation TCustomSQLDataSet.LocateRecord in XE4 unit Data.SqlExpr, das sieht mir nicht besonders clever aus.

Der rennt sogar zwei mal durch die Datenmenge...

Also wenn ich das richtig sehe, sucht er ab der aktuellen Cursorposition bis zum Ende. Wie soll man das schneller machen (ok, Index berücksichtigen). Wenn nichts gefunden wurde, fängt er von vorne an. Von 'zwei mal durch die Datenmenge' kann man nun wirklich nur dann reden, wenn man sich ganz vorne befindet und der zu suchende Datensatz nicht vorhanden ist.

Öhm, du hast schon gesehen, dass das mit First startet (Position an den Anfang des DataSets setzen).
Da ist also nix von ab hier.

Und der zweite Suchlauf sucht einfach nur nach dem Datensatz, der aktiv war, bevor die Suche begann. Wenn dieser der letzte Datensatz im DataSet ist, dann wird die Datenmenge im WorstCase zweimal komplett durchsucht.

[IBExpert]

Zitat von Furtbichler:

Ich kenne FB nicht so gut, aber i.a. sind Sortieroperationen auf indexierten Spalten wesentlich schneller als auf Spalten ohne Index. Ist auch irgendwie logisch. Ich glaube auch, das FB den Index nicht verwendet, wenn dieser z.B. aufsteigend sortiert ist, man selbst aber absteigend sortieren möchte.

Wenn ein absteigender index existiert wird der auch benutzt, jedenfalls dann, wenn aus der eigenen Anwendung ein passendes SQL kommt und ide nicht versucht, das im Speicher zu sortieren. Der Index muss aber passend asc oder desc sein. Das kannst du beim Anlegen des Index selbst bestimmen und auf jeder Tabelle können pro Feld zum Beispiel ein aufsteigender und ein absteigender index erstellt werden.

Dann aber nicht beschweren, das das Schreiben extrem lahm wird. Bei 16k Pagesize gehen bis zu 818 single column indices pro Tabelle. Dann werden bei jedem Insert oder Update nicht nur die zugehörige Datenpage neu geschrieben, sondern auch noch mal bis zu 818 Indexpages.

Denk noch mal über die Anregungen von Furtbichler nach. Es ist ganz selten sinnvoll, in einem Grid hundertausende Records ungefiltert anzuzeigen. Aber bitte dann nicht mit den Filtereigenschaften arbeiten, die sind häufig genau so blöde implementiert wie locate.

Ohne kleine Datenmenge fliegt dir auch ab einer gewissen Datensatzanzahl die Funktionalität mit win32 schnell um die Ohren, dann startet dein Programm ab einer gewissen Datensatzanzahl gar nicht mehr, weil das Grid inkl Overhead bei so vielen Records auch schnell mal die 2GB Grenze sprengt.

Zitat von Furtbichler:

Wenn nichts gefunden wurde, fängt er von vorne an. Von 'zwei mal durch die Datenmenge' kann man nun wirklich nur dann reden, wenn man sich ganz vorne befindet und der zu suchende Datensatz nicht vorhanden ist.

Ganz vorne befindet man sch immer, weil ganz oben ein First steht

Aber wie du schon sagst, 2 komplette Durchläufe gibt es nur wenn es keine Übereinstimmung gibt und vorher der letzte aktiv war.
Dafür muss ich aber nicht alle Daten zum Client schicken, dafür gibt es eigenständige SQL Befehle.

Bei kleinen Datenmenge ist die Implementation in den Komponenten fast egal, aber 300000 Records sind schon mal per se keine kleine Datenmenge

[jobo]

Ich kann Furtbichlers Maßnahmen nur unterstreichen.
Wir bauen Systeme, die so arbeiten:
Verwendung z.B. ADO mit MAXRECORDS z.B. gleich 1000
Ableitung von TADOQuery/TADODataset wandelt Filter & Sort in Where-Bedingung/Order By um.
Mehrere Millionen Datensätze
Im Backend wird generell eine Dimension rausgefiltert, bleiben z.B. 200000 DS in großen Datenmengen
Öffnen/Sortieren von komplexen Datenmengen dauert leider noch mehrere Sekunden
Das ist natürlich nicht gewünscht, dafür gibt es spezialisierte Suchmasken mit einer handvoll Suchfeldern (indiziert)
Öffnen der großen Datenmengen über PK Filter aus Suchmaske.
Suchmasken und Ergebnisdarstellung liegen deutlich unter 0,5 Sekunden

Der Kunde kann unabhängig davon auch die großen Datenmengen auf jedem Feld filtern oder sortieren. Wenn er schlau ist, filtert er erst nährungsweise im Sekundenbereich auf eine kleine, relevante(!) Menge, dann sortiert er im Millisekundenbereich.

Auch ein Locate auf vorgefilteterten, kleinen Datenmengen läuft hinreichend schnell.

[Perlsau]

Zitat von IBExpert:

Moin, die deutschen Videos sind im IBExpertLive, auf Yourtube ist wirklich nur eins auf deutsch

Ja, das schau ich mir auf jeden Fall nochmal an. Wenn ich mich richtig an das letzte Mal erinnere, werden viele Funktionen erklärt, die in der Personalversion gesperrt sind. Aber egal, ich warte jetzt auf das neue Passwort, mein altes scheint nicht mehr gültig zu sein.

Zitat von Furtbichler:

Ich befürchte, Du wirst nicht darum herum kommen, dich zu entscheiden: Entweder hohe Ladezeiten oder einen etwas anderen Aufbau deines UX: Wieso soll der Anwender durch 300.000 Datensätze scrollen dürfen? Wieso muss er nach der (z.B.) Schuhgröße oder anderen, vollkommen unwichtigen, Eigenschaften sortieren können? Kann man sein Anliegen nicht anders lösen?

Ja, eine ähnliche Einsicht habe ich auch bereits gewonnen. Bei der riesigen Tabelle geht es um Daten aus der OpenGeoDB bzw. aus OpenStreetMaps: Weltweite Orte, Postalcodes, Länder, Längen- und Breitengrade usw. Ich habe gestern weiter herumprobiert und lasse jetzt via ComboBox-Auswahl nur noch Daten eines ausgewählten Landes anzeigen, wahlweise natürlich auch alle. Eine zweite ComboBox filtert nach Bundesländern vielleicht eine dritte noch nach Städten. Diese Filterung betrifft das View, die eigentliche Tabelle wird ja gar nicht angezeigt, sondern steht bei der Adresseingabe zur Verfügung, um dieselbe zu erleichtern. Findet der Anwender seine speziellen Adressbestandteile bei der Adresseingabe nicht, kann er die Daten in einem gesonderten Formular ergänzen.

Zitat von Furtbichler:

Wir haben z.B. einmal ein ähnliches Problem dadurch gelöst, in dem der Anwender genau befragt wurde, wieso er denn darauf besteht, 100.000 Datensätze im Grid zu sehen: Dabei hat sich dann herausgestellt, das er diese Daten filtert und exportiert, um sie in EXCEL weiter zu verarbeiten. Und dazu hat er die Daten sortiert und bestimmte Bereiche rausgeschnippelt. Also haben wir ihm kurzerhand ein Export-Tool geschrieben, was nun viel schneller geht und sind erstens unser Problem los (wie sortiert man eine Tabelle mit 50 Spalten und > 1Mio Zeilen performant in der GUI?) und zweitens sein Problem: Die Anwendung lief nur noch auf den aktuellsten PC mit schneller CPU und viel RAM. Unser Exporttool läuft überall. Die UX haben wir dahingehend verändert, das er zur Darstellung seiner Daten immer einen Filter setzen muss, d.h. es werden nicht alle Daten angezeigt. Lässt er diesen leer, dann weiß er wenigstens, warum das jetzt so lange dauert.

Das sehe ich inzwischen auch so: Es gibt eigentlich keinen Grund, eine Million Datensätze zum Scrollen bereitzustellen. Und Export der Geo-Daten ist auch nicht notwendig. Die einfach dazu verwendet, Adress-Eingaben zu verifizieren. So möchte ich z.B. die Möglichkeit bereitstellen, via integriertem Webbroser von Google-Maps anzeigen zu lassen, wo sich die eingegebene Location befindet.

Zitat von Furtbichler:

Ich kenne FB nicht so gut, aber i.a. sind Sortieroperationen auf indexierten Spalten wesentlich schneller als auf Spalten ohne Index. Ist auch irgendwie logisch. Ich glaube auch, das FB den Index nicht verwendet, wenn dieser z.B. aufsteigend sortiert ist, man selbst aber absteigend sortieren möchte.

Vor allem gibt es bei Views keinen Index! Das hab ich gestern herausgefunden. Es besteht keine Möglichkeit, Views zu indexieren, da es sich ja um virtuelle Tabellen handelt, die nicht physikalisch in der Datenbank existieren. Views stellen ja im Grunde ein Select-Resultat dar. Views verwende ich deshalb, weil z.B. die Tabelle Orte etliche Verknüpfungen zu Untertabellen aufweist wie z.B. zur Tabelle Land, und ich so auf Lookup-Felder in den entsprechenden Queries verzichten kann. Die Sortierung von Lookup-Feldern dauert seltsamerweise länger als die Sortierung des Views.

Zitat von Furtbichler:

Du könntest allen Spalten deiner Tabelle nun einen Index spendieren, aber das wäre irgendwie bescheuert, denn nun hast Du bei der Datenänderung ein Performanceproblem, wenn es viele sind. Zudem kann man den Index nicht mehr so gut verwenden, wenn man nach mehreren Spalten sortieren will.

Wie gesagt, Views kann man nicht indexieren. Ich geh jetzt nach dem Frühstück gleich mal dran und reduziere die Datenmenge so vernünftig, wie ich es vermag.

Achso, Frage: Was ist ein UX? User-Schnittstelle?

[jobo]

Zitat von Perlsau:

Wie gesagt, Views kann man nicht indexieren. Ich geh jetzt nach dem Frühstück gleich mal dran und reduziere die Datenmenge so vernünftig, wie ich es vermag.

Das spielt keine Rolle, die Datenbankphysik- Existenz und Art des Index bspw.- hat nichts mit dem Modell zu tun. Die Wirksamkeit eines Index sollte unabhängig vom Zugriff gegeben sein. Ich kenne es nicht anders.
Wirkt ein Index nicht, dann weil der Optimizer es nicht schnallt - oder der Entwickler-. Ein View ist nichts anderes als ein vorbereitetes Selectstatement.
Du kannst es ja ausprobieren und die Zugriffszeiten testen. Die sollten gleich sein.
(Ein View hat natürlich auch noch andere Zwecke, Interfaceschicht, Abstraktionsschicht, Berechtigungsschicht, ..), aber darum geht es hier ja gerade nicht.

[Perlsau]

Zitat von jobo:

Ich kann Furtbichlers Maßnahmen nur unterstreichen.

Ich auch!

Zitat von jobo:

Verwendung z.B. ADO mit MAXRECORDS z.B. gleich 1000

Das gibt's beim IBDac-Query nicht.

Zitat von jobo:

Ableitung von TADOQuery/TADODataset wandelt Filter & Sort in Where-Bedingung/Order By um.

Das versuche ich gerade, erhalte aber eine Fehlermeldung beim Versuch, den Filter zu setzen. Natürlich hab ich das bereits viele Male gemacht, aber noch nie mit den IBDac-Queries. Die Fehlermeldung lautet: Feld nicht gefunden. Dabei wird als Name des angeblich nicht gefundenen Feldes das Filterkriterium gemeldet:

Column unknown
BRASILIEN

Die Procedure, die aufgerufen wird:

Delphi-Quellcode:

procedure TDatMod.SetFilter_OrteLand(Land: String);
begin
  View_Orte.FilterSQL := 'V_LAND=' + Land;
end;

// Example aus IbDac.pdf:
// Query1.FilterSQL := 'Dept >= 20 and DName LIKE ''M%''';

Wobei das Property FilterSQL laut Dokumentation nichts anderes macht als:
Used to change the WHERE clause of SELECT statement and reopen a query.

Übrigens derselbe Fehler, der auch beim Setzen der gewöhnlichen Filtereigenschaft auftritt, wie ich es zuerst versucht hatte:

Delphi-Quellcode:

procedure TDatMod.SetFilter_OrteLand(Land: String);
begin
  View_Orte.Filtered := False;
  View_Orte.Filter := 'V_LAND=' + Land;
  View_Orte.Filtered := True;
end;

Schon irgendwie seltsam ... vielleicht 'n Bug ...

[mkinzler]

View_Orte.FilterSQL := 'V_LAND=' + QuotedStr(Land);

[Lemmy]

nein, das ist korrekt:

Zitat von Perlsau:

Delphi-Quellcode:

procedure TDatMod.SetFilter_OrteLand(Land: String);
begin
  View_Orte.FilterSQL := 'V_LAND=' + [B]QuotedStr(Land)[/B];
end;

du musst den Suchert in ' einschließen - ist ja auch ein String den Du suchst...

Grüße