Partager via

Optimiser le traitement JSON avec OLTP en mémoire

  • Article

S’applique à : SQL Server 2017 (14.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

SQL Server et Azure SQL Database vous permettent de travailler avec du texte au format JSON. Pour améliorer les performances des requêtes qui traitent des données JSON, vous pouvez stocker des documents JSON dans des tables à mémoire optimisée en utilisant des colonnes de chaîne standard (type nvarchar). Le stockage des données JSON dans des tables à mémoire optimisée améliore les performances des requêtes grâce à un accès aux données en mémoire sans verrouillage.

Stocker des données JSON dans des tables optimisées en mémoire

L’exemple suivant montre une table optimisée en mémoire Product avec deux colonnes JSON, Tags et 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

Optimiser le traitement JSON avec des fonctionnalités en mémoire supplémentaires

Vous pouvez intégrer entièrement des fonctionnalités JSON aux technologies OLTP en mémoire existantes. Vous pouvez par exemple effectuer les opérations suivantes :

Valider des colonnes JSON

Vous pouvez ajouter des contraintes CHECK compilées nativement qui valident le contenu des documents JSON stockés dans une colonne de chaîne, afin de garantir que le texte JSON stocké dans vos tables à mémoire optimisée est correctement formaté.

L’exemple suivant crée une table Product avec une colonne JSON Tags. La colonne Tags a une contrainte CHECK qui utilise la fonction ISJSON pour valider le texte JSON dans la colonne.

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

Vous pouvez également ajouter la contrainte CHECK compilée en mode natif à une table existante qui contient des colonnes JSON.

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

Exposer les valeurs JSON à l’aide de colonnes calculées

Les colonnes calculées vous permettent d’exposer les valeurs d’un texte JSON et d’accéder à ces valeurs sans avoir à récupérer à nouveau la valeur du texte JSON ni à réanalyser la structure JSON. Les valeurs ainsi exposées sont fortement typées et physiquement conservées dans les colonnes calculées. L’accès aux valeurs JSON à l’aide de colonnes calculées persistantes est plus rapide que l’accès aux valeurs dans le document JSON directement.

L’exemple suivant montre comment exposer les deux valeurs suivantes à partir de la colonne JSON Data :

  • Le pays/la région où un produit est fabriqué.
  • Le coût de fabrication du produit.

Dans cet exemple, les colonnes calculées MadeIn et Cost sont mises à jour chaque fois que le document JSON stocké dans la colonne Data est changé.

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

Valeurs d’index dans les colonnes JSON

Vous pouvez indexer des valeurs dans des colonnes JSON en utilisant des index à mémoire optimisée. Les valeurs JSON qui sont indexées doivent être exposées et fortement typées à l’aide de colonnes calculées, comme illustré dans l’exemple suivant.

Les valeurs des colonnes JSON peuvent être indexées à l’aide des index standard NONCLUSTERED et HASH.

  • Les index NONCLUSTERED optimisent les requêtes qui sélectionnent des plages de lignes selon une valeur JSON ou trient les résultats par valeurs JSON.
  • Les index HASH optimisent les requêtes qui sélectionnent une seule ligne ou quelques lignes en spécifiant une valeur exacte à rechercher.

L’exemple suivant crée une table qui expose les valeurs JSON à l’aide de deux colonnes calculées. L’exemple crée un index NONCLUSTERED sur une valeur JSON et un index HASH sur l’autre.

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

Compilation native des requêtes JSON

Si vos procédures, fonctions et déclencheurs contiennent des requêtes qui utilisent les fonctions JSON intégrées, la compilation native améliore les performances de ces requêtes et réduit les cycles processeur nécessaires pour les exécuter.

L’exemple suivant montre une procédure compilée en mode natif qui utilise plusieurs fonctions JSON : JSON_VALUE, OPENJSON et 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

Étapes suivantes

Pour obtenir une présentation visuelle de la prise en charge intégrée de JSON dans SQL Server et Azure SQL Database, consultez les vidéos suivantes :