Ottimizzare l'elaborazione JSON con OLTP in memoriaOptimize JSON processing with in-memory OLTP

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2017)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2017)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server e il database SQL di Azure consentono di usare il testo formattato come JSON.SQL Server and Azure SQL Database let you work with text formatted as 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).To increase the performance of queries that process JSON data, you can store JSON documents in memory-optimized tables using standard string columns (NVARCHAR type). L'archiviazione dei dati JSON in tabelle con ottimizzazione per la memoria aumenta le prestazioni delle query mediante l'accesso ai dati in memoria senza blocco.Storing JSON data in memory-optimized tables increases query performance by leveraging lock-free, in-memory data access.

Archiviare dati JSON in tabelle con ottimizzazione per la memoriaStore JSON in memory-optimized tables

Nell'esempio seguente è illustrata una tabella Product con ottimizzazione per la memoria contenente due colonne JSON, Tags e Data.The following example shows a memory-optimized Product table with two JSON columns, Tags and 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);

Ottimizzare l'elaborazione JSON con funzionalità in memoria aggiuntiveOptimize JSON processing with additional in-memory features

Le funzionalità disponibili in SQL Server e nel database SQL di Azure consentono di integrare le funzionalità JSON con le tecnologie OLTP in memoria esistenti.Features that are available in SQL Server and Azure SQL Database let you fully integrate JSON functionality with existing in-memory OLTP technologies. Ad esempio, è possibile eseguire le operazioni seguenti:For example, you can do the following things:

Convalidare le colonne JSON Validate JSON columns

SQL Server e il database SQL di Azure consentono di aggiungere vincoli CHECK compilati in modo nativo che convalidano il contenuto dei documenti JSON archiviati in una colonna di tipo stringa.SQL Server and Azure SQL Database let you add natively compiled CHECK constraints that validate the content of JSON documents stored in a string column. I vincoli JSON CHECK compilati in modo nativo garantiscono la corretta formattazione del testo JSON archiviato nelle tabelle con ottimizzazione per la memoria.With natively compiled JSON CHECK constraints, you can ensure that JSON text stored in your memory-optimized tables is properly formatted.

L'esempio seguente crea una tabella Product con una colonna JSON Tags.The following example creates a Product table with a JSON column Tags. La colonna Tags ha un vincolo CHECK che usa la funzione ISJSON per convalidare il testo JSON nella colonna.The Tags column has a CHECK constraint that uses the ISJSON function to validate the JSON text in the column.

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);

È anche possibile aggiungere il vincolo CHECK compilato in modo nativo in una tabella esistente che contiene colonne JSON.You can also add the natively compiled CHECK constraint to an existing table that contains JSON columns.

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

Esporre i valori JSON usando colonne calcolate Expose JSON values using computed columns

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.Computed columns let you expose values from JSON text and access those values without fetching the value from the JSON text again and without parsing the JSON structure again. I valori esposti sono fortemente tipizzati e fisicamente salvati in modo permanente nelle colonne calcolate.Values exposted in this way are strongly typed and physically persisted in the computed columns. L'accesso ai valori JSON tramite colonne calcolate salvate in modo permanente è più veloce rispetto all'accesso ai valori direttamente nel documento JSON.Accessing JSON values using persisted computed columns is faster than accessing values in the JSON document directly.

Nell'esempio seguente viene illustrato come esporre i due valori seguenti dalla colonna JSON Data:The following example shows how to expose the following two values from the JSON Data column:

  • paese di produzione del prodotto;The country where a product is made.
  • costo di produzione del prodotto.The product manufacturing cost.

Nell'esempio le colonne calcolate MadeIn e Cost vengono aggiornate a ogni modifica del documento JSON archiviato nella colonna Data.In this example, the computed columns MadeIn and Cost are updated every time the JSON document stored in the Data column changes.

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)

) WITH (MEMORY_OPTIMIZED=ON);

Indicizzare i valori nelle colonne JSON Index values in JSON columns

SQL Server e il database SQL di Azure consentono di indicizzare i valori nelle colonne JSON tramite indici ottimizzati per la memoria.SQL Server and Azure SQL Database let you index values in JSON columns by using memory-optimized indexes. I valori JSON indicizzati devono essere esposti e fortemente tipizzati usando colonne calcolate, come illustrato nell'esempio precedente.JSON values that are indexed must be exposed and strongly typed by using computed columns, as described in the preceding example.

I valori nelle colonne JSON possono essere indicizzati usando sia gli indici NONCLUSTERED sia gli indici HASH standard.Values in JSON columns can be indexed by using both standard NONCLUSTERED and HASH indexes.

  • 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.NONCLUSTERED indexes optimize queries that select ranges of rows by some JSON value or sort results by JSON values.
  • Gli indici HASH ottimizzano le query che selezionano una singola riga o poche righe specificando un valore esatto da trovare.HASH indexes optimize queries that select a single row or a few rows by specifying an exact value to find.

L'esempio seguente crea una tabella che espone i valori JSON mediante due colonne calcolate.The following example builds a table that exposes JSON values by using two computed columns. L'esempio crea un indice NONCLUSTERED in un valore JSON e un indice HASH in un altro.The example creates a NONCLUSTERED index on one JSON value and a HASH index on the other.

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)

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

Compilazione nativa di query JSON Native compilation of JSON queries

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.If your procedures, functions, and triggers contain queries that use the built-in JSON functions, native compilation increases the performance of these queries and reduces the CPU cycles required to run them.

L'esempio seguente illustra una procedura compilata in modo nativo che usa diverse funzioni JSON, ovvero JSON_VALUE, OPENJSON e JSON_MODIFY.The following example shows a natively compiled procedure that uses several JSON functions - JSON_VALUE, OPENJSON, and 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
        JOIN OPENJSON(@ProductIds)
            ON ProductID = value

END;

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

Altre informazioni sul supporto JSON integrato in SQL ServerLearn more about the built-in JSON support in SQL Server

Per soluzioni specifiche, casi d'uso e indicazioni, vedere i post del blog sul supporto JSON integrato in SQL Server e nel database SQL di Azure redatti da Jovan Popovic, Microsoft Program Manager.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.