Optimierung einer Abfrage in SQLite3

[Sempervivum]

Mal eine Frage an die Datenbankspezialisten:
Ich lese Daten aus einer SQLite3-Datenbank in Javascript aus und zeige sie mit HTML an. Die Datenbank enthält Bilder als Blobs. 400 Datensätze und die Datei ist 80MB groß. IMHO nicht besonders viel in der Datenbankwelt.
Erster simpler Ansatz:

select Bild, Name, Art, Beschreibung from `Substanzen - Substanzen`

Es handelt sich um eine App für Android, die man Dank Capacitor mit Javascript entwickeln kann. Geniale Sache, als Webentwickler kann man Apps entwickeln ohne allzu viel neu lernen zu müssen.
Leider stürzt mein Handy bei dieser Operation ab. Ich vermute, es hat zu wenig Arbeitsspeicher. Issue bei Github:
Crashes when querying all records from a database containing images (blobs) · Issue #439 · capacitor-community/sqlite
Ich konnte einen Workaround bauen, indem ich zunächst alle Datensätze ohne Bilder auslese und dann Bild-für-Bild die Bilder. Funktioniert so weit dann ohne Absturz, nur ziemlich langsam.
1. Maßnahme: Ich spreche die Bilder über den Namen an, also einen Index über diese Spalte angelegt. Brachte keine nennenswerte Verbesserung.
2. Maßnahme: Ich fand heraus, dass es eine rowid gibt, also die Bilder darüber angesprochen. Ebenfalls keine nennenswerte Verbesserung.
3. Maßnahme: Vielleicht braucht das Rendern des HTML bei jedem Datensatz nennenswert Zeit? Also nur alle 10 Datensätze das HTML im DOM hinzu gefügt. Leider auch keine nennenswerte Verbesserung.
Der Code:

            conn = new exports.SQLiteConnection(exports.CapacitorSQLite);
            console.log(conn);
            conn.createConnection(
                "Substances", false, "no-encryption", 3, false
            ).then(db => {
                console.log(db);
                db.open("Substances", false, 'no-encryption', 3, true).then(() => {
                    document.getElementById('feedback').innerHTML = `Texte werden aus Datenbank gelesen ...`;
                    const sql = "select rowid, Name, Art, Beschreibung from `Substanzen - Substanzen`";
                    db.query(sql).then(result => {
                        console.log(result);
                        document.querySelector('section.substances').innerHTML = '';
                        let nr = 0, htmlTotal = '';
                        const nrRows = result.values.length;
                        result.values.forEach(row => {
                            console.log(row.Name + ' - ' + row.Art);
                            const sqlImg = "select Bild from `Substanzen - Substanzen` where rowid='" + row.rowid + "'";
                            let srcImg = '';
                            db.query(sqlImg).then(result => {
                                if (result.values[0]) {
                                    const img = result.values[0].Bild;
                                    if (img) {
                                        console.log((typeof new Blob([new Uint8Array(img).buffer])))
                                        srcImg = URL.createObjectURL(new Blob([new Uint8Array(img).buffer]));
                                    }
                                }
                                // HTML aus Template lesen und die Platzhalter ersetzen
                                let html = document.getElementById('substance-tpl').innerHTML
                                    .replace(/{{name}}/g, row.Name)
                                    .replace(/{{art}}/g, row.Art)
                                    .replace(/{{beschreibung}}/g, row.Beschreibung)
                                    .replace(/{{img}}/g, srcImg);
                                htmlTotal += html;
                                nr++;
                                document.getElementById('feedback').innerHTML = `Datensatz ${nr} von ${nrRows} wurde gelesen`;
                                if (nr % 10 == 0 || nr == nrRows) {
                                    // HTML in section mit den Substanzen am Ende eintragen
                                    document.querySelector('section.substances')
                                        .insertAdjacentHTML('beforeend', htmlTotal);
                                    htmlTotal = '';
                                }
                            });
                        });
                        // db.close("Substances");
                        return 'database read successfully';
                    });
                });
            });

Jetzt vermute ich, dass einfach die Verarbeitung der Blobs mit den Bildern seine Zeit braucht. Oder gibt es noch andere Möglichkeiten, das zu optimieren?
Beste Grüße, Ulrich

 

[Sempervivum]

PS: Mir fällt gerade ein: Thumbnails von den Bildern erzeugen und zunächst nur diese lesen und anzeigen. Bei Bedarf bzw. beim Drauftippen dann das große Bild lesen und anzeigen. Macht aber die Pflege der DB aufwändiger. Zzt. erzeugt der Anwender die Datenbank mit dem DB-Browser für Sqlite, das ist recht einfach und gefällt ihm.

 

[Zvoni]

Wie gross ist jeder Blob (Im Schnitt)?
Du schreibst Dateigrösse 80MB --> Ist das die gesamte Datenbank?

 

[Sempervivum]

Ja, die 80 MB ist die Größe der Datenbankdatei. Und jedes Bild ist im Schnitt ca. 500kB groß. Also alles nicht so besonders viel.
In dem Issue auf Github gab es inzwischen Antworten aber die haben auch nicht zum Erfolg geführt.
Ich konnte inzwischen eine entscheidende Verbesserung erzielen, indem ich die Bilder gecached habe, also beim ersten Lesen aus der Datenbank im Dateisystem gespeichert und später nicht aus der DB sondern aus dem Dateisystem gelesen. Zunächst unverständlich, dass das schneller ist als das Lesen aus der DB. Ich kann es mir nur so erklären, dass Android die Daten zusätzlich im Arbeitsspeicher cached und dass das nicht der Fall ist, wenn man sie aus der DB liest.

 

[Zvoni]

uhhmm....
500KB x 400 = 200 MB + Overhead für die anderen Felder
Hab ich nen Denkfehler?
Was ich mal an deiner Stelle testen würde:
SQLite versteht das LIMIT keyword.
Würde mal schrittweise das LIMIT anheben, bis zu der stelle wo es abkachelt (und du hast ja auch geschrieben mit 20 Sätzen und 4 Bildern geht es ja)

select Bild, Name, Art, Beschreibung from `Substanzen - Substanzen` LIMIT 10 //20, 50, 100 usw.

 

[Sempervivum]

Ich hatte da eine Logausgabe drin, wo ich die Anzahl der gelesenen Datensätze angezeigt hatte. Ging bis 100, dann ist die App abgestürzt.

 

[Sempervivum]

Ach so,

500KB x 400 = 200 MB + Overhead für die anderen Felder
Hab ich nen Denkfehler?

nicht alle Datensätze haben ein Bild drin.

 

[Zvoni]

Sehr seltsam....

 

[Zvoni]

hmmm... --> Implementation Limits For SQLite

During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB.So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

Kommst du irgendwie ran, herauszufinden, was der Wert dafür auf deinem Android ist?
So wie ich es verstanden habe, wird das beim kompilieren der engine festgelegt, und es kann sein, dass der Wert in der Android-Lib deutlich niedriger ist, als am Rechner (Du erwähntest DB-Browser for SQLite).
Vielleicht erhälst du bei dem "100sten" Datensatz einen Fehler, weil der Satz zu lang ist

EDIT: Und ein Crash ist ja bekanntlich zu 99,999% eine unbehandelte Exception.
Du hast ein Log erwähnt. Kannst du mal die Fehlerbehandlung voll auf Anschlag drehen? Also alle Exceptions ins Log

 

[Sempervivum]

Was diesen Puffer betrifft, so habe ich keinen Zugang weil ich das nicht selbst kompiliere.

Das Log, das ich erwähnte, ist nur eine selbst programmierte Ausgabe, die nur das anzeigt, was ich programmiere.

Ins Debugging des Plugins oder von Capacitor einzusteigen, fehlen mir leider sowohl die Kenntnisse als auch die Zeit. Das wäre Sache des Entwicklers auf Github.

Generell habe ich den Eindruck gewonnen, dass binäre Daten und Blobs in Capacitor Fremdworte sind, wenn man mal vom Blobwriter-Plugin absieht. Würde mich nicht überraschen, wenn da noch etwas im Argen liegt.