Storicizzazione del dato, compressione e interrogazione di strutture dati JSON in SQL Server

Durante lo sviluppo di software basati su strutture dati relazionali complesse, spesso si ha la necessità di effettuare una storicizzazione del dato, che sia per utilizzi futuri, oppure ai fini di analisi o statistica.

Più la mole di dati è consistente, più ci si scontra con la necessità di trovare un equilibrio tra prestazione di interrogazione del dato e dimensione che questo occupa sul disco.

In questo articolo vediamo una tecnica di storicizzazione di informazioni in formato JSON, effettuando una compressione del dato ed analizzando successivamente le tecniche per interrogarlo.

Per fare ciò, utilizzeremo diversi strumenti introdotti con la versione SQL Server 2016: COMPRESS/DECOMPRESS per la compressione, OPENJSON e JSON_VALUE per la lettura dei dati JSON.

COMPRESS/DECOMPRESS

I metodi COMPRESS e DECOMPRESS utilizzano l’algoritmo di compressione GZIP per comprimere un campo, che può essere, per esempio, NVARCHAR o NTEXT, in un campo VARBINARY(MAX) e viceversa.

Nel nostro caso effettuiamo una compressione di un campo NVARCHAR(MAX) contente una stringa formattata in formato JSON:

DECLARE @JsonString = '{ "chiave": "valore" }'
INSERT INTO Tabella (CampoCompresso) VALUES (COMPRESS(@JsonString))

Per decomprimere il campo è sufficiente utilizzare il metodo DECOMPRESS ed effettuare il CAST al tipo di dato che era in origine prima della compressione:

SELECT CAST(DECOMPRESS(CampoCompresso) AS NVARCHAR(MAX)) FROM Tabella

Preparazione della base dati

Vediamo ora un’esempio concreto su una base dati.

Creiamo un nuovo database su SQL Server 2016 o successivo ed al suo interno creiamo due tabelle: TabellaInChiaro e TabellaCompressa, utilizzando i due comandi:

CREATE TABLE [dbo].[TabellaInChiaro](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Json] [nvarchar](max) NOT NULL,
 	CONSTRAINT [PK_TabellaInChiaro] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
CREATE TABLE [dbo].[TabellaCompressa](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Json] [varbinary](max) NOT NULL,
	CONSTRAINT [PK_TabellaCompressa] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)

Entrambe le tabelle contengono due campi: un Id progressivo numerico ed un campo chiamato Json, che sarà di tipo NVARCHAR(MAX) nella tabella con i dati in chiaro e VARBINARY(MAX) in quella con i dati compressi.

Popoliamo ora le tabelle con molteplici dati utilizzando un ciclo WHILE:

DECLARE @Tot INT = 100000;
DECLARE @Indice INT = 0;
WHILE @Indice < @Tot
BEGIN
	INSERT INTO TabellaInChiaro ([Json]) VALUES ('
		{
			"Nome": "Prodotto' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '",
			"Prezzo": 15,
			"Qta": 50,
			"Descrizione": "Descrizione del prodotto ' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '"
		}
	')
	SET @Indice = @Indice + 1
END
DECLARE @Tot INT = 100000;
DECLARE @Indice INT = 0;
WHILE @Indice < @Tot
BEGIN
	INSERT INTO TabellaCompressa([Json]) VALUES (COMPRESS('
		{
			"Nome": "Prodotto' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '",
			"Prezzo": 15,
			"Qta": 50,
			"Descrizione": "Descrizione del prodotto ' + CAST(@Indice + 1 AS NVARCHAR(MAX)) + '"
		}
	'))
	SET @Indice = @Indice + 1
END

A questo punto abbiamo due tabelle che contengono i medesimi dati, da una parte in chiaro e dell’altra compressi.

Interrogazione

Per interrogare una stringa contenente informazioni formattate in formato JSON, SQL Server, come già detto dalla versione 2016, mette a disposizione diversi strumenti, tra cui: JSON_VALUE per estrarre un valore scalare da una stringa JSON e JSON_QUERY per estrarre invece un oggetto.

E’ possibile anche ottenere da un JSON un set di righe ed interrogarle come fossero comuni campi di una tabella SQL. Per fare ciò si utilizza OPENJSON.

Vediamo ora qualche esempio di interrogazione della nostra tabella con i dati in chiaro:

SELECT JSON_VALUE([Json], '$.Nome') FROM TabellaInChiaro
1Prodotto1
2Prodotto2
3Prodotto3
4Prodotto4

Questa QUERY mostra l’elenco dei nomi dei prodotti accedendo direttamente al campo Nome del JSON sfruttando il metodo JSON_VALUE.

Lo stesso risultato lo otteniamo anche utilizzando il metodo OPENJSON per trasformare la stringa JSON in un set di dati:

SELECT Nome FROM TabellaInChiaro CROSS APPLY OPENJSON([Json]) WITH (Nome NVARCHAR(MAX))
1Prodotto1
2Prodotto2
3Prodotto3
4Prodotto4

Continuando ad utilizzare OPENJSON inseriamo qualche condizione:

SELECT Nome, Prezzo, Qta FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json]) 
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)
WHERE Prezzo = 15
NomePrezzoQta
1Prodotto11550
2Prodotto21550
3Prodotto31550
4Prodotto41550

Oppure qualche operazione:

SELECT SUM(Prezzo), SUM(Qta) FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json]) 
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)
15000005000000

E i dati compressi?

Niente di più semplice, basta effettuare la decompressione del campo Json prima di passarlo come argomento ai metodi JSON_VALUE e/o OPENJSON. Per esempio:

SELECT JSON_VALUE(CAST(DECOMPRESS([Json]) AS NVARCHAR(MAX)), '$.Nome') FROM TabellaCompressa
1Prodotto1
2Prodotto2
3Prodotto3
4Prodotto4

O ancora:

SELECT SUM(Prezzo), SUM(Qta) FROM TabellaCompressa
CROSS APPLY OPENJSON(CAST(DECOMPRESS([Json]) AS NVARCHAR(MAX))) 
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT)

Già eseguendo queste semplici QUERY si nota come il risultato non venga mostrato nell’immediato ma è necessario qualche secondo in più di elaborazione.

Cerchiamo ora quindi di capire quali differenze ci sono in termini di prestazioni e in termini di dimensione del dato.

Qualche cifra…

Le cifre che seguono sono state calcolate utilizzando una base dati simile a quella degli esempi precedenti ma con una struttura JSON più ricca di proprietà e contenente oggetti nidificati.

Hardware utilizzato per i test:
Laptop Lenovo X1 Carbon 6a generazione
Intel i7-8550U
16GB di memoria RAM LPDDR3
SSD Samsung.

Dimensioni

TabellaInChiaro (byte)TabellaCompressa (byte)
3368227
3324227
3324225
3324227
Dimensioni in byte di 4 record contenuti in entrambe le tabelle.

Come si può vedere, il record compresso è del 93% circa più piccolo rispetto al record non compresso.

E quindi parlando di dimensioni totali della tabella:

TabellaInChiaro (megabyte)TabellaCompressa (megabyte)
31621
Dimensioni in megabyte dell’intera tabella.

Le dimensioni dei singoli record e della tabella sono state ricavate utilizzando il metodo DATALENGTH.

Prestazioni

Eseguiamo ora una semplice QUERY SELECT del Nome prodotto utilizzando il metodo OPENJSON:

SELECT Nome FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json]) 
WITH (Nome NVARCHAR(MAX))
TabellaInChiaro (ms)TabellaCompressa (ms)
27047916
Lettura del dato in chiaro 66% più veloce.

Ora proviamo ad ottenere lo stesso elenco ma utilizzando il metodo JSON_VALUE:

SELECT JSON_VALUE([Json], '$.Nome') FROM TabellaInChiaro
TabellaInChiaro (ms)TabellaCompressa (ms)
6954933
Lettura del dato in chiaro 86% più veloce.

Si può notare a colpo d’occhio come la QUERY che utilizza il metodo OPENJSON sia meno performante di quella che utilizza il metodo JSON_VALUE. Questo perché la stringa JSON con il primo metodo viene convertita in set di dati a differenza del secondo, dove si accede alla proprietà Nome in maniera diretta e scalare.

Proviamo ad effettuare ora una QUERY un po’ più complessa interrogando oggetti nidificati all’interno del JSON:

SELECT P.Nome, P.Prezzo, P.Qta, T.Nome, T.Prezzo, T.Qta  FROM TabellaInChiaro
CROSS APPLY OPENJSON([Json]) 
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT, SottoProdotto NVARCHAR(MAX) AS JSON) P
CROSS APPLY OPENJSON(SottoProdotto)
WITH (Nome NVARCHAR(MAX), Prezzo INT, Qta INT, SottoProdotto NVARCHAR(MAX) AS JSON) T
TabellaInChiaro (ms)TabellaCompressa (ms)
627912424
L’accesso ai dati compressi ha bisogno del doppio del tempo rispetto ai dati in chiaro.

Conclusione

Trovare un’equilibrio tra prestazioni dell’interrogazione del dato e lo spazio che questo occupa sul disco non è certamente semplice. Bisogna fare scelte, dettate anche da altre variabili, come ad esempio la quantità di volte che il dato deve essere interrogato oppure quali tipologie di interrogazioni vengono fatte più spesso (esempio interrogazioni temporali).

In questo articolo non c’è la soluzione ma semplicemente una possibile strada da poter percorrere. Un’idea per migliorare la storicizzazione potrebbe per esempio essere una gestione ibrida dove i dati necessari per filtrare i record o con un alto numero di accessi vengono salvati in chiaro, mentre i restanti vengono compressi per evitare consumo inutile di spazio.

Link utili

https://docs.microsoft.com/it-it/sql/relational-databases/data-compression/data-compression?view=sql-server-ver15

https://docs.microsoft.com/it-it/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.