[GELÖST] Lazarus, MySQL und der Datendurchsatz

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Antworten
Michel
Beiträge: 35
Registriert: Sa 4. Dez 2021, 11:43
OS, Lazarus, FPC: Windows 10 / Ubuntu 20.04 LTS (L 2.0.12 FPC 3.2.0)
CPU-Target: 64Bit

[GELÖST] Lazarus, MySQL und der Datendurchsatz

Beitrag von Michel »

Ich habe ein Frage zum Datendurchsatz beim Zugriff von Lazarus auf eine MySQL-Datenbank.

Ok, klar ist mir, dass der Standort der Datenbank, im Internet, via VPN oder lokalem Netzwerk, sowie die Ausstattung des Servers eine erste wichtige Rolle spielt. Und natürlich ist auch das Volumen der Datenbewegungen von Bedeutung.

Wie schaut es mit Lazarus und den mitgelieferten Modulen für MySQL aus? Gibt es die Möglichkeit, bei der Stapelverarbeitung von einer Vielzahl an Daten den Datendurchsatz zu optimieren?

Hintergrund ist, dass ich Daten aus .csv-Dateien, die auch mal 6 MB groß sein können, aus einem Stringgrid in eine MySQL-Tabelle auf einem lokalen Server im Netzwerk importieren möchte. Leider bedürfen die in der .csv-Datei enthaltenen Daten einer Überarbeitung, so dass andere Möglichkeiten des Imports zur Zeit leider nicht in Frage kommen. Das Einlesen in den String-Grid ist ein Klick und ein paar Sekunden. Der Transfer in die Datenbank hingegen eine Katastrophe.

Nun, das läuft zwar via Lazarus, aber für mich elend langsam, egal ob ich den ApplyUpdates und Commit bei jedem einzelnen Datensatz mache oder erst am Ende der Aktion. Sofern ich das am Ende mache, gibt es keine Möglichkeit der Benutzerunterhaltung (ich habe mir einen Laufbalken erlaubt) und das Programm scheint zu hängen.

Wie sind hier die Erfahrungen? Ich würde schon gerne den Benutzer darüber informieren, wie viel schon erledigt ist und wie viel noch aussteht.

Nun, das Ziel ist es, diese "Rohdaten" via SQL auszuwerten und entsprechend komprimierte Daten zu erhalten. Wäre SQLLite hier sinnvoller?

Ja, mir ist schon klar, dass hier der Flaschenhals an einer anderen Stelle zu suchen ist. Mir reichen schon ein paar Ideen, damit ich mich auf die Suche machen kann.
Zuletzt geändert von Michel am Do 21. Apr 2022, 14:30, insgesamt 1-mal geändert.

TraumTaenzerDieter
Beiträge: 28
Registriert: So 14. Aug 2011, 09:11

Re: Lazarus, MySQL und der Datendurchsatz

Beitrag von TraumTaenzerDieter »

BulkInsert hilft hier gewaltig!

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Lazarus, MySQL und der Datendurchsatz

Beitrag von af0815 »

Je größer die Datenmenge umso näher muss man sich an die Tools des DB-Herstellers binden. Und da gibt es keine allgemeine Syntax.

Allgemein:
Die Daten in keine grafischen Komponenten packen. Das Refresh bei jeder Änderung in der GUI ist mit BeginUpdate und EndUpdate zu beherrschen bei kleineren Datenmengen, aber unnötig.
Die Daten in ein "Select Into" (wird meist als bulkinsert bezeichnet) packen, bis die maximale Übertragungsgröße erreicht ist. Dann alles mit einer Transaktion ohne grafischen Komponenten übertragen (oder Tools des Herstellers verwenden, siehe auch https://dev.mysql.com/doc/refman/8.0/en/load-data.html). Dabei wenn möglich keine Indizies verwenden, sondern diese später erst wieder aufbauen lassen (Das geht aber nicht immer).

Es gibt kein allgemeines Rezept für den Fall. Reine CSV Dateien lese ich auch direkt in den Speicher ein und werte die aus. Ist manchmal besser als das ganze einer Datenbank um zu hängen. Es hängt von Daten und Auswertungen ab.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Michel
Beiträge: 35
Registriert: Sa 4. Dez 2021, 11:43
OS, Lazarus, FPC: Windows 10 / Ubuntu 20.04 LTS (L 2.0.12 FPC 3.2.0)
CPU-Target: 64Bit

Re: Lazarus, MySQL und der Datendurchsatz

Beitrag von Michel »

Zunächst vielen Dank für die Hinweise.

Ich muss mich also nicht weiter wundern. Der Overhead bei einer Stapelverarbeitung einer MySQL-Datenbank ist demnach bei Lazarus bei der Nutzung der grafischen Komponenten wohl eben so gewaltig, dass es zu den genannten Auswirkungen kommt.

Ich hatte es mit einem "BeginUpdate" und "EndUpdate" im "Try finnally" Block bereits versucht. Das hat leider nichts an dem Problem geändert, dass der Import elend lang dauert. Zudem merkt man erst beim Versuch das Programm nach einer Zeitlang scheinbaren Nichtstun zu schließen, dass hier noch etwas läuft.

Für einen Bulk-Insert via 'LOAD DATA...' benötigt der verwendete MySQL-Benutzer mehr Rechte für die Datenbank als unter "USING" zusammengefasst. Aber selbst mit genügend Rechte für den Datenbank-Benutzer verhindert die Ausführung des MySQL-Servers --secure-file-priv option (Standard) die Ausführung.

Ich darf ja schon froh sein, dass ich vom Datenbanktreiber hilfreiche Fehlermeldungen, wie "The MySQL server is running with the --secure-file-priv option" erhalte.

Schade, ich wollte eigentlich nur ein kleine komfortable Oberfläche schaffe, damit ein Benutzer hier Daten für die Auswertung hochladen und auswerten kann. Mal schauen, wie ich hier weiter komme.

TraumTaenzerDieter
Beiträge: 28
Registriert: So 14. Aug 2011, 09:11

Re: Lazarus, MySQL und der Datendurchsatz

Beitrag von TraumTaenzerDieter »

Mit "BulkInsert" habe ich NICHT "Load Data" gemeint
sondern mehrere Inserts per Semikolen getrennt
in einem Execute ausführen: das hat bei einem meiner
Programme (ca. 10.000 Inserts) den Fakter 5 ergeben!

Michel
Beiträge: 35
Registriert: Sa 4. Dez 2021, 11:43
OS, Lazarus, FPC: Windows 10 / Ubuntu 20.04 LTS (L 2.0.12 FPC 3.2.0)
CPU-Target: 64Bit

Re: Lazarus, MySQL und der Datendurchsatz

Beitrag von Michel »

Ok, habe ich verstanden. Der Hinweis Bulkinsert hat mich aber noch andere Dinge testen lassen und ich habe hier tatsächlich den gewünschten Erfolg erzielt.

Die Klasse TSQLQuery hat hier ein interessantes Feature, die Verwendung von Parameter. Also z.B.:

TSQLQuery.SQL.Text := 'INSERT INTO TEST(id, monat, code, betrag') VALUES (0, :monat, :code, :betrag)';

Dann in der Schleife zum Auswerten der .CSV Datei z.B. wie folgt die oben "erstellten" Parameter mit Daten füllen.

Das Feld id ist AUTOINCREMENT, UNIQUE, PRIMARY und NOT NULL, daher schlicht den Integer 0 als "Platzhalter" verwenden. Die übrigen werden je nach Datentyp gefüllt, die Umsetzung mit korrekter Notierung je Datentyp macht TSQLQuery dann selber. z.B. folgende Befehle.

TSQLQuery.Params(ParamByName('monat').AsDateTime) := [Datum];
TSQLQuery.Params(ParamByName('code').AsString) := [String];
TSQLQuery.Params(ParamByName('betrag').AsFloat) := [Float];

Daten werden mit folgendem Befehl lokal zwischengespeichert:

TSQLQuery.ExecSQL;

Und nach Abschluss dieser Schleife ein

TSQLTransaction.Commit;

Und die Daten werden komplett in die Datenbank eingespielt. So sind 6 MB CSV-Rohdaten in etwa 2 Minuten in der Datenbank angekommen. Davor waren es knapp 30 Minuten.

Nun, der Hinweis mit dem Bulkinsert hat mich dankenswerterweise nochmals auf den Weg gebracht, hier einige Tests mit den Optionen der Komponenten, u.a TSQLQuery durchzuführen. Also, für mich ist das eine adäquate Lösung. :P

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: [GELÖST] Lazarus, MySQL und der Datendurchsatz

Beitrag von af0815 »

Es sollte noch schneller gehen, wenn man den Insert Into voll ausnutzt. Daher (gegen meine sonstige Überzeugung) die Daten gesammelt als String übergibt. Damit kann man die Daten noch stärker Bündeln als über einfache Parameter zu gehen. Gerade by MySql wird das gerne verwendet.

Edit: Vielleicht noch ein Hint über den ich gestolpert bin https://linuxhint.com/bulk-insert-mysql/
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Michel
Beiträge: 35
Registriert: Sa 4. Dez 2021, 11:43
OS, Lazarus, FPC: Windows 10 / Ubuntu 20.04 LTS (L 2.0.12 FPC 3.2.0)
CPU-Target: 64Bit

Re: [GELÖST] Lazarus, MySQL und der Datendurchsatz

Beitrag von Michel »

Jo, das ist sicher möglich. Aber meine Intention ist, hier einem Ottonormal-Benutzer das Aktualisieren von Daten zu ermöglichen. Neben möglichst hoher Geschwindigkeit braucht es auch einen Fortschrittsbalken als Benutzerunterhaltung, der sich sinnvoll füllt. Wer sitzt schon gerne vor dem Rechner und starrt auf ein Programm, ohne so recht wissen, läuft das noch oder hat sich das irgendwie aufgehängt. Und wenn es noch läuft, wie lange dauert es denn noch. Und dieser Kompromiss reicht mir hier.

charlytango
Beiträge: 843
Registriert: Sa 12. Sep 2015, 12:10
OS, Lazarus, FPC: Laz stable (2.2.6, 3.x)
CPU-Target: Win 32/64, Linux64
Wohnort: Wien

Re: [GELÖST] Lazarus, MySQL und der Datendurchsatz

Beitrag von charlytango »

Michel hat geschrieben:
Di 26. Apr 2022, 16:37
Neben möglichst hoher Geschwindigkeit braucht es auch einen Fortschrittsbalken als Benutzerunterhaltung, der sich sinnvoll füllt.
Also ich mache das so: Zeilen der CSV-Datei zählen (wirst du wohl in jedem Fall machen müssen)
Das ist dann der Maximalwert des Fortschrittsbalkens.
kleine Schleife die jeweils X Zeilen zu einem INSERT Statement zusammen baut

Code: Alles auswählen

INSERT INTO TEST
(monat, code, betrag) 
VALUES 
(1, '4711', 500;
 2, '4712',100;
......)
BTW: Autoincrement ID's haben für mich in einem Insert nichts verloren.

mit TSQLQuery.ExecSQL wegschicken.
Balken um X weiterschieben.

Code: Alles auswählen

Application.ProcessMessages
nicht vergessen.

zwei, drei Tests mit unterschiedlichen X um eine Balance zu finden.

Dann hast du beides: Tempo UND Benutzerinformation.
Den Aufwand sehe ich in der Fehlerbehandlung. Ich würde eine Art Import-Laufnummer mitspeichern um ggfs unvollständige Imports leicht identifizieren zu können oder eben mit Transaktionen arbeiten

just my 2cents

Antworten