Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server 2016–COMPRESS czyli ukryty skarb

W ostatnim czasie u kliku klientów powtórzył mi się ten sam scenariusz. Duże bazy danych (terabajty), których rozmiar był ściśle związany ze sporą liczbą przechowywanych w bazie dokumentów (głównie XML). Przykład: tabela u klienta, 120 tys. wierszy, w jednej z kolumn dokumenty XML trzymane jako varbinary(max) (hę???), rozmiar tabeli – 740 GB. Sporo. Na dodatek charakterystyka dostępu użytkowników pozwala stwierdzić, że dokumenty to w zasadzie archiwa, które są potrzebne w bardzo specjalnych sytuacjach, a dostęp do nich jest realizowany za pomocą bardzo selektywnych zapytań opartych o filtry na indeksowanych kolumnach (czyli w pełni poprawnie). I takie bazy puchną. I to całkiem szybko. Na szczęście można coś z tym zrobić, zwłaszcza mając do dyspozycji SQL Server 2016.

Funkcje skalarne COMPRESS i DECOMPRESS wprowadzone zostały w SQL Server 2016 bez rozgłosu. A szkoda, bo wielu programistów w poprzednich wersjach systemu było zmuszonych do tworzenia własnych mechanizmów kompresji (CLR z zaszytymi algorytmami ZIP-owania). COMPRESS oferuje silną kompresję GZIP. Jak silną i co może dać jej zastosowanie? Przeprowadziłem prosty eksperyment:

USE master;
GO
 
IF DB_ID('DemoDB') IS NOT NULL
BEGIN
  ALTER DATABASE DemoDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE DemoDB;
END;
GO
 
CREATE DATABASE DemoDB
CONTAINMENT = NONE
ON  PRIMARY ( 
	NAME = N'DemoDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\DemoDB.mdf', 
	SIZE = 100MB, 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 100MB 
)
LOG ON ( 
	NAME = N'DemoDB_log', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\DemoDB_log.ldf', 
  SIZE = 100MB, 
  MAXSIZE = 2048GB, 
  FILEGROWTH = 100MB)
GO
 
USE DemoDB;
GO
 
DROP TABLE IF EXISTS dbo.SampleTable;
GO
 
CREATE TABLE dbo.SampleTable (
  Id bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Code char(4) NOT NULL,
  CreateTime datetime2(7) NOT NULL DEFAULT (SYSDATETIME()),
  URI varchar(50) NOT NULL,
  Document varbinary(max) NOT NULL
);
GO
 
DECLARE @Start datetime = GETDATE();
DECLARE @x varbinary(max);
SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\Temp\Execution plan.xml', SINGLE_BLOB) AS T);
 
INSERT INTO dbo.SampleTable (Code, URI, Document)
SELECT
  CONVERT(char(4), column_id), 'http://plssug.org.pl', @x
FROM sys.all_columns;
 
SELECT DATEDIFF(ms, @Start, GETDATE());
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
 
SELECT Id, Code, CreateTime, URI, Document
FROM dbo.SampleTable
WHERE  Id = 100;
 
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
 
EXEC sp_spaceused 'dbo.SampleTable';
GO
 
---------------------------------------------------------------
 
USE master;
GO
 
IF DB_ID('DemoDB') IS NOT NULL
BEGIN
  ALTER DATABASE DemoDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE DemoDB;
END;
GO
 
CREATE DATABASE DemoDB
CONTAINMENT = NONE
ON  PRIMARY ( 
	NAME = N'DemoDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\DemoDB.mdf', 
	SIZE = 100MB, 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 100MB 
)
LOG ON ( 
	NAME = N'DemoDB_log', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\DemoDB_log.ldf', 
  SIZE = 100MB, 
  MAXSIZE = 2048GB, 
  FILEGROWTH = 100MB)
GO
 
USE DemoDB;
GO
 
DROP TABLE IF EXISTS dbo.SampleTable;
GO
 
CREATE TABLE dbo.SampleTable (
  Id bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Code char(4) NOT NULL,
  CreateTime datetime2(7) NOT NULL DEFAULT (SYSDATETIME()),
  URI varchar(50) NOT NULL,
  Document varbinary(max) NOT NULL
);
GO
 
DECLARE @Start datetime = GETDATE();
DECLARE @x varbinary(max);
SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\Temp\Execution plan.xml', SINGLE_BLOB) AS T);
 
INSERT INTO dbo.SampleTable (Code, URI, Document)
SELECT
  CONVERT(char(4), column_id), 'http://plssug.org.pl', COMPRESS(@x)
FROM sys.all_columns;
 
SELECT DATEDIFF(ms, @Start, GETDATE());
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
 
SELECT Id, Code, CreateTime, URI, CAST(DECOMPRESS(Document) AS xml) AS Document
FROM dbo.SampleTable
WHERE  Id = 100;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
 
EXEC sp_spaceused 'dbo.SampleTable';
GO

Szybki opis testu:

  • Dwukrotnie tworzę bazę danych DemoDB od początku.
  • Za każdym razem tworzę w niej tę samą tabelę dbo.SampleTable, w której jedną z kolumn jest kolumna Document. W kolumnie tej trzymane są binarne reprezentacje dokumentów XML.
  • Za pierwszym razem wstawiam dokumenty bez kompresji, a za drugim razem z kompresją (używając funkcji COMPRESS).
  • Mierzę:
    • czas wstawienia danych (liczba wierszy równa liczbie wierszy w widoku systemowym sys.all_columns),
    • czas odpowiedzi zapytania wyszukującego jeden konkretny dokument (Id = 100),
    • rozmiar tabeli.

Wynik:

Pomiar Bez kompresji Z kompresją (COMPRESS/DECOMPRESS)
Wstawianie danych 6600 ms 860 ms
Wyszukiwanie danych 39 ms 79 ms
Rozmiar tabeli 793616 KB 76040 KB

Uderzają rozbieżności w rozmiarach tabel (rząd wielkości!) oraz czasu wstawiania danych (także rząd wielkości!). Dla zapytania wyszukującego różnica była pomijalnie mała.

Zrobiłem też test za pomocą narzędzia ostress.exe – uruchamiałem wstawianie pojedynczych rekordów obiema metodami (100 sesji po 100 razy wstawiało 1 rekord). Wyniki zbliżone co do sekundy.

A zatem… W mojej ocenie można śmiało używać obu funkcji wszędzie tam, gdzie w grę wchodzi przechowywanie dużej ilości dokumentów (tekstowych lub binarnych) w bazach SQL Server 2016. Oczywiście, wymagana jest ingerencja w kod i logikę odpowiedzialną za zwracanie dokumentów do aplikacji klienckich. Jednak sądzę, że podane powyżej wyniki testu są wystarczającym argumentem, by pokusić się o zastosowanie tych, niekoniecznie mocno reklamowanych, nowych funkcji systemowych. Jeśli Twoja baza danych robi się wielka, a przyczyną może być przechowywanie dużej ilości dokumentów lub fragmentów dokumentów, to może być strzał w dziesiątkę.

Dajcie znać, jeśli użyjecie wspomnianych funkcji. Ciekaw jestem Waszych wrażeń.

Leave a Reply