Dela via

Optimera JSON-bearbetning med minnesintern OLTP

Gäller för: SQL Server 2017 (14.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL Database i Förhandsversion av Microsoft Fabric

Med SQL Server och Azure SQL Database kan du arbeta med text formaterad som JSON. Om du vill öka prestandan för frågor som bearbetar JSON-data kan du lagra JSON-dokument i minnesoptimerade tabeller med standardsträngkolumner (nvarchar typ). Om du lagrar JSON-data i minnesoptimerade tabeller ökar frågeprestandan med hjälp av låsfri dataåtkomst i minnet.

Lagra JSON i minnesoptimerade tabeller

I följande exempel visas en minnesoptimerad Product tabell med två JSON-kolumner, Tags och 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

Optimera JSON-bearbetning med ytterligare minnesinterna funktioner

Du kan helt integrera JSON-funktioner med befintliga minnesintern OLTP-tekniker. Du kan till exempel göra följande:

Verifiera JSON-kolumner

Du kan lägga till internt kompilerade CHECK-begränsningar som verifierar innehållet i JSON-dokument som lagras i en strängkolumn för att säkerställa att JSON-text som lagras i dina minnesoptimerade tabeller är korrekt formaterad.

I följande exempel skapas en Product tabell med en JSON-kolumn Tags. Kolumnen Tags har en CHECK-begränsning som använder funktionen ISJSON för att verifiera JSON-texten i kolumnen.

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

Du kan också lägga till den internt kompilerade CHECK-begränsningen i en befintlig tabell som innehåller JSON-kolumner.

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

Exponera JSON-värden med hjälp av beräknade kolumner

Med beräknade kolumner kan du exponera värden från JSON-text och komma åt dessa värden utan att hämta värdet från JSON-texten igen och utan att parsa JSON-strukturen igen. Värden som exponeras på det här sättet är starkt skrivna och fysiskt bevarade i de beräknade kolumnerna. Det går snabbare att komma åt JSON-värden med hjälp av beständiga beräknade kolumner än att komma åt värden direkt i JSON-dokumentet.

I följande exempel visas hur du exponerar följande två värden från kolumnen JSON Data:

  • Det land/den region där en produkt tillverkas.
  • Produkttillverkningskostnaden.

I det här exemplet uppdateras de beräknade kolumnerna MadeIn och Cost varje gång JSON-dokumentet som lagras i Data kolumnen ändras.

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

Indexvärden i JSON-kolumner

Du kan indexera värden i JSON-kolumner med hjälp av minnesoptimerade index. JSON-värden som indexeras måste exponeras och vara starkt typade med hjälp av beräknade kolumner, som beskrivs i föregående exempel.

Värden i JSON-kolumner kan indexeras med hjälp av både NONCLUSTERED- och HASH-standardindex.

  • NONCLUSTERED-index optimerar frågor som väljer radintervall efter något JSON-värde eller sorterar resultat efter JSON-värden.
  • HASH-index optimerar frågor som väljer en enskild rad eller några rader genom att ange ett exakt värde att hitta.

I följande exempel skapas en tabell som exponerar JSON-värden med hjälp av två beräknade kolumner. Exemplet skapar ett NONCLUSTERED-index på ett JSON-värde och ett HASH-index på det andra.

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

Intern kompilering av JSON-frågor

Om dina procedurer, funktioner och utlösare innehåller frågor som använder de inbyggda JSON-funktionerna ökar den interna kompilering prestandan för dessa frågor och minskar de CPU-cykler som krävs för att köra dem.

I följande exempel visas en intern kompilerad procedur som använder flera JSON-funktioner: JSON_VALUE, OPENJSONoch 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

Related content

En visuell introduktion till det inbyggda JSON-stödet finns i följande videor: