Dela via

Optimera JSON-bearbetning med minnesintern OLTP

  • Artikel

gäller för: SQL Server 2017 (14.x) och senare Azure SQL DatabaseAzure SQL Managed Instance

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

Nästa steg

En visuell introduktion till det inbyggda JSON-stödet i SQL Server och Azure SQL Database finns i följande videor: