Delen via

JSON-verwerking optimaliseren met OLTP in het geheugen

  • Artikel

van toepassing op: SQL Server 2017 (14.x) en hoger Azure SQL DatabaseAzure SQL Managed Instance

Met SQL Server en Azure SQL Database kunt u werken met tekst die is opgemaakt als JSON. Als u de prestaties van query's die JSON-gegevens verwerken wilt verbeteren, kunt u JSON-documenten opslaan in tabellen die zijn geoptimaliseerd voor geheugen met behulp van standaardtekenreekskolommen (nvarchar type). Het opslaan van JSON-gegevens in tabellen die zijn geoptimaliseerd voor geheugen verhoogt de queryprestaties door gebruik te maken van vergrendelingsvrije, in-memory gegevenstoegang.

JSON opslaan in tabellen die zijn geoptimaliseerd voor geheugen

In het volgende voorbeeld ziet u een voor geheugen geoptimaliseerde Product tabel met twee JSON-kolommen, Tags en 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

JSON-verwerking optimaliseren met aanvullende functies in het geheugen

U kunt JSON-functionaliteit volledig integreren met bestaande OLTP-technologieën in het geheugen. U kunt bijvoorbeeld het volgende doen:

JSON-kolommen valideren

U kunt systeemeigen gecompileerde CHECK-beperkingen toevoegen waarmee de inhoud van JSON-documenten die zijn opgeslagen in een tekenreekskolom, worden gevalideerd om ervoor te zorgen dat JSON-tekst die is opgeslagen in uw voor geheugen geoptimaliseerde tabellen correct is opgemaakt.

In het volgende voorbeeld wordt een Product tabel gemaakt met een JSON-kolom Tags. De kolom Tags heeft een CHECK-beperking die gebruikmaakt van de functie ISJSON om de JSON-tekst in de kolom te valideren.

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

U kunt ook de systeemeigen gecompileerde CHECK-beperking toevoegen aan een bestaande tabel die JSON-kolommen bevat.

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

JSON-waarden beschikbaar maken met behulp van berekende kolommen

Met berekende kolommen kunt u waarden uit JSON-tekst weergeven en deze waarden openen zonder de waarde opnieuw op te halen uit de JSON-tekst en zonder de JSON-structuur opnieuw te parseren. Waarden die op deze manier worden weergegeven, zijn sterk getypeerd en fysiek bewaard en opgeslagen in de berekende kolommen. Het openen van JSON-waarden met behulp van persistente berekende kolommen is sneller dan het rechtstreeks openen van waarden in het JSON-document.

In het volgende voorbeeld ziet u hoe u de volgende twee waarden uit de kolom JSON Data beschikbaar maakt:

  • Het land/de regio waar een product wordt gemaakt.
  • De productiekosten van het product.

In dit voorbeeld worden de berekende kolommen MadeIn en Cost telkens bijgewerkt wanneer het JSON-document wordt opgeslagen in de Data kolom wordt gewijzigd.

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

Indexwaarden in JSON-kolommen

U kunt waarden in JSON-kolommen indexeren met behulp van indexen die zijn geoptimaliseerd voor geheugen. JSON-waarden die zijn geïndexeerd, moeten worden weergegeven en sterk worden getypt met behulp van berekende kolommen, zoals beschreven in het voorgaande voorbeeld.

Waarden in JSON-kolommen kunnen worden geïndexeerd met zowel standaard NIET-GECLUSTERDE als HASH-indexen.

  • NIET GECLUSTERDE indexen optimaliseren query's die een reeks van rijen selecteren volgens een JSON-waarde of resultaten sorteren op JSON-waarde.
  • HASH-indexen optimaliseren query's die één rij of een paar rijen selecteren door een exacte waarde op te geven die moet worden gevonden.

In het volgende voorbeeld wordt een tabel gemaakt die JSON-waarden beschikbaar maakt met behulp van twee berekende kolommen. In het voorbeeld wordt een NIET-GECLUSTERDE index gemaakt op de ene JSON-waarde en een HASH-index op de andere.

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

Natuurlijke compilatie van JSON-zoekopdrachten

Als uw procedures, functies en triggers query's bevatten die gebruikmaken van de ingebouwde JSON-functies, verhoogt systeemeigen compilatie de prestaties van deze query's en vermindert u de CPU-cycli die nodig zijn om ze uit te voeren.

In het volgende voorbeeld ziet u een systeemeigen gecompileerde procedure die gebruikmaakt van verschillende JSON-functies: JSON_VALUE, OPENJSONen 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

Volgende stappen

Zie de volgende video's voor een visuele inleiding tot de ingebouwde JSON-ondersteuning in SQL Server en Azure SQL Database: