Pchełki SQL: kompresja, DotNet i triggery

https://xpil.eu/mko 📋

Dziś pchełka nieco bardziej zaawansowana niż zwykle, za to dość przydatna.

Jak powszechnie wiadomo^{[citation needed]}, dane komputerowe można kompresować. Robi się to z rozmaitych przyczyn, z których główna to - uwaga, niespodzianka - próba zaoszczędzenia miejsca, które owe dane zajmują.

W tabeli w bazie danych dane również można kompresować i jest to na ogół całkiem niezły pomysł. Dane, zwłaszcza tekstowe, kompresują się całkiem nieźle. Czasami jesteśmy w stanie ścisnąć dane do jednej piątej oryginalnej objętości, a to już coś. Koszt CPU jest stosunkowo niewielki, różne testy pokazują różne wyniki, ale na ogół dodatkowe zużycie procesora na kompresję / dekompresję danych to około 2-3%.

Niestety, jak ze wszystkimi przysłowiowymi beczkami miodu, i ta zawiera łyżeczkę dziegciu. Otóż - o ile tylko nie używamy SQL Servera w wersji 2016 lub nowszej - nie będziemy w stanie skompresować danych typu Dużego, czyli takiego, gdzie pojedyncza dana zawiera więcej niż 8kB. Krótko mówiąc wszystkie typy danych kończące się na (MAX).

To znaczy tak: atrybut kompresji możemy takim danym zaaplikować i on tam sobie będzie cichutko siedział, ale zajmowane miejsce nie zmaleje nawet o bit.

Cóż więc zrobić, żeby móc jednak kompresować dane typu VARBINARY(MAX) albo NVARCHAR(MAX)?

W sukurs przychodzi słynna biblioteka SQL#, która udostępnia (za darmo!) mnóstwo funkcji .Net. Dzięki temu możemy ich używać "natywnie" w samym SQL-u, co daje mnóstwo fajnych, nowych możliwości.

W naszym przypadku mowa o funkcji Util_GZip, która na wejściu łyka nieskompresowane dane typu VARBINARY(MAX), a zwraca dane skompresowane (również VARBINARY(MAX)). Dzięki temu można sobie napisać "przelotkę" do kompresji danych, która będzie całkiem "przezroczysta" dla końcowego użytkownika. Dziś pokażę w jaki sposób to zrobić.

Zaczniemy od utworzenia prościutkiej tabeli:

CREATE TABLE dbo.tabela (
id BIGINT IDENTITY(1, 1) NOT NULL
, dane NVARCHAR(MAX) NULL
, CONSTRAINT PK_tabela PRIMARY KEY CLUSTERED ( id )
)

Następnie wypełnimy sobie naszą tabelę danymi testowymi:

WITH q1 AS (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
, q2 AS (SELECT NULL n FROM q1 q11, q1 q12, q1 q13)
INSERT    dbo.tabela
        SELECT    CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1) + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1)
                + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1) + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1)
                + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1) + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1)
                + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1) + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1)
                + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1) + CONVERT(VARCHAR(MAX), CONVERT(VARBINARY, NEWID()), 1)
FROM q2;

No ok, a więc mamy jakieś dane w tabeli i teraz chcemy zapodać im kompresję w taki sposób, żeby użytkownik się nie zorientował.

Zaczniemy od dodania do naszej tabeli kolumny z danymi skompresowanymi:

ALTER TABLE dbo.tabela
ADD dane_skompresowane VARBINARY(MAX) NULL

Teraz czas na wypełnienie tej kolumny danymi:

UPDATE	dbo.tabela
SET		dane_skompresowane = SQL#.Util_GZip(CONVERT(VARBINARY(MAX), dane))

W tym momencie mamy w każdym rekordzie dwie kopie danych: w kolumnie [dane] oraz w kolumnie [dane_skompresowane]. Nie do końca o to nam chodziło, dlatego w kolejnym kroku skasujemy kolumnę [dane]:

ALTER TABLE dbo.tabela
DROP COLUMN dane

No dobrze. Mamy teraz tabelę ze skompresowanymi danymi, hura! Tylko jak jej używać? Przecież nie będę za każdym razem odwoływał się do biblioteki SQL#, żeby zapuścić prostego SELECT-a, prawda?

Jeszcze pół biedy gdybym to tylko ja tej tabeli używał. Ale co z innymi użytkownikami?

Trzeba zaimplementować "przezroczysty" dostęp do danych w tej tabeli. Najlepiej w taki sposób, żeby użytkownik w ogóle nie musiał wiedzieć czegokolwiek o kompresji.

Idea ogólna jest taka: zmienić nazwę tabeli i utworzyć widok o nazwie [tabela], na którym następnie zbudujemy trigger do modyfikowania danych.

Zmieniamy nazwę tabeli:

sys.sp_rename
    @objname = N'dbo.tabela'
  , @newname = 'tabela_zip'

Tworzymy widok:

CREATE VIEW dbo.tabela
AS
   SELECT    id
     , CONVERT(NVARCHAR(MAX), SQL#.Util_GUnzip(dane_skompresowane)) AS dane
   FROM dbo.tabela_zip

Następnie - tu się właśnie wydarza wspomniana wcześniej "przezroczystość" - tworzymy trigger dla operacji INSERT i UPDATE:

CREATE TRIGGER dbo.trg_tabela_iu ON dbo.tabela
	INSTEAD OF INSERT, UPDATE
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE	@typ_operacji BIT = 0; -- 0: insert, 1: update
		IF EXISTS ( SELECT	1
					FROM	Deleted )
			SET @typ_operacji = 1;
		IF @typ_operacji = 0
			INSERT	dbo.tabela_zip
					( dane_skompresowane
					)
					SELECT	SQL#.Util_GZip(CONVERT(VARBINARY(MAX), Inserted.dane))
					FROM	Inserted
		ELSE
			UPDATE	tgt
			SET		tgt.dane_skompresowane = SQL#.Util_GZip(CONVERT(VARBINARY(MAX), i.dane))
			FROM	dbo.tabela_zip tgt
			JOIN	Inserted i
					ON tgt.id = i.id
	END

I to w zasadzie tyle. Wypadałoby jeszcze przetestować nasze rozwiązanie:

INSERT    INTO dbo.tabela(dane)
VALUES    ( 'dupa' )

SELECT    *
FROM    tabela
WHERE    id = 1001

Na wyjściu dostajemy 'dupa', czyli wszystko się zgadza.

Podsumowanie:

1. SQL Server 2012 (i wcześniejsze) nie umie kompresować danych o typach większych niż 8kB (a więc wszystkie ...(MAX))
2. Istnieje darmowa biblioteka SQL#: http://www.sqlsharp.com/, która pozwala używać wielu funkcji środowiska .Net bezpośrednio w zapytaniach T SQL
3. Na widokach można ustawiać triggery tak samo jak na tabelach.
4. Za pomocą pary funkcji SQL#.Util_GZip oraz SQL#Util_GUnzip możemy napisać trigger, który "w locie" obsłuży kompresję, dzięki czemu użytkownik końcowy może używać danych "po staremu", nie wiedząc nawet, że są one skompresowane.

Wady:

1. Kompresja możliwa wyłącznie na poziomie pojedynczej danej (a więc zapominamy o "page level compression" czy nawet "row level compression")
2. Brak możliwości sensownego indeksowania skompresowanych danych - a więc przy dużej ich ilości wyszukiwanie będzie bardzo, ale to bardzo nieefektywne.

Zalety:

1. Mniejsze zapotrzebowanie na przestrzeń dyskową.
2. O wiele mniejsze (nawet o rząd wielkości!) zużycie IO za cenę stosunkowo niedużego wzrostu zużycia CPU.
3. Możliwość przeczytania o tym na najnudniejszym blogu w naszej Galaktyce.
4. Walor edukacyjny dla nowicjuszy 😉

 

https://xpil.eu/mko 📋