Ottimizzare l'elaborazione JSON con OLTP in memoria

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL diIstanza gestita di SQL di Azure

SQL Server e il database SQL di Azure consentono di usare il testo formattato come JSON. Per migliorare le prestazioni delle query che elaborano dati JSON, è possibile archiviare i documenti JSON in tabelle ottimizzate per la memoria usando colonne di tipo stringa standard (tipo nvarchar). L'archiviazione dei dati JSON in tabelle ottimizzate per la memoria aumenta le prestazioni delle query mediante l'accesso ai dati in memoria senza blocco.

Archiviare dati JSON in tabelle ottimizzate per la memoria

Nell'esempio seguente è illustrata una tabella Product ottimizzata per la memoria contenente due colonne JSON, Tags e Data.

CREATE SCHEMA xtp;
GO

CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED, --standard column
    Name NVARCHAR(400) NOT NULL, --standard column
    Price FLOAT, --standard column
    Tags NVARCHAR(400), --JSON stored in string column
    Data NVARCHAR(4000) --JSON stored in string column
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Ottimizzare l'elaborazione JSON con funzionalità in memoria aggiuntive

È possibile integrare completamente le funzionalità JSON con le tecnologie OLTP in memoria esistenti. Ad esempio, è possibile eseguire le operazioni seguenti:

Convalidare le colonne JSON

È possibile aggiungere vincoli CHECK compilati in modo nativo che garantiscono la corretta archiviazione dei documenti JSON in una colonna stringa per garantire che il testo JSON memorizzato nelle tabelle ottimizzate per la memoria sia formattato correttamente.

L'esempio seguente crea una tabella Product con una colonna JSON Tags. La colonna Tags ha un vincolo CHECK che usa la funzione ISJSON per convalidare il testo JSON nella colonna.

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Tags NVARCHAR(400)
        CONSTRAINT [Tags should be formatted as JSON] CHECK (ISJSON(Tags) = 1),
    Data NVARCHAR(4000)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

È anche possibile aggiungere il vincolo CHECK compilato in modo nativo in una tabella esistente che contiene colonne JSON.

ALTER TABLE xtp.Product
    ADD CONSTRAINT [Data should be JSON]
        CHECK (ISJSON(Data)=1);

Esporre i valori JSON tramite le colonne calcolate

Le colonne calcolate consentono di esporre i valori del testo JSON e di accedere ai valori senza recuperare di nuovo il valore dal testo JSON e senza rieseguire l'analisi della struttura JSON. I valori esposti sono fortemente tipizzati e fisicamente salvati in modo permanente nelle colonne calcolate. L'accesso ai valori JSON tramite colonne calcolate salvate in modo permanente è più veloce rispetto all'accesso ai valori direttamente nel documento JSON.

Nell'esempio seguente viene illustrato come esporre i due valori seguenti dalla colonna JSON Data:

  • Il paese o l'area geografica di produzione del prodotto.
  • costo di produzione del prodotto.

Nell'esempio le colonne calcolate MadeIn e Cost vengono aggiornate a ogni modifica del documento JSON archiviato nella colonna Data.

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Data NVARCHAR(4000),
    MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') AS NVARCHAR(50)) PERSISTED,
    Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') AS FLOAT) PERSISTED
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Indicizzare i valori nelle colonne JSON

È possibile indicizzare i valori nelle colonne JSON usando indici ottimizzati per la memoria. I valori JSON indicizzati devono essere esposti e fortemente tipizzati usando colonne calcolate, come illustrato nell'esempio precedente.

I valori nelle colonne JSON possono essere indicizzati usando sia gli indici NONCLUSTERED sia gli indici HASH standard.

  • Gli indici NONCLUSTERED ottimizzano le query che eseguono la selezione di intervalli di righe in base a un valore JSON oppure l'ordinamento dei risultati in base ai valori JSON.
  • Gli indici HASH ottimizzano le query che selezionano una singola riga o poche righe specificando un valore esatto da trovare.

L'esempio seguente crea una tabella che espone i valori JSON mediante due colonne calcolate. L'esempio crea un indice NONCLUSTERED in un valore JSON e un indice HASH in un altro.

DROP TABLE IF EXISTS xtp.Product;
GO
CREATE TABLE xtp.Product (
    ProductID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(400) NOT NULL,
    Price FLOAT,
    Data NVARCHAR(4000),
    MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') AS NVARCHAR(50)) PERSISTED,
    Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') AS FLOAT) PERSISTED,
    INDEX [idx_Product_MadeIn] NONCLUSTERED (MadeIn)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

ALTER TABLE Product ADD INDEX [idx_Product_Cost] NONCLUSTERED HASH (Cost)
    WITH (BUCKET_COUNT = 20000);

Compilazione nativa di query JSON

Se le procedure, le funzioni e i trigger contengono query che usano le funzioni JSON predefinite, la compilazione nativa migliora le prestazioni delle query e riduce i cicli della CPU necessari per eseguirle.

L'esempio seguente illustra una procedura compilata in modo nativo che usa diverse funzioni JSON, ovvero JSON_VALUE, OPENJSON e JSON_MODIFY.

CREATE PROCEDURE xtp.ProductList (@ProductIds NVARCHAR(100))
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
    ATOMIC WITH (TRANSACTION ISOLATION LEVEL = snapshot, LANGUAGE = N'English')

    SELECT ProductID,
        Name,
        Price,
        Data,
        Tags,
        JSON_VALUE(data, '$.MadeIn') AS MadeIn
    FROM xtp.Product
    INNER JOIN OPENJSON(@ProductIds)
        ON ProductID = value
END;
GO

CREATE PROCEDURE xtp.UpdateProductData (
    @ProductId INT,
    @Property NVARCHAR(100),
    @Value NVARCHAR(100)
)
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
    ATOMIC WITH (TRANSACTION ISOLATION LEVEL = snapshot, LANGUAGE = N'English')

    UPDATE xtp.Product
    SET Data = JSON_MODIFY(Data, @Property, @Value)
    WHERE ProductID = @ProductId;
END
GO

Passaggi successivi

Per un'introduzione visiva al supporto JSON predefinito in SQL Server e nel database SQL di Azure, vedere i video seguenti: