Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Wenn Sie längere Texte in Tabellen abspeichern oder umfangreiche Binärdaten in OLE-Feldern ablegen, so bläht das die Datenbank auf. Während dieser Umstand bei lokalen Datenbanken heutzutage kein Problem mehr darstellt, sieht die Sache in einer Mehrbenutzerumgebung und Netzwerkzugriffen auf Backends schon anders aus. Hier sollte der Traffic minimiert werden. Da macht sich dann die Komprimierung solcher Datenfelder gut, was zu verbesserter Performance führen kann.
Komprimieren von Memo- und Long-Binary-Feldern
über das Für und Wider von in Tabellen abgelegten Binärdaten oder Textdokumente möchten wir hier nicht diskutieren. Das kann ganz einfach immer wieder vorkommen, und es gibt ganze Serverumgebungen, die davon weidlich Gebrauch machen, wie etwa der Microsoft Sharepoint-Server. Die Kompression dieser Daten verkleinert die Datenbankdateien und hilft dabei den benötigten Traffic zu den Clients zu verringern.
An sich ist derlei bereits in Access integriert. Der Datentyp Anlage (Attachment) speichert binäre Daten und komprimiert diese nach Bedarf, wobei Microsoft sich weitgehend darüber ausschweigt, bei welchen Dateitypen dies zutrifft und welcher Kompressionsalgorithmus zum Einsatz kommt. Nicht jeder ist jedoch ein Freund von Anlage-Feldern, denn aufgrund des dahinter liegenden versteckten Datenmodells, welches sich tatsächlich von Sharepoint-Technik ableitet, ist der programmtechnische Umgang mit ihnen recht komplex. Zudem können Anlagefelder nicht in Access 2003 oder früher angezeigt oder ausgelesen werden. Deshalb macht sich die Binärspeicherung in OLE-Feldern, bei denen es sich ja schlicht um Long-Binary-Felder handelt, besser.
Das Thema ist nicht neu. Bereits in der Ausgabe 3/2007 (Shortlink 466) von Access Im Unternehmen wurde es behandelt, weshalb wir an dieser Stelle zusätzlich auf jenen Beitrag verweisen. Dort wurde die Komprimierung über eine externe Komponente erreicht, eine zlib-DLL, auf die im VBA-Projekt verwiesen wird. Darauf kann verzichtet werden, wenn man sich der wenig beachteten Windows-API-Funktion RTLCompressBuffer bedient, die eigentlich zur Abteilung der Treiber-APIs gehört. Sie erreicht, vor allem bei Texten, durchaus vergleichbare Kompressionsraten. Der eingesetzte Algorithmus ist eine LZ-Variante (Lempel-Ziv).
Einschub: Kompression in Anlagefeldern
Da Microsoft keine komplette Liste veröffentlicht, welche Dateitypen in Anlage-Feldern denn nun komprimiert werden und welche nicht, sondern pauschal behauptet, dass jene unbearbeitet blieben, die bereits hinlänglich komprimiert sind, wie etwa JPG, haben wir einen einfachen Test unternommen. Eine Datenbank enthält eine Tabelle, die wiederum nur ein Anlage-Feld enthält. Wir messen die Dateigröße dieser ACCDB nach dem Hinzufügen jeweils einer größeren Datei eines bestimmten Typs und anschließendem Komprimieren und Reparieren der Datenbank. Verwendet werden gängige Dateitypen. Steigt die Größe der ACCDB um die Größe der hinzugefügten Datei, so dürfte die interne Kompression der Anlage nicht angesprochen haben. Die folgende Tabelle gibt Auskunft über das Ergebnis:
<b>Dateityp Größe Datei Größe DB Kompression, ca. (leer) - 404 kB - txt 3770 kB 1020 kB 6-fach doc 3430 kB 1778 kB 2,5-fach rtf 4400 kB 524 kB 37-fach xls 3640 kB 1020 kB 6-fach ppt 3800 kB 676 kB 14-fach pdf 3280 kB 2208 kB 1,8-fach bmp 4130 kB 516 kB 34-fach jpg 4300 kB 4752 kB keine tiff 3050 kB 3305 kB keine xml 3830 kB 992 kB 6,5-fach kml 6650 kB 540 kB 49-fach csv 3380 kB 740 kB 10-fach
Zu erwähnen wäre noch, dass dezidiert Dateien verwendet wurden, die entweder viele Wiederholungen enthalten oder viele Null-Bytes, sich mithin also gut komprimieren lassen. Das Ergebnis ist eindeutig: Die Kompression scheint zum Glück der Default zu sein. Nur ausgewählte Dateiformate, wie JPG, ZIP, DOCX, XLSX, die bekannterweise schon eine Kompression aufweisen, werden ausgenommen. Erstaunlich hoch sind die Kompressionsraten. Dass etwa eine RTF-Datei von über 4 MB Größe auf intern 120 kB schrumpft, war nicht zu erwarten. Als Datei-Container eignen sich Anlage-Felder demnach hervorragend.
Leider kann der in diesem Beitrag vorgestellte Algorithmus da nicht mithalten. Dennoch hat er seine Existenzberechtigung, und wir führen kurz die Vorteile gegenüber Attachments auf:
- Die komprimierte Speicherung ist nicht nur auf Dateien beschränkt, sondern kann etwa auch auf beliebige per VBA generierte Strings angewandt werden.
- Die Speicherung in OLE-Feldern kann auch in Access 2003 und früher erfolgen.
- Auch EXE-Dateien können integriert werden, was Access bei Anlage-Feldern verbietet.
- OLE-Felder lassen sich über ODBC ansprechen und deren Inhalte sind damit auch für SQL-Server oder andere DBMS nicht tabu.
- Der Umgang mit Long-Binary-Feldern ist unter VBA und DAO, sowie ADODB, erheblich einfacher, als mit Attachments.
Kompressionsroutine
Das Modul mdlCompression der Beispieldatenbank enthält eine Funktion CompressRTL, die Byte-Arrays komprimieren kann. Ihr Code ist in Listing 1 abgebildet. Er nutzt einige Windows-API-Funktionen, die im Modulkopf deklariert sind (Listing 2). Der Rückgabewert ist wiederum ein Byte-Array mit den komprimierten Daten. Das Ganze kommt ausgesprochen übersichtlich daher. Auf die genaue Funktion des Codes und der API-Aufrufe möchten wir hier gar nicht eingehen. Einige Hinweise dürfen jedoch nicht fehlen.
Private Declare Function RtlGetCompressionWorkSpaceSize _ Lib "NTDLL" (ByVal Flags As Integer, WorkSpaceSize As _ Long, UNKNOWN_PARAMETER As Long) As Long Private Declare Function NtAllocateVirtualMemory Lib _ "ntdll.dll" (ByVal ProcHandle As Long, BaseAddress As _ Long, ByVal NumBits As Long, regionsize As Long, ByVal _ Flags As Long, ByVal ProtectMode As Long) As Long Private Declare Function RtlCompressBuffer Lib "NTDLL" _ (ByVal Flags As Integer, ByVal BuffUnCompressed As Long, _ ByVal UnCompSize As Long, ByVal BuffCompressed As Long, _ ByVal CompBuffSize As Long, ByVal UNKNOWN_PARAMETER As _ Long, OutputSize As Long, ByVal WorkSpace As Long) As _ Long Private Declare Function RtlDecompressBuffer Lib _ "NTDLL" (ByVal Flags As Integer, ByVal BuffUnCompressed _ As Long, ByVal UnCompSize As Long, ByVal BuffCompressed _ As Long, ByVal CompBuffSize As Long, OutputSize As _ Long) As Long Private Declare Function NtFreeVirtualMemory Lib _ "ntdll.dll" (ByVal ProcHandle As Long, BaseAddress As _ Long, regionsize As Long, ByVal Flags As Long) As Long
Listing 1: Die fünf im Modul verwendeten API-Funktionen
So enthält die Prozedur keine Fehlerbehandlung. übergeben Sie etwa ein undimensioniertes Array, so kracht es gleich in der ersten Zeile beim Ausdruck UBound. Auch sonst werden die Rückgabewerte der API-Funktionen nicht ausgewertet, so dass Fehlfunktionen nicht offensichtlich sind. Allerdings dürfte es auch kaum einen Fall geben, der hier bei einem ordnungsgemäßen Byte-Array zu Fehlern führt. Kritisch ist bestenfalls der Wert der von RTLCompressBuffer zurückgelieferten Variablen retSize, die angibt, wie viele Bytes im Ergebnis enthalten sind. Er wird im weiteren Verlauf dazu verwendet, um das Ergebnis-Array binOut neu zu dimensionieren. Hier könnten rein theoretisch Werte anfallen, die über den erlaubten Wertebereich hinausgehen. Uns ist bei allen Tests derlei aber nicht untergekommen.
Nun möchten Sie möglicherweise nicht Byte-Arrays komprimieren, sondern Strings. Das macht eine Konvertierung dieser erforderlich. Gegeben sei etwa ein Text in der Variablen sText, der zu komprimieren wäre. Dann verwenden Sie die VBA-Funktion StrConv:
Dim bin() As Byte Dim binRet() As Byte bin = StrConv(sText, vbFromUnicode) binRet = CompressRTL(bin)
StrConv kann Strings in Byte-Arrays überführen und umgekehrt. Da es sich bei VBA-Strings immer um Unicode handelt, ist der Parameter vbFromUnicode der richtige. Er macht aus dem String quasi ein ANSI-Byte-Array. Dieses übergeben Sie dann der Funktion CompressRTL des Moduls.
Dekompressionsroutine
Mit dem komprimierten Byte-Array selbst können Sie wenig anfangen. Entweder speichern Sie es in einer Datei ab, oder im OLE-Feld einer Tabelle. Es reicht dazu schlicht die Zuweisung an den Feldinhalt. Beispiel:
Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("Binaertabelle") rs.Edit ''''oder rs.AddNew rs!OleFeld1.Value = binRet rs.Update
Einstmals mussten Sie sich mit der Methode AppendChunk eines DAO-Felds herumschlagen und in einer Schleife Teile des Byte-Arrays (Chunks) hinzufügen. Das ist veraltet. Die unmittelbare Zuweisung funktioniert bei heutigen Rechnerausstattungen auch bei großen Byte-Arrays. Das Limit liegt lediglich beim dem DAO-Thread zugewiesenen RAM-Bereich (Heap Working Set) der Datenbank-Engine.
Nun möchten Sie die komprimierten Daten wieder in ihrer ursprünglichen Form zurückgewinnen. Das übernimmt die Dekompressionsroutine DecompressRTL des Moduls, welche in Listing 3 abgebildet ist. Auch ihr Umfang ist erfreulich gering. Ihr Aufruf entspricht genau dem für die Komprimierprozedur CompressRTL. Sie holen sich etwa die komprimierten Daten aus der Tabelle und erhalten die entpackten im Rückgabe-Array, welches Sie wieder per StrConv in einen Text verwandeln:
Public Function DeCompressRTL(Buffer() As Byte) As Byte() Dim LSize As Long, memSize As Long Dim binOut() As Byte LSize = UBound(Buffer) * 12.5 ReDim binOut(LSize) RtlDecompressBuffer 2& Or &H100, VarPtr(binOut(0)), LSize, _ VarPtr(Buffer(0)), 1& + UBound(Buffer), memSize If (memSize > 0) And (memSize <= UBound(binOut)) And _ (memSize < 2000000) Then ReDim Preserve binOut(memSize - 1) DeCompressRTL = binOut End If End Function
Listing 3: Routine DecompressRTL zum Entpacken der Byte-Arrays
Dim rs As DAO.Recordset Dim bin() As Byte Dim binRet() As Byte Dim sText As String Set rs = CurrentDb.OpenRecordset("Binaertabelle") bin = rs!OleFeld1.Value binRet = DecompressRTL(bin) sText = StrConv(binRet, vbUnicode)
Die String-Umwandlung passiert nun natürlich umgekehrt mittels des Parameters vbUnicode. Haben Sie nicht Texte, sondern Dateien komprimiert, so entfällt die Umwandlung per StrConv selbstverständlich jeweils.
Dateien komprimiert im OLE-Feld
Das bisher Gesagte zusammengefasst mündet in ein praktisches Beispiel, das so auch in der Demo-Datenbank vorhanden ist. Alle Dateien eines bestimmten Verzeichnisses sollen komprimiert als Datensätze in einer Tabelle abgespeichert werden. Die dafür vorgesehene Routine ImportFiles finden Sie in Listing 4, die Entwurfsansicht der Zieltabelle in Bild 1.
Bild 1: Aufbau der Tabelle tblBinary zum Abspeichern binärer Daten
Sub ImportFiles(Byval sDir As String) Dim sFile As String Dim bin() As Byte Dim rs As DAO.Recordset Dim F As Integer CurrentDb.Execute "DELETE FROM tblBinary" Set rs = CurrentDb.OpenRecordset( _ "SELECT * FROM tblBinary", dbOpenDynaset) sFile = Dir(sDir & "\*.*") Do While Len(sFile) > 0 F = FreeFile Open sDir & "\" & sFile For Binary As F ReDim bin(LOF(F) - 1) Get F, , bin Close F With rs .AddNew !Path = sDir & "\" & sFile !FileLen = FileLen(sPath) !BLOB.Value = bin !CompressedBLOB.Value = CompressRTL(bin) !LenCompressed = !CompressedBLOB.FieldSize !Ratio = Round(!FileLen.Value / _ !LenCompressed.Value, 3) .Update End With sFile = Dir Loop rs.Close End Sub
Listing 4: Einlesen der Dateien eines Verzeichnisses in die Tabelle
Das Feld Path nimmt den vollständigen Pfad zur Datei auf, FileLen deren Größe in Bytes. BLOB ist der Container für die unkomprimierten Daten. In der Praxis würden Sie dieses Feld nicht verwenden, da im OLE-Feld CompressedBLOB die komprimierten Daten stehen, aus denen die Datei ja wiederhergestellt werden kann. LenCompressed speichert die Größe der komprimierten Daten und Ratio als Gleitkommazahl das Kompressionsverhältnis. Diese beiden letzten Felder könnte man ebenfalls weglassen, da sich LenCompressed aus CompressedBLOB.FieldSize ergäbe, und Ratio aus Selbigem geteilt durch FileLen. Die beiden Felder sind hier integriert, damit man in der Datenblattansicht der Tabelle gleich eine übersicht über den Erfolg der Kompression hat.
Die mit einigen Beispieldateien gefüllte Tabelle zeigt Bild 2. Sie ist absteigend nach dem Kompressionsfaktor (Ratio) sortiert. Es wird deutlich, dass sich bei den Bilddateien und PDFs keine nennenswerte Kompression einstellt. Notepad.exe verringert sich immerhin um ein Viertel. Bei den Bitmaps hängt der Faktor vom Bildinhalt ab. Je mehr gleichfarbige Flächen enthalten sind, desto besser die Kompression. Dasselbe gilt für Excel- und Word-Dokumente. Die Kompression ist umso erfolgreicher, je mehr Wortdoppelungen in diesen Dokumenten enthalten sind.
Bild 2: Die Datenblattansicht der Tabelle tblBinary zeigt den Ursprung der Dateien neben zusätzlichen Angaben zur Kompression
Wir haben alle Dateien zum Vergleich einmal in ZIP-Archive gepackt. Die resultierenden Dateigrößen sind allgemein deutlich geringer, als die mit der API-Funktion erreichten. Das Nonplusultra stellt der Algorithmus von RTLCompressBuffer also nicht dar. Dafür arbeitet die Funktion allerdings außerordentlich schnell. Möchten Sie etwa eine größere Zahl von Office-Dokumenten in Ihrer Datenbank ablegen, so verkleinern Sie deren Dateigröße mit der Kompression etwa um den Faktor zwei bis drei.
Doch zurück zur Prozedur in Listing 4. Hier werden in einer Do While-Schleife alle Dateien des als Parameter übergebenen Verzeichnisses sDir abgearbeitet und in die Tabelle tblBinary übertragen. Auf diese ist, nachdem sie in der ersten Zeile geleert wird, ein Recordset rs geöffnet. Eine Datei liest die Open-Anweisung im Binärmodus in das Byte-Array bin ein. Dessen Inhalt wird direkt dem Feld BLOB als Wert übergeben, während das Feld CompressedBLOB das Ergebnis des Funktionsaufrufs der weiter oben behandelten Prozedur CompressRTL speichert. Das Einlesen der Dateien von Bild 2 dauerte bei uns mit dieser Routine gerade einmal eine Sekunde! Die Kompression selbst geschah jeweils in wenigen Millisekunden.
Texte komprimiert im OLE-Feld
Für das Abspeichern komprimierter Textdateien gibt es in der Beispieldatenbank eine gesonderte Tabelle tblTexte (s. Bild 3) und eine eigene Prozedur ImportTexts. Diese Routine entspricht ziemlich genau der vorigen Prozedur ImportFiles, weshalb ein Listing entfallen kann. Nur benutzt sie zusätzlich die Konvertierung des eingelesenen Byte-Arrays per StrConv in Strings. Die wiederum nimmt das Memo-Feld Text auf, während die komprimierten Daten in das OLE-Feld CompressedText gelangen. Der Aufbau der Tabelle entspricht ansonsten dem von tblBinary.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo