[gelöst] DBGrid bei Firebird Datenbank Abfragen bei Zeilenanzahl > 100.000 sehr langsam

[Helios]

Hallo zusammen,

vorab erstmal eine Entschuldigung, dass ich keinen Sourcecode zu diesem Thema hier zu Beginn ablege
(ich habe die Hoffnung, dass jemand das Problem kennt und vielleicht nur eine Einstellung in den Komponenten Optionen das Problem ist).
Nun zum Problem (Windows 10, Lazarus 2.0.12, Firebird 3.0.7):
Ich lese XML-Dateien mit Messwerten zwischen 25.000 und 100.000 in eine Firebird Datenbank (3.0 Embedded) in eine Tabelle MESSWERTE ein (alles über Lazarus SQLdb Standardkomponenten kein ZEOS oder so). Der Import einer XML-Messdatendatei funktioniert auch soweit ohne Probleme innerhalb von ca. 10 Sekunden (damit kann ich leben).
Wenn ich nun auf die Messdaten über eine TDBGrid Komponente zugreife sind die Zugriffe bis ca. 10.000 Messwerte (z.B. mit "SELECT * FROM messwerte rows 10000") noch recht performant (ca. 3 Sekunden). Ab ca. 50.000 Zeilen wird die Sache zäh (und das liegt leider im Standard Abfrage Umfang der oben genannten XML-Dateien) und dauert länger als der eigentliche Insert der Daten in die Datenbank.
Wichtig dabei, die erste Anzeige der Daten im DBGrid nach Ausführung des Selects ist schön schnell. Erst wenn man z.B. über den vertikalen Scrollbalken an das Ende der ausgewählten Datenmenge springen will, dauert der "Refresh" der angezeigten Daten sehr lange. Ich habe schon etwas recherchiert und z.B. bei ZEOS Komponenten etwas wie FetchRows gefunden oder bei SQLdb SQLQuery1.Disable/Enablecontrols. Hat aber leider nicht geholfen. Ein TStringGrid hat mit der Anzeige so vieler Daten weniger Probleme (selbst wenn die Header-RowSortier-Funktion angeschaltet ist). Ich vermute das "Fetching" der Daten von der DB ist zu klein eingestellt.
Kennt jemand das Problem und hat da evtl. eine Lösung für mich?
Vielleich noch als Hinweis: Die Anzeige von 100.000 Zeilen (mit "normaler" Befüllung) sind im Datenbankumfeld eigentlich nicht mehr soo riesig. Ab mehreren (aber einstelligen) Millionen sehe ich das erst kritisch;).

Danke für das Interesse an diesem Thema und Gruß
Helios

PS: Ein Beispiel reiche ich nach, wenn gewünscht (kann aber dauern, da ich dann irgendwie auch die Embedded Datenbank und Testdaten zusammenstellen und vernünftig "Packen" muss)

[af0815]

Grundlegend mal die Frage ob du entsprechende Sortierungen und Schlüssel in der Datenbank vorgesehen hast. Es muss zumindest den Primärschlüssel geben.

Es ist so, das normalerweise nur die Teile der DB gefeched werden , die aktuell vom Grid benötigt werden. Dazu muss die DB-Logik aber über einen Key angeben, was gerade gebraucht wird. Das Fetching funktioniert also nur dann gut, wenn zumindest ein PK gefunden wird, die Einstellungend dazu sind schon passend. Hast du das nicht, kann sogar das direkte lesen und einfüllen in ein normales Stringgrid schneller sein.

Noch dazu mal die Frage, wird was geändert oder nur angezeigt. Wenn nur anzeige, dann schon mal die Query als ReadOnly deklarieren.

Bei wirklichen Datenbanken (> 10 mio Zeilen) wird es schon interessant sich die Logik genau zu überlegen, besonders dann wenn man auch mit Joins arbeiten muss. Da lasse ich bei mir im DB-Studio die Ausführungspläne anzeigen und designe dann auf das Optimum der DB hin. In Lazarus kommt einfach nur eine einfache Abfrage, notfalls auch auch eine Stored Procedure.

[six1]

Schalte beim DBGrid die Datasource vor dem Einlesen auf NIL und nach dem Einlesen wieder auf die entsprechende Datasource.

Code: Alles auswählen

DbGrid1.Datasource:=NIL;
....einlesen
DbGrid1.Datasource:=Datasource1;

[charlytango]

Schalte beim DBGrid die Datasource vor dem Einlesen auf NIL und nach dem Einlesen wieder auf die entsprechende Datasource.

Möglicherweise auch mal mit einem DisableControls arbeiten

https://lazarus-ccr.sourceforge.io/docs ... trols.html

MyDataset.DisableControls; Try // Do ... trols end;

Daten dort verarbeiten wo sie liegen -- also in der DB -- mit Stored Procedures, Views etc.
Und ganz genau überlegen wieviel oder was in Lazarus überhaupt geholt oder angezeigt werden soll. Bei großen Datenmengen dauert das Fetchen der Daten meist länger als die Abfrage selbst.

Bei wirklichen Datenbanken (> 10 mio Zeilen) wird es schon interessant sich die Logik genau zu überlegen, besonders dann wenn man auch mit Joins arbeiten muss. Da lasse ich bei mir im DB-Studio die Ausführungspläne anzeigen und designe dann auf das Optimum der DB hin. In Lazarus kommt einfach nur eine einfache Abfrage, notfalls auch auch eine Stored Procedure.

Kann da af0815 nur recht geben -- je weniger gefetcht wird desto performanter. Und Tabellen ohne PrimaryKey gehen eigentlich gar nicht.
Sorgfältig gewählte Indizes helfen dabei ebenso wie die Analyse des Lösungsweges der Datenbank, denn nicht jedes SQL-Statement wird auch über vorhandene Indizes optimiert -- da gibt es sprachliche Feinheiten und Unterschiede was die Datenbank in welcher Reihenfolge macht.
Ich weiß nicht wie deine Messwerttabelle aussieht und was du davon anzeigen und verarbeiten willst/musst. SELECT * wende ich (aus Prinzip und eigener schlechter Erfahrung) nichtmal in solchen Situationen an in denen es möglich wäre. Nur die Felder angeben die du UNBEDINGT brauchst -- auch das reduziert schon die zu fetchende Datenmenge.

[af0815]

Wenn ich 'Select * from' sehe, steige ich aus. Das geht maximal im Primärschulbereich beim basteln und ist wie ein Lakmus Test . charlytango hat da genau die richtige Umschreibung gewählt.

Leider kenn ich Firebird zu wenig tiefgehend, es ist zulange her, das ich damit und mit Interbase gearbeitet habe.

Grundlegend stelle ich noch in den Raum, brauche ich eine DB oder bin ich bei den heutigen Rechnern nicht tw. besser dran, das ganze gleich diskret im Hauptspeicher zu machen. 1 GB Daten machen mir da kein Kopfweh und es kommt auf die Verarbeitung an.

BTW: Ich habe zwischen 24GB und 36GB Speicher am Rechner/Laptop, weil ich damit arbeite und nicht herumspiele.

[Helios]

Danke für eure Rückmeldungen.
readonly hatte ich schon gesetzt. Enable- und DisableControls hatte ich auch schon probiert aber ohne Erfolg.
Den Vorschlag mit

Code: Alles auswählen

DbGrid1.Datasource:=NIL;
....einlesen
DbGrid1.Datasource:=Datasource1;

von six1 finde ich interessant. Nur ist das Problem, dass der Teil bei dem das lange Fetchen der Daten startet - also beim
vertikalen Scrollen - ohne mein zutun startet und endet. Ich habe da leider nicht die Möglichkeit im Code mich
"dazwischenzuschieben" (das gilt auch für die Enable- und DisableControls).
Die Tabelle hat (noch) keinen Primary Key aber mehrere Indizes (non unique) gesetzt.
Die Geschwindigkeit der Anzeige der Daten sollte, meiner Meinung (und Erfahrung mit z.B. Oracle) nach,
nicht vom Primary Key abhängen, da ich ja keinen Join über andere Tabellen durchführe bzw
nicht mit irgendeiner Bedingung, suche. Somit müsste mir die Datenbank die Zeilen einfach nacheinander auslesen und dann im DBGrid anzeigen.
Ich habe immer noch den Verdacht, dass da eine "FetchRows" (oder so) Option auf einen zu kleinen
Wert gesetzt ist und daher das Nachladen der Daten so lange dauert.
Der Inhalt einer XML Datei sollte zumindest in einem DBGrid performant angezeigt werden können
(also die 25.000-100.000 Zeilen) ansonsten muss ich eine andere Technologie/Programmiersprache wählen (Java/JDBC
oder C/C++ mit QT oder FLTK). Das wäre aber Schade, da die Datenanbindung mit Lazarus sonst wirklich schön umgesetzt ist.
Ich kann mir auch nicht vorstellen, dass das DBGrid von Lazarus "von Hause aus" so lahm ist. Da gibt es bestimmt noch einen
Trick.
@af0815: "SELECT * FROM messwerte" ist wirklich nicht sehr schlau bei Zeilen > 10 Mio.
Mit "SELECT * FROM messwerte WHERE ROWNUM < 100000" (Oracle) oder ""SELECT * FROM messwerte ROWS 100000"
sollte ich aber in der Sekundarstufe II sein:-)
Von den XML-Dateien möchte ich später gerne bis zu 10.000 Stück in der lokalen Datenbank verarbeiten können (als Minimum habe ich mir 1000 gesetzt).
Auch 128GB RAM reichen da dann nicht mehr. Daher der Ansatz über eine frei Verfügbare Firebird Datenbank und Lazarus.

Gibt es sonst noch andere Vorschläge?

Danke erneut und Gruß

Helios

[af0815]

Ja, einen hätte ich noch.

Kannst du eine Tabellendefinition mal hier reinstellen. Weil damit hat man die Möglichkeit das ganze an einem neutralen Testprogramm mal nach zu stellen. Weil grundlegend sollte dein Problem ja auf allen DB Systemen sichtbar sein, wenn es wirklich nur mit dem Grid zusammenhängt.


Übrigens, wenn man nur ROWNUM oder ähnliches anhängt, so ändert das nichts und ist auch nicht schlauer. Das hat nichts mit der richtigen Benennung der Columnen zu tun.

[Helios]

Hallo af0815,

ich baue ein anständiges Beispiel zusammen und stelle das hier rein. Dauert aber etwas.
Das mit dem ROWS/ROWNUM nutze ich nur gerne bei großen Tabellen um nur testweise eine bestimmte Anzahl Daten zu bekommen ohne eine Full Table Scan zu provozieren.
Das hat bisher immer sehr gut und performant geklappt egal wie der Tabellenaufbau und die Größe der Tabelle war.

Danke für Deine Hilfe... ich haue in die Tasten für in schönes Beispiel...

Gruß
Helios

[charlytango]

Full Table Scan

sorry. kann mich nicht zügeln. Das ist ein Reizwort.

Erklärung: Hab mir meine SQL-Sporen (vor vielen jahren) in einem Projekt einer großen Versicherung verdient. Und dort den SQL-Admin bei der Entwicklung und beim Testen mit solchen Aktionen des öfteren an den Rand eines Nervenzusammenbruchs gebracht weil der SQL-Server ausgelastet war und auch schon mal weggebrochen ist bzw nur gekillt und neu gestartet werden konnte.
Das macht besonders Laune wenns mal den Produktionsserver erwischt

Eigentlich tut man doch alles um eben einen Full Table Scan (zumal bei großen Datenmengen) zu vermeiden. Schon klar, deswegen auch ROWS/ROWNUM - trotzdem.

Mir ist immer noch nicht klar was der Sinn hinter mehreren 100tsd Datensätzen in einem Grid sein soll?
Soll sich das jemand ansehen ?

Bin gespannt auf dein Beispiel

[Helios]

Hallo charlytango,
bitte das Wort davor "ohne" beachten, also "ohne einen Full Table Scan":-).
Wie gesagt rownum/rows nur für Testzwecke. Klappt auf einer Oracle DB auch gut wenn dort 10hoch9 Datensätze drin sind und ohne den DBA auf die Palme zu bringen:-)

Testbeispiel ist in Arbeit...

Gruß

Helios

[Helios]

Hallo zusammen,

anbei das versprochene Beispielprogramm.

DBGridFireBirdTest.zip

(129.07 KiB) 88-mal heruntergeladen

.
Anleitung zur installation:

• Firebird Datenbank installieren bzw. passend ablegen
  Firebird ZIP (keine Installation notwendig) von https://github.com/FirebirdSQL/firebird ... -1_x64.zip herunterladen und unter C:\Firebird entpacken. Die DLL fbclient.dll muss unter C:\Firebird\fbclient.dll zum liegen kommen, damit der Pfad zur SQLDBLibraryLoder1(.LibraryName) Einstellung passt).
  Ich verwende Windows10 64Bit. Für Windows 32Bit (oder Linux) bitte auf der Firebird Seite (https://firebirdsql.org) nach dem richtigen installationspaket suchen.

• Beispieldateien in einem leeren/neuen Verzeichnis entpacken und compilieren (ich hoffe ich habe keine weiteren Abhängigkeiten vergessen).
  Es wird eine Datenbankdatei "TestDB.FDB" erstellt und jeweils wieder gelöscht (siehe Zeile 209 in "Unit1.pas").

Programm starten und die Laufzeit zwischen 50000 und 100000 Einträgen anschauen.
Wichtig: Im unteren Grid nach dem Anlegen der Daten bis ganz nach unten Scrollen. Die Zeit die dabei vergeht ist mein Problem.
Dummerweise ist das die Datenmenge die dem Benutzer i.d.R. komplett zur Ansicht gebracht werden muss.
Für jeden Hinweis zur Beschleunigung dieses zeitkritischen Teils wäre ich sehr dankbar.
Gruß
Helios

[wp_xyz]

Warum verwendest du denn so ellenlange Felder (Länge 1000 und 8000), wenn die enthaltenen Strings nur sehr sehr viel kürzer sind? Ich habe probehalber alle Felder mit 30 Zeichen erzeugt (bis auf LangText, das ich auf 100 gesetzt habe), und damit kann ich das DBGrid ganz flüssig scrollen.

[Helios]

Hallo wp_xyz,
die Feldergröße habe ich mir nicht ausgedacht. Diese stammen tatsächlich aus einem produktiven System (mit ca. 80 Milliarden Rows mit einer Oracle DB). Die Daten werden aber über einen Join über mehrere Tabellen zusammengeführt. Zur Ansicht für den Nutzer kommen Sie dann so wie in der Tabelle Messwerte angegeben sind. Das entspricht dann mit 25-100 tausend rows der Auslesung aus einem Fahrzeug. Das System ist schon fast 20 Jahre alt und die Anzeige erfolgte über Java/JDBC. Da wurde jetzt die Anzeige langsam (ca. 30 Sekunden) wenn ca. 200 tausend Werte über mehrere Fahrzeugauslesungen abgefragt werden.
Ich hatte bisher so gute Erfahrungen mit der Performance von Lazarus, dass ich gedacht habe
das lässt sich mit Pascal bestimmt besser und schneller umsetzen (zumindest die GUI) als beim Interpretierten Bytecode vor vielen Jahren.
Hast Du eine Erklärung warum in meinem Beispiel die Abfrage der 50.000 Werte etwa 3 Sekunden dauert, aber die 100.000 dann ca. 20 Sekunden? Spielen da Auslagerungsprozesse eine Rolle oder ist das evtl. ein Datenbankthema? Ich schaue mir das in den nächsten Tagen mit Java an. Mal schauen ob in Verbindung mit Firebird die Performance ähnlich wie mit Lazarus ist. Dann hat sich die Sache geklärt.
Danke für Deine Rückmeldung (und auch für die vielen Beispielcodes, da ist einiges
zur Wiederverwendung, auch hier, gekommen) und Gruß
Helios

[Soner]

Ich finde es läuft normal, wenn ich gleich zum letzten Datensatz scrolle dauert es nicht mal 2 Sekunden. Der Ruckler, der beim Seitenblättern auftaucht, hat nichts mit TDBGrid zu tun.
SQLDb holt Datensätze "Seitenweise" nicht alle auf einmal. Versuch mal am Anfang alle auf einmal zu holen. Ich habe den Eigenschaftsnamen nicht im Kopf weil ich SQLDB nicht verwende. Es könnte TSQLQuery.RowsAffected sein.

Nachtrag:
Ich habe jetzt nach SQLQuery1.Open; zusätztlich SQLQuery1.Last; hinzugefügt, um alle Datensätze vom Server zu holen, voilà es gibt gar keine Verzögerung mehr bei der Anzeige.

[wp_xyz]

Richtig, wenn ich auf die 64-bit IDE wechsle (normalerweise verwende ich 32-bit) kann ich Soners Beobachtung bestätigen. (Das 32-bit Programm stürzt mit einem Heap-Overflow ab).

Ein Tipp: Aktiviere beim Entwickeln immer den Debugger ("Projekt" > "Projekteinstellungen" > "Debuggen" > "Generate Info for the debugger" und "Zeilennummern in Laufzeit-Backtraces anzeugen" markieren) - dein Beispielprogramm hast du ohne Debuggerunterstützung gemacht.