Database table with Blob-Field (large image file)

[bernhard_LA]

ich habe eine Datenbank-Tabelle erstellt in der ich diverse ( ~50 ) Metadaten in int, string, ... fields und in einem field dann auch das Bild als blob File speichere.
Die Blobs / Bilder sind bis zu 25.000 * 25.000 pixel große Jpgs files. (< 100 Mbyte je Bild).
Ich habe aktuell ca. 200 Datensätze gespeichert und ein

select * from table bzw.

select data1, data2...... from table
jedoch ohne das image field ist unendlich langsam.

Welche Optionen habe um den Zugriff auf die Datensätze zu beschleunigen?

[himitsu]

OK, ohne das Field, fehlt erstmal dieser BLOB in der Result/Temp-Table für die Abfrage
und anschließend wird der Blob auch nicht zum Client übertragen.
Daher entfällt hier schonmal eine zusätzliche Auslastung und Traffic.

Dann kommt es aber darauf an, wie/ob der Blob in den Record der DB-Datei liegt und ob der Teil somit auch zwangsläufig mit beim Lesen der DB-Datei und somit auch im Cache für dieser Tabelle landet.
Alternativ bliebe dann nur noch die Variante, dass du diese Blobs in eine andere Tabelle auslagerst.

[bernhard_LA]

Danke für die Antwort , d.h. die Idee erst ohne die Bilder/Blobs nur auf den metadaten zuarbeiten und dann nur das wirklich benötigte Bild zu laden müsste doch "auch schnell" funktionieren?

[himitsu]

Bezüglich der Übertragung und Anzeige im Client ja, dort schnell, wenn das Feld nicht enthalten ist.

Aber bezüglich der Abfrage in der DB kommt es drauf an, wie/wo das DBMS dieses Feld verwaltet/speichert,
also ob der Speicher dieser Felder dennoch gelesen wird, selbst ohne Abfrage des Feldes. (hätte aber vermutet, dass MSSQL hier intelligent genug ist)

[peterbelow]

Zitat von bernhard_LA:

Danke für die Antwort , d.h. die Idee erst ohne die Bilder/Blobs nur auf den metadaten zuarbeiten und dann nur das wirklich benötigte Bild zu laden müsste doch "auch schnell" funktionieren?

Nicht wenn die Datenbank die BLOB-Daten inline speichert, denn dann muss die DB Engine sie trotzdem lesen und in ihrem Cache verwalten, auch wenn sie garnicht benötigt werden. Himitsus Vorschlag, die BLOB-Daten in einer separaten Tabelle abzulegen ist eine gute Lösung, wenn die DB selbst dafür keine Option anbietet. Mit SQL Server kenne ich mich nicht aus, aber bei Oracle z. B. geht das einfach durch eine entsprechende Definition der BLOB-Spalte. Die Query gibt dann zunächst eine Art access token für die BLOB-Daten zurück, über das man dann die Daten holen kann wenn man sie braucht.

[Uwe Raabe]

Hier findet man ein paar Grundlagen dazu: Binary Large Object (Blob) Data (SQL Server)

[IBExpert]

auch von mir der hinweis, mach das so wie himitsu vorschlägt. Wir machen das mit firebird
in der aktuell größten DB mit 6.5 TB ähnlich, der eigentliche Blobinhalt ist eine eigene
Tabelle, die nur aus einer eigene ID als PK besteht und dem Blob Inhalt.

Alle Tabellen, die Daten dazu in anderen Feldern haben, nehmen für den referenzierten Inhalt
nur ein typengleichen feld als Foreign key, der die daten verbindet.

Bei kleinen Blobs lohnt sich das zwar eher selten, aber auf dem weg kannst du lokal
einen beliebigen Select auf den restlichen Daten machen und zum Beispiel im Front
end erst dann die daten in die Blob control TIMage oder sonstwas holen, wenn der
Anwender zum Beispiel im Grid auf einem Datensatz stehen geblieben ist, zB dann
per onnavigate event einen timer resetten und wenn der timer zB 500ms ohne reset
erreicht hat, dann mit extra query den blob inhalt holen (und falls das riesige dateien
sind wie röntgenbilder o.ä. dieser sogar temporär lokal cachen wenn die sich eh nicht
ständig ändern, zB als 12345.png in einem lokalen pfad, den du beim programmstart
oder ende ggf selber lokal aufräumst).

wir nutzen das verfahren dann auch noch dafür, das wir die eigentlichen blobs dann
je nach nummernkreis oder erstelldatum in firebird dann auch noch in extra datenbanken
zu packen, die dann nur noch readonly gebraucht werden als jahresarchiv.
bei firebird holt man sich dann eben je nach logik bei bedarf den blob aus einer
dieser datenbanken, wo man laut regel vermutet wo der inhalt wohl sein könnte, zB
anhand von einem Timestamp in deiner anderen Tabelle in der Main Database.

Warum mssql die blobs komplett lädt, auch wenn die spalte dafür gar nicht teil vom
select ist, frag ich mich aber trotzdem. Macht firebird zum beispiel selbst dann
nicht, wenn die blob spalte abgefragt wird, technisch bekommt man dabei nämlich nur
einen kleinen teil vom blob zum client übertragen (bei kleinen blobs das was eh schon
davon in der Datenpage ist und für den rest falls bedarf besteht den eigentlich blob
pointer, der anzeigt wor es weiter geht, und um den rest zu bekommen, müsste
der client entweder selber den kompletten inhalt per api abfragen oder man zum Beispiel
durch eine serverseitige funktion dafür sorgen, das die wirklich komplett gelesen
werden muss (wie im Beispiel durch den char_length funktion).
Der fetch all sorgte hier bei fb in ibexpert für die folgende page loads in der
tabelle mit 136000 blobs

Code:

select files.id, files.txt, files.removed, files.fn from files
=
Reads from disk to cache = 4.139

select files.id, files.txt, files.dat, files.removed, files.fn from files
=
Reads from disk to cache = 4.139

select files.id, files.txt, char_length(files.dat), files.removed, files.fn from files 
=
Reads from disk to cache = 50.804

Wenn mssql das aber immer ungefragt mitlädt wäre da die lösung ebenfalls, den blob
in eine andere tabelle auszulagern.

[Sinspin]

Was ist der Vorteil das Bild mit in der DB zu speichern?
Ich würde es als Datei belassen und es auf einem Fileserver ablegen zur Not mit FTP Zugriff, wenn kein direkt Zugriff besteht.

[t2000]

Zitat von Sinspin:

Was ist der Vorteil das Bild mit in der DB zu speichern?
Ich würde es als Datei belassen und es auf einem Fileserver ablegen zur Not mit FTP Zugriff, wenn kein direkt Zugriff besteht.

Wir speichern auch schon seit, .. eigentlich immer, die Dokumente, Bilder, etc. in der Datenbank. Es hat den Vorteil, dass die DB Backuptechniken diese in der Sicherung berücksichtigen. Weiterhin besteht manchmal das Problem, dass der Zugriff auf das Filesystem beschränkt ist. Replikationen (DB) nehmen auch die BLObs mit. Die Rechteverwaltung wird über die DB gesteuert. Usw.
Ich habe vor einigen Jahren mal einen sehr guten Artikel im Internet gelesen, der genau die Vor- und Nachteil beschreibt. Leider finde ich den nicht mehr. Das Ergebnis war aber, dass es je nach Projekt sowohl als auch besser sein kann. Also abhängig vom Einsatz.
Wir haben aber sogar nicht nur die eigentlichen BLObs in einer anderen Tabelle, sonder diese Tabellen gleich in einer weiteren DB. Unser MS SQL Server kann problemlos Daten aus verschiedenen DB's verknüpfen. Foreign Keys sind dann natürlich nicht erstellt.

[IBExpert]

Zitat von Sinspin:

Was ist der Vorteil das Bild mit in der DB zu speichern?
Ich würde es als Datei belassen und es auf einem Fileserver ablegen zur Not mit FTP Zugriff, wenn kein direkt Zugriff besteht.

Hast du schon mal ca 10 millionen dateien mit ca 3tb in ca 1mio verzeichnissen aus dem Filesystem von einem Windows Server oder auch
von einer qnap nas von einen Rechner auf den anderen Kopiert?

Wir hatten das problem gerade für einen Kunden aus dem medizinumfeld (röntgenbilder und anderer diagnose kram). das kopieren von
20 Firebird Datenbanken mit zusammen ca 120 GB dauerte ein paar minuten.

Dateisystem: alleine der xcopy mit /C /V /D /S braucht 15 stunden, auch wenn da gar nichts mehr zu kopieren gab, weil alles schon aktuell war.
Der erste Kopiervorgang brauchte ca 3 Tage! Und so schlecht war der alte windows Server da auch nicht.
qnaps sind da teilweise noch schlimmer ...

in der linux welt ist das mit rsync unglaublich schnell, aber das auch nur weil die den kram wohl dabei selbst in eine db packen.

fakt ist aber:
-dateien oder komplette pfade im filesystem versehentlich verschieben passiert andauernd und dann geht das suchen los, in einer db kannst du das ggf per trigger verbieten
-von dateien einfach mal so löschen oder übers wochenende vom trojaner verschlüsseln lassen auf der netzwerkfreigabe reden wir mal gar nicht erst
-ftp ist dann noch eine lustige quelle für halbe dateien, wenn da ein kopiervorgang nicht zuende lief
-Transaktions kontrolle und rollback Möglichkeiten etc. machen da noch weitere Punkte ...
-Fragmentierung usw. auf der Filesytemseite machen das auch nicht besser

bei 100000 Dateien ist das sicherlich vernachlässigbar, aber mit richtig großen datenmengen im Sinne von dateianzahl und Pfadstruktur sieht das ganz anders aus