Probleme mit fpspreadsheet bzw. spready bei größeren Datenmengen

[Helios]

Hallo Forenmitglieder,

bei der Suche nach einer Library, bei der ich Daten aus einem StringGrid in eine Excel Datei übertragen kann, bin ich auf das sehr schöne fpspreadsheet bzw. spready gestoßen.
Leider stoße ich mit meinen "Big Data" Anforderungen verhältnismäßig schnell mit dem spready an die Leistungs und Speichergrenzen.
Bei Dateien größer 500.000 Zeilen (Excel 365 kann etwas über 1Mo Zeilen verarbeiten (1.048.576)) verbraucht spready etwa 3x so viel RAM wie Excel und
Load/Save Operationen werden sehr langsam (ca. 3x langsamer als Excel) und Copy/Paste Operationen zeitweise gar nicht mehr richtig ausgeführt.
Ist das ein Bug oder Design bedingt?
Bitte nicht falsch verstehen, im Excel sind wahrscheinlich einige 100 Mannjahre an Entwicklung vergraben, das kann fpspreadsheet/spready sicher nicht alles nachbilden aber mich würde interessieren, wo hier der Bottleneck ist.
Gibt es eine Erklärung für den großen Speicherverbrauch von spready (vielleicht ein größerer Zeichensatz, kann man einen anderen Zeichensatz irgendwo einstellen?). In Excel lassen sich selbst bei über 1Mio Zeilen sogar noch die Filter und Sortierfunktionen verwenden.
So etwas hätte ich natürlich auch gerne (ähnlich performant wie im Excel) im fpspreadsheet/spready. Ich hatte einige Hinweise gesehen, das man im spready sortieren und filtern (auf komplette Spalten?) kann. Wäre das umsetzbar?

Gerne würde ich mich zu diesem Thema mit euch oder dem Maintainer von fpspreadsheet/spready austauschen.

Vielen Dank und Gruß

Helios

Im Anhang ein kleines Programm zur Erzeugung der Testdaten (CSV) mit dem SortGrid aus dem Online-Package-Manager von Lazarus.
fpspreadsheet/spready habe ich aus dem aktuellen CCR Trunc.

SortGrid2.zip

(127.4 KiB) 62-mal heruntergeladen

[wp_xyz]

Der Maintainer von fpspreadsheet bin zur Zeit ich, nachdem meine Vor"macher" abgesprungen (Felipe) oder verstorben (Rainier) sind. Ich denke in ihrem Sinn zu sprechen, wenn ich sage, dass fpspreadsheet nie für "Big Data" gedacht war und kaum Anstrengungen zur Optimierung des Speicherbedarfs und der Geschwindigkeit unternommen wurden. Und ich will das auch nicht ändern, denn, ehrlich gesagt, wenn jemand Excel für "Big Data" verwendet, dann hat er das falsche Tool, und mit fpspreadsheet erst recht (vor kurzem habe ich gelesen, dass durch die Verwendung von Excel Covid-Meldungen verloren gegangen sind (https://www.bbc.com/news/technology-54423988)).

FPSpreadsheet ist gedacht für kleine bis mittlere Tabellen, z.B. mit Messwerten, die vom eigenen Programm eingelesen und weiterverarbeitet werden sollen. Oder für den Export eigener Rechnungen und Auswertungen nach Excel, genauso wie man halt nach CSV exportiert, nur mit zusätzlichen Formatierungsmöglichkeiten. Dadurch dass ich die visuellen Komponenten weiterentwickelt habe, ist vielleicht der Eindruck entstanden, fpspreadsheet wäre ein vereinfachtes Konkurrenz-Produkt zu Excel/Calc, was natürlich nie der Fall war. Aber es war einfach verlockend zu versuchen ein Grid zu entwickeln, das automatisch die Formatierungen der Excel/Calc-Datei darstellen oder die enthaltenen Formeln selbst berechnen kann. Natürlich ist hier die Frage: Wo ist die Grenze? Muss FPSpreadsheet jedes exotische (aber kaum verwendete) Format kennen oder jede beliebige (aber kaum verwendete) Formel unterstützen? Ich meine nicht, und ich meine auch: Big Data ist auf jeden weit jenseits der Grenze.

In einem Übergangsbereich kann man durch Optimierung den Nutzungsbereich etwas ausdehnen:
- Den Speicherbedarf kann man reduzieren, indem man auf das WorksheetGrid verzichtet, denn jede Zelle des Grid braucht selbst wieder seinen eigenen Speicher. Und man sollte in dem Fall mit 64 Bit arbeiten, bei 32-Bit stößt man schon unter 100.000 Zeilen ans Speicherlimit.
- Die Geschwindigkeit kann man erhöhen, indem man nicht mit FileStreams arbeitet, aber das ist eigentlich sowieso per Default ausgeschaltet.

----

Ich habe mir mit deinem Programm eine Testdatei mit 500.000 Zeilen erzeugt, und die wird von spready in 20 s geöffnet, ok - von Excel in 6, aber selbst LibreOffice Calc braucht 16 s - so schlecht ist das in diesem Zusammenhang dann auch wieder nicht...

[Winni]

Und ich will das auch nicht ändern, denn, ehrlich gesagt, wenn jemand Excel für "Big Data" verwendet, dann hat er das falsche Tool, und mit fpspreadsheet erst recht (vor kurzem habe ich gelesen, dass durch die Verwendung von Excel Covid-Meldungen verloren gegangen sind (https://www.bbc.com/news/technology-54423988)).

Hi!

Zu den von Excel gefälschten Daten gehören aber zwei Sorten von Deppen:

* Die M$-Programmierer, die automatisch das, was in ein simpelsten Muster passt, zu einem Datum konvertieren.
* Die Wissenschaftler, die erstens M$ benutzen. Mit LibreOffice wär das nicht passiert. Und wenn sie schon M$ benutzen: Dann muss man alle automatischen Konvertierungen abstellen.

Hintergrund: Die M$-Autokonvertierung nimmt an, dass alles, was mit "Apr" anfängt, ein Datum im April ist. Gleiches für "Jan", "Feb", etc.

Winni

[Helios]

Hallo wp_xyz und winni,
Danke für eure Antworten.
Ich finde es gut wie FPSpreadsheet entstanden und wie es von der Usability ausgelegt ist
und es ist in Pascal Freier Software vorhanden.
Exotische Formate vermisse ich nicht. Mein Problem ist einfach der 3fach höhere (RAM) Speicherbedarf und die Filter/Sortier/Ladezeiten im Vergleich zum Excel.

Für mich fängt Big Data (ohne Hochkamma:-) erst jenseits der aktuellen Excel Kapazitäten an (ich hab das mal so für mich definiert).

65536 Zeilen waren vorgestern (Excel bis 2007) - ehemals typische Messdatengröße
1048576 Zeilen waren gestern (Excel ab 2007) - derzeit typische Messdatengröße
1048576*100(?) Zeilen hier fängt für mich Big Data an und dann muss ich eine Datenbank nehmen und da bin ich mit euch.

Wenn "kaum Anstrengungen zur Optimierung des Speicherbedarfs und der Geschwindigkeit unternommen wurden", steht doch die Chance gar nicht schlecht mit entsprechenden Maßnahmen/Ideen aus der Community hier mit FPSpreadsheet näher an Excel an die Leistungsdaten heranzurücken (oder es gar besser zumachen)? Das wäre doch der Weg zu einer vielfach anwendbaren Superkomponente.
Oder gibt es die schon in Freier Software in Pascal? Dann stubst mich bitte mit der Nase drauf.

Aber nochmal zu meiner eingangs gestellten Frage: Welcher Characterset wird unter
FPSpreadsheet verwendet? Kann man den ggf. ändern? Ist das evtl schon das Problem?
Gibt es die Möglichkeit die Daten "In-Memory" gepackt zu halten und so das Laufzeitverhalten positiv zu beeinflussen bzw. ist dieser Weg schon beschritten worden?
64 Bit verwende ich bereits unter Win10 und Debian11, da kann ich nicht mehr viel herausholen.

Vielen Dank und Gruß aus Flensburg

Helios

PS: Gerne würde ich bei der Lösungsfindung unterstützen da ich immer wieder bei meinen Projekten mit so einem Thema oder "artverwandt" konfrontiert werde und ich möchte es mit Free Pascal/Lazarus und möglichst besser als mit Excel oder Java lösen:-)

[Winni]

Aber nochmal zu meiner eingangs gestellten Frage: Welcher Characterset wird unter
FPSpreadsheet verwendet? Kann man den ggf. ändern? Ist das evtl schon das Problem?
Helios

Hallo!

Was meinst Du mit "Characterset"? Alle Komponenten benutzen seit einigen Jahren UTF8.
Bei UTF8 benutzt alles zwischen #0 und #127 ein byte. wie andere Codepages.

Willst Du jetzt bei äöüÄÖÜß jeweils ein byte sparen??
Oder bei den mathematischen Zeichen ⟃⟆⟐⟒⟓⟦⟧⟬⟭ jeweils 2 byte??

Das wird ja wohl nicht den erwünschten Erfolg haben: zu mickrig.
Mal abgesehen von dem Aufwand, alles nach CP 12xx umzustellen.

Winni

PS.: Dafür kannst Du mit UTF8 solch "tolle" Icons benutzen. Kostet jeweils 4 byte.

[wp_xyz]

fpspreadsheet speichert Strings als UTF8, alle andere Daten binär.

Jede (verwendete) Zelle wird in fpspreadsheet mit einem Zeiger auf einen TCell-Record belegt (unit fpsTypes):

Code: Alles auswählen

type
    TCell = record
    Row: Cardinal; // zero-based
    Col: Cardinal; // zero-based
    Worksheet: TsBasicWorksheet;   // Must be cast to TsWorksheet when used  (avoids circular unit reference)
    Flags: TsCellFlags;
    FormatIndex: Integer;
    ConditionalFormatIndex: array of Integer;
    
    // Cell content 
    UTF8StringValue: String;   // Strings cannot be part of a variant record
    RichTextParams: TsRichTextParams; // Formatting of individual text ranges
    case ContentType: TCellContentType of  // variant part must be at the end
      cctEmpty      : ();      // has no data at all
      cctNumber     : (Numbervalue: Double);
      cctUTF8String : ();      // UTF8StringValue is outside the variant record
      cctDateTime   : (DateTimeValue: TDateTime);
      cctBool       : (BoolValue: boolean);
      cctError      : (ErrorValue: TsErrorValue);
  end; 

Dazu können noch ausgelagerte Records für weniger oft benutzte Daten wie Kommentare, Hyperlinks, Bilder, Formeln.

Früher waren noch die kompletten Formatierungsinformationen drinnen, das ist jetzt in eine dem Workbook gehörenden Liste ausgelagert, so dass man nur noch den Format-Index braucht. Vielleicht kann man durch Umstrukturieren noch etwas gewinnen, aber ein Faktor 3 wird das sicherlich nicht. Und das Problem dabei ist natürlich, dass so massive Änderungen mit höchster Wahrscheinlichkeit Regressionen im Benutzercode bewirken.

Die Dateien werden standardmäßig zuerst in einen MemoryStream eingelesen, und von dort aus wird dann allmählich das Workbook aufgebaut. Das bedeutet, dass zum Ende des Ladevorgangs hin, die komplette Datei noch im Memorystream steckt und gleichzeitig fast vollständig im Workbook - hoher Speicherverbrauch beim Lesen. Du kannst vor dem Laden die Workbook-Option boBufStream setzen, damit wird die Datei häppchenweise eingelesen und ausgewertet - geringerer Speicherverbrauch beim Lesen, aber höherer Verwaltungsaufwand (und ein gewisses Risiko, wenn ein "Häppchen" zu Ende geht und nachgeladen werden muss - ist zwar gut getestet, aber 100% Sicherheit gibt's nicht).

Wenn du die Daten nicht permanent in einem Workbook vorhalten willst, kannst du auch den "virtual mode" aktivieren (Workbook Option boVirtualMode); hier wird Zelle für Zelle gelesen, der Inhalt zum Verarbeiten angeboten und dann gleich wieder verworfen. -- geringster Speicherverbrauch, minimalster Benutzer-Komfort, kein Berechnen von Formeln (weil es die Zellen, auf die die Formel verweist, ja gar nicht mehr gibt).

Beim Suchen gibt es keinen Index, da läuft man einfach von Zelle zu Zelle. Bei "normalen" Spreadsheets ist das ausreichend, und bei größeren bin ich eh der Überzeugung dass man da mit einer Datenbank besser fahren würde.

[Helios]

Hallo Winni,
ich hatte im April das Thema hier viewtopic.php?f=17&t=13600 und da war letztendlich der Characterset UTF8 der Schlüssel zum Problem. Nach dem Wechsel auf ISO8859_1 datenbankseitig (enthält auch die deutschen Umlaute) waren die Speicher und Laufzeitprobleme für mich (genügend) gelöst. Bei dem Fall im April, hatte ich es aber mit dem DBGrid und einer Abfrage aus einer Firebird Datenbank zu tun.
Hübsche Icons im StringGrid brauche ich nicht, dafür mehr Performance beim Laden/Speichern/Sortieren/Filtern der Strings/Texte .
Wenn da noch was gehen würde wäre das klasse!
Danke und Gruß
Helios

[wp_xyz]

ich hatte im April das Thema hier viewtopic.php?f=17&t=13600 und da war letztendlich der Characterset UTF8 der Schlüssel zum Problem. Nach dem Wechsel auf ISO8859_1 datenbankseitig (enthält auch die deutschen Umlaute) waren die Speicher und Laufzeitprobleme für mich (genügend) gelöst.

Das trifft hier mit hoher Wahrscheinlichkeit nicht zu. Die xlsx-Dateien sind ein gezippter Satz von mehreren xml-Dateien, und dort steht alles schon als UTF8, so dass nichts mehr hinundher konvertiert werden muss. Und auch wenn ich die von deinem Programm erzeugte CSV-Datei mit 500.000 Zeilen nach UTF8 konvertiere und dann in Spready lade, so dauert das 19 sek statt 20 bei der Original-ANSI-Kodierung - geschenkt.

[Winni]

Moin!

Nur mal ein Schuß ins blaue:

Es ist äußerst selten, dass in Spreadsheets strings mit einer Länge > 255 gebraucht werden.

Wenn Du nun den ganzen Salat auf ShortStrings umstellst: {$H-}

Hintergrund: Shortstrings haben einen Overhead von einem byte.
AnsiStrings haben einen Overhead von 8 bytes .
Spart bei jedem String 7 byte. Da bist Du bei Deinen oben erwähnten Dimensionen recht schnell im GigaByte-Bereich.

Winni

[af0815]

Wenn ich mir den ersten Post ansehe, brauchst du genau genommen kein fpspreadsheet, sondern nur einen Konverter von Stringgrid nach Excel.

Vielleicht kann das einer der Writer von fpspreadsheet alleine leichter erledigen, als das in das fpspreadsheet zwischen zu puffern.

[Socke]

Es ist äußerst selten, dass in Spreadsheets strings mit einer Länge > 255 gebraucht werden.

Da kenne ich ganz ander "Spreadsheets". Zur Optimierung kannst du dann direkt auf Nullterminierte Zeichenketten gehen. Damit hast du zwar immer noch einen Overhead von 4/8 Byte, musst den ganzen String aber nicht immer durch die Gegen kopieren.

[wp_xyz]

Es ist äußerst selten, dass in Spreadsheets strings mit einer Länge > 255 gebraucht werden.

Wenn Du nun den ganzen Salat auf ShortStrings umstellst: {$H-}

Hintergrund: Shortstrings haben einen Overhead von einem byte.
AnsiStrings haben einen Overhead von 8 bytes .
Spart bei jedem String 7 byte. Da bist Du bei Deinen oben erwähnten Dimensionen recht schnell im GigaByte-Bereich.

Jetzt bin ich verwirrt. ShortStrings das waren doch die, bei denen man die vorreservierte Stringlänge in eckigen Klammern angegeben hat und wenn nichts angegeben war, dann wurden 255 Byte reserviert. Das heißt, jeder String belegt automatisch 255 byte + 1 Längenbyte. Um 7 Bytes im Overhead einzusparen, werden also bei einem 1-Zeichen-String 254 unnötige Bytes reserviert. Das ist eine Verschlimmbesserung.

[wp_xyz]

Wenn ich mir den ersten Post ansehe, brauchst du genau genommen kein fpspreadsheet, sondern nur einen Konverter von Stringgrid nach Excel.

Die gesamte Anforderung ist zwar etwas unklar (es ist auch von Sortieren, Suchen, Lesen die Rede), aber wenn wir uns auf das Konvertieren eines großen StringGrids in ein Spreadsheet-Format beschränken, dann kann der beigefügte Code helfen. Hier wird der "virtual mode" von fpspreadsheet aktiviert. Das bedeutet, man gibt zunächst an, wieviele Zeilen und Spalten das zu erzeugende Worksheet haben soll. Beim Schreiben durchläuft das Worksheet all seine Zellen in diesen Bereich (die aber nicht existieren, sondern nur per Zeilen/Spalten-Nummer angegeben sind), und das User-Programm gibt in einem Handler zum OnWriteCell-Event zurück, was für jede dieser Zellen in die Datei geschrieben werden soll. Eine eigentliche Worksheet-Datenstruktur wird nicht aufgebaut, jede geschriebene Zelle wird sofort wieder verworfen. Auf diese Art und Weise bleibt der Speicherbedarf minimal. Mein erster Versuch mit über 1 Million Zeilen schlug allerdings mit einem Speicherüberlauf fehl (ich arbeite normalerweise mit 32-bit) - das ist klar, denn der Writer schreibt per Default alles in einen Memory-Stream. Erst das Umstellen auf einen gepufferten Stream (Workbook-Option boBufStream) brachte die Datei auf die Platte. Für XLSX hat das bei mir 34 sek gedauert, LibreOffice .ods wird in 25 Sek geschrieben (beides wiegesagt von einer 32-Bit-Anwendung). Das kommt mir auf den ersten Blick etwas lang vor, aber bis auf den Rand volle Dateien habe ich noch nie geschrieben, und wenn ich mir den FPSpreadsheet-SpeedTest in Erinnerung rufe, bei dem 100.000 Zeilen getestet werden, dann wären die extrapolierten etwa 3 Sekunden durchaus im Rahmen.

[Helios]

Hallo wp_xyz und alle anderen die sich beteiligt haben,
vielen Dank für Eure Rückmeldungen!
Ich habe heute Nacht mal mit (dem für mich neuen) Excel 365 gekämpft und mein Eindruck ist, dass Excel standardmäßig mit dem CP1252 8Bit=1Byte Zeichensatz (abgeleitet vom ISO 8859-1 (Latin-1)) arbeitet.
Kann das jemand mit mehr internen Know How evtl. bestätigen? Ich hab mal versucht Excel beim Import auf echtes UTF8 umzustellen. Bei mir hat das Laden der 500.000 Zeilen Testdatei mehrere Minuten gedauert (ich wollte es schon abbrechen). Daher werde ich irgendwie immer noch nicht den Verdacht los, das UTF8 die Strings deutlich stärker aufbläht (als das zusätzliche Byte bei Umlauten) als erwartet (kann man im fpspreadsheet testweise mal mit der AnsiString Variante in TCell schauen, ob Speicher und Laufzeit deutlich besser sind?). Mit den Erkenntnissen von gestern Nacht, somit 1:0 für fpspreadsheet.
Mir ist auch aufgefallen das fpspreadsheet tendenziell aus CSV Dateien kleinere XLSX Dateien erzeugt (kann natürlich daran liegen, das weniger Formate etc. abgelegt werden) aber aus meiner Usecase Sicht (Messdatenverarbeitung, braucht weniger Excel Schnickschnack) 2:0 für fpspreadsheet. So und nun die Frage wie schaffen wir es evtl. einen schnellen Sortier- und Filteralgorithmus evtl. auf Basis von Indizierung (Hash und B-Tree's etc.) hinzubekommen (da haben Excel und Libreoffice noch die Nase vorn).
Wäre das möglich, das auf Grid Ebene hinzubekommen und über diesen Weg es in das fpspreadsheet zu integrieren? Ein schneller Sorter/Filter würde die Verwendbarkeit der Grids nochmal aus meiner Sicht richtig pushen (kann ja gerne optional sein, da sicherlich die Sortier/Filtergeschwindigkeit auf Kosten des Speicherplatzes geht).
Viele wenn's und aber's, aber vielleicht eine gute Möglichkeit das fpspreadsheet noch einen weiteren Useability Kick Richtung 3:0 zu geben (und den Grids in Richtung der derzeit gängigen/wichtigen 1Mio Grenze) .
Aber vielleicht arbeitet da schon jemand dran?
Danke und Gruß
Helios
PS: Und sorry sollte ich mit diesem Vorstoß zu forsch gewesen sein.

[wp_xyz]

Ich habe heute Nacht mal mit (dem für mich neuen) Excel 365 gekämpft und mein Eindruck ist, dass Excel standardmäßig mit dem CP1252 8Bit=1Byte Zeichensatz (abgeleitet vom ISO 8859-1 (Latin-1)) arbeitet [...] Ich hab mal versucht Excel beim Import auf echtes UTF8 umzustellen.

Ah, ich beginne zu verstehen: du importierst CSV-Dateien, und deine Aussage, Excel würde standardmäßig mit CP1252 arbeiten, bezieht sich auf den Import von CSV-Dateien. Das kann ich glauben. Dass Excel generell mit CP1252 arbeitet ist nicht richtig, denn sonst könnte keine englischen, kyrillischen, griechischen und chinesischen Zeichen gemeinsam darstellen.

Jetzt ist mir auch klar, warum die pas-Datei deines Beispiel-Projekts in CP1252-Encoding ist, statt standardmäßig in UTF8. Als Folge ist mir nämlich aufgefallen, dass die Umlaute im Grid nicht richtig angezeigt werden. Erst wenn ich die Kodierung der unit1.pas auf utf8 umstelle, sind die Umlaute richtig. Aber dann sind sie in Excel falsch, das für den Import von .csv-Dateien CP1252 annimmt. Ich weiß nicht, wie man diese Voreinstellung ändern kann (oh Mann, wie haben die das Programm kompliziert gemacht...). Ändere ich die Dateiendung in .txt bekomme ich den Importassistenten zu sehen, in dem ich die Kodierung auf UTF8 einstellen kann - aber nun sehe ich auch das von dir berichtete, langsamere Einlesen von Excel infolge der Umwandlung zu UTF8.

Aber egal wie auch immer, die Datei wird irgendwann bis zum Speichern immer nach UTF8 umkodiert, denn die in der xlsx-Datei enthaltenen xml-Dateien sind UTF8.

einen schnellen Sortier- und Filteralgorithmus

Sortieren erfolgt in fpspreadsheet mit dem Standard-Quicksort, das ist kaum mehr zu verbessern.
Filtern ist in fpspreadsheet m.E. gar nicht implementiert...
Suchen, ok, könnte verbessert werden, durch Verwendung eines Index, aber ein Index für 1 Million Zellen ist auch nicht ohne und auch nicht ruckzuck erstellt. Ich habe es schon mehrmals gesagt: ich meine solche Aufgaben sollte man denjenigen überlassen, die dafür gemacht sind und die es können: Datenbanken.