Was tut COMPRESS?
Wenn ihr in einer Datenbank eine größere Menge an Daten liegen habt, die ihr nicht mehr benötigt, beispielsweise von gelöschten Datensätzen, die ihr archivieren möchtet, aber die in eurer Anwendung nicht mehr verwendet werden, dann wäre es doch schön, wenn diese Daten in komprimierter Form hinterlegt wären. Wie ihr das mit Hilfe der COMPRESS-Funktion erreichen könnt, seht ihr heute in unserem Dojo.
Wie könnt ihr COMPRESS verwenden?
Was tut COMPRESS? Die COMPRESS-Funktion wandelt den Eingabewert in ein Bytearray vom Typ VARBINARY(MAX) um, das mit dem GZIP-Algorithmus komprimiert wurde. Das bedeutet, ihr könnt dieser Funktion einen Wert, beispielsweise eine Zeichenfolge übergeben und dieser Wert wird dann komprimiert und zurückgegeben. Wie könnt ihr das nutzen? Um das zu demonstrieren, verwenden wir die WideWorldImporters Datenbank und arbeiten mit den Sales Invoicelines. Legen wir uns zunächst eine Tabelle an, in die wir gelöschte Daten archivieren können:
DROP TABLE IF EXISTS [Sales].[DeletedInvoiceLines]
SELECT TOP 0 [InvoiceLineID]
,[InvoiceID]
,[StockItemID]
,[Description]
,[PackageTypeID]
,[Quantity]
,[UnitPrice]
,[TaxRate]
,[TaxAmount]
,[LineProfit]
,[ExtendedPrice]
,[LastEditedBy]
,[LastEditedWhen]
INTO [Sales].[DeletedInvoiceLines]
FROM [Sales].[InvoiceLines]
Das ist natürlich nicht die beste Art, eine Archiv-Tabelle anzulegen, da ihr euch normalerweise Gedanken über Filegroups und Speicherorte machen solltet, aber für unsere momentan Demonstration wird diese Tabelle ausreichen.
Wenn ihr nun Daten löschen und in der neuen Tabelle archivieren möchtet, dann könnt ihr das wie folgt tun:
DELETE
FROM [Sales].[InvoiceLines]
OUTPUT
Deleted.[InvoiceLineID]
,Deleted.[InvoiceID]
,Deleted.[StockItemID]
,Deleted.[Description]
,Deleted.[PackageTypeID]
,Deleted.[Quantity]
,Deleted.[UnitPrice]
,Deleted.[TaxRate]
,Deleted.[TaxAmount]
,Deleted.[LineProfit]
,Deleted.[ExtendedPrice]
,Deleted.[LastEditedBy]
,Deleted.[LastEditedWhen]
INTO Sales.DeletedInvoiceLines
WHERE InvoiceID < 50000
So habt ihr nun die Daten aus der produktiven Tabelle gelöscht, sie jedoch nicht komprimiert, wie können wir es nun schaffen, die Daten zu komprimieren? Dafür kopieren wir zunächst die archivierten Daten zurück in die originale Tabelle:
INSERT INTO Sales.InvoiceLines SELECT * FROM Sales.DeletedInvoiceLines
Leider ist es nun so, dass nicht alle Datentypen für die Kompression mit COMPRESS geeignet sind. Daher erlaubt die COMPRESS-Funktion nur Werte folgender Datentypen als Argument:
binary(n)char(n)nchar(n)nvarchar(max)nvarchar(n)varbinary(max)varbinary(n)varchar(max)
Wie können wir uns das zunutze machen? Dafür wandeln wir zunächst die Daten der InvoiceLines-Tabelle in JSON-Objekte um. Mit einem SELECT Statement ginge das beispielsweise wie folgt:
SELECT
CONCAT('{',
' "InvoiceLineID" : ', [InvoiceLineID],
',"InvoiceID" : ', [InvoiceID],
',"StockItemID" : ', [StockItemID],
',"Description" : ', '"', [Description], '"',
',"PackageTypeID" : ', [PackageTypeID],
',"Quantity" : ', [Quantity],
',"UnitPrice" : ', [UnitPrice],
',"TaxRate" : ', [TaxRate],
',"TaxAmount" : ', [TaxAmount],
',"LineProfit" : ', [LineProfit],
',"ExtendedPrice" : ', [ExtendedPrice],
',"LastEditedBy" : ', [LastEditedBy],
',"LastEditedWhen" : ', '"', [LastEditedWhen], '"',
'}'
)
FROM Sales.InvoiceLines
Diese Definition ist nicht gerade elegant, aber sie erfüllt ihren Zweck. Arbeiten wir nun mit diesen Objekten in unserem Delete-Statement, indem wir diese Spalte komprimieren und in eine Archiv-Tabelle schreiben. Diese Archiv-Tabelle legen wir wie folgt an:
Schreiben wir nun die gelöschten Daten in diese Tabelle:
DELETE
FROM [Sales].[InvoiceLines]
OUTPUT
COMPRESS(
CONCAT('{',
' "InvoiceLineID" : ', Deleted.[InvoiceLineID],
',"InvoiceID" : ', Deleted.[InvoiceID],
',"StockItemID" : ', Deleted.[StockItemID],
',"Description" : ', '"', Deleted.[Description], '"',
',"PackageTypeID" : ', Deleted.[PackageTypeID],
',"Quantity" : ', Deleted.[Quantity],
',"UnitPrice" : ', Deleted.[UnitPrice],
',"TaxRate" : ', Deleted.[TaxRate],
',"TaxAmount" : ', Deleted.[TaxAmount],
',"LineProfit" : ', Deleted.[LineProfit],
',"ExtendedPrice" : ', Deleted.[ExtendedPrice],
',"LastEditedBy" : ', Deleted.[LastEditedBy],
',"LastEditedWhen" : ', '"', Deleted.[LastEditedWhen], '"',
'}'
))
INTO Sales.DeletedInvoiceLinesCompressed
WHERE InvoiceID < 50000
Für diese Tabelle ist das gewählte Vorgehen nicht besonders effizient, wie ihr einfach über die sp_spaceused-Funktion bestätigen könnt, da hier viele der Spalten, nämlich alle numerischen Werte nicht komprimiert werden und der Overhead durch das JSON-Format enorm ist. Doch wenn ihr das mit Tabellen tut, die viele VARCHAR-Felder enthalten, dann sieht das schon anders aus. Wählen wir als alternativen Ansatz nur die Komprimierung der Beschreibungs-Spalte aus. Dafür erzeugen wir folgende Tabelle:
SELECT TOP 0
[InvoiceLineID]
,[InvoiceID]
,[StockItemID]
,COMPRESS([Description]) as DescriptionCompressed
,[PackageTypeID]
,[Quantity]
,[UnitPrice]
,[TaxRate]
,[TaxAmount]
,[LineProfit]
,[ExtendedPrice]
,[LastEditedBy]
,[LastEditedWhen]
INTO Sales.DeletedInvoiceLinesCompressed_V2
FROM Sales.InvoiceLines
Nun passen wir das DELETE-Statement wie folgt an:
DELETE
FROM [Sales].[InvoiceLines]
OUTPUT
Deleted.[InvoiceLineID]
,Deleted.[InvoiceID]
,Deleted.[StockItemID]
,COMPRESS(Deleted.[Description])
,Deleted.[PackageTypeID]
,Deleted.[Quantity]
,Deleted.[UnitPrice]
,Deleted.[TaxRate]
,Deleted.[TaxAmount]
,Deleted.[LineProfit]
,Deleted.[ExtendedPrice]
,Deleted.[LastEditedBy]
,Deleted.[LastEditedWhen]
INTO Sales.DeletedInvoiceLinesCompressed_V2
WHERE InvoiceID < 50000
Wenn ihr nun mit sp_spaceused die drei Tabellen vergleich, dann seht ihr, dass die Daten bei der ursprünglichen Variante 28.360 KB Speicherplatz belegen. In der Version mit dem vollständigen JSON und sehr viel Overhead kommt ihr dagegen auf 53.544 KB Speicherplatz, was wie gesagt durch die schlechte Komprimierung der numerischen Werte und den Overhead der JSON-Struktur verursacht wird. Die Variante in der nur die Beschreibung komprimiert wird, hingegen, kommt auf 27.920 KB Speicherplatz für die Daten. Hier hat durch COMPRESS also eine Reduktion der Daten um etwa 2% stattgefunden.
Um die komprimierten Daten wieder abzurufen, können wir die DECOMPRESS-Funktion verwenden, doch Vorsicht: da DECOMPRESS nicht weiß, welcher Wert im komprimierten Byte-Array "versteckt" ist, bekommt ihr hier wieder ein VARBINARY(MAX) zurück, das ihr in den richtigen Datenwert CASTen müsst, wenn ihr es weiterverwenden möchtet:
SELECT
[InvoiceId]
,[InvoiceLineId]
,[DescriptionCompressed]
,CAST(DECOMPRESS([DescriptionCompressed]) as nvarchar(100))
FROM Sales.DeletedInvoiceLinesCompressed_V2
WHERE InvoiceID = 1
Im Dojo hattet ihr gesehen, dass ein COMPRESS nicht immer und überall sinnvoll ist. Außerdem habt ihr sicher an der Laufzeit der Beispiele bemerkt, dass ihr euch eine Reduktion des Speicherplatzes und der IO-Last eures Systems hier mit CPU-Last erkauft, denn das zeilenweise Aufrufen von COMPRESS macht eure Abfragen garantiert nicht gerade "sportlich". Also wägt gut ab, ob die Reduktion des Speicherplatzes den Overhead der Abfrage wert ist.
