Freigeben über

Optimieren der JSON-Verarbeitung mit In-Memory-OLTP

  • Artikel

Gilt für: SQL Server 2017 (14.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance

SQL Server- und Azure SQL-Datenbanken ermöglichen die Verwendung von JSON-formatiertem Text. Sie können JSON-Dokumente mithilfe von Standardzeichenfolgenspalten (vom Typ nvarchar) in speicheroptimierten Tabellen speichern, um die Leistung der für die Verarbeitung von JSON-Daten verwendeten Abfragen zu verbessern. Beim Speichern von JSON-Daten in speicheroptimierten Tabellen wird die Abfrageleistung durch den Einsatz eines ungehinderten Zugriffs auf In-Memory-Daten gesteigert.

Speichern von JSON-Daten in speicheroptimierten Tabellen

Im folgenden Beispiel wird eine speicheroptimierte Product-Tabelle mit zwei JSON-Spalten, Tags und Data, erstellt:

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

Optimieren der JSON-Verarbeitung mit zusätzlichen In-Memory-Funktionen

Sie können JSON-Funktionen vollständig in vorhandene In-Memory-OLTP-Technologien integrieren. Sie haben beispielsweise folgende Möglichkeiten:

Überprüfen von JSON-Spalten

Sie können nativ kompilierte CHECK-Einschränkungen ergänzen, welche den Inhalt von in einer Zeichenfolgespalte gespeicherten JSON-Dokumenten validieren, um sicherzustellen, dass in Ihren speicheroptimierten Tabellen gespeicherter JSON-Text ordnungsgemäß formatiert wird.

Im folgenden Beispiel wird eine Product-Tabelle mit einer JSON-Spalte Tags erstellt. Die Tags-Spalte weist eine CHECK-Einschränkung auf, die die ISJSON-Funktion verwendet, um den JSON-Text in der Spalte zu überprüfen.

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

Sie können zudem die nativ kompilierte CHECK-Einschränkung in vorhandenen Tabellen hinzufügen, die JSON-Spalten enthalten.

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

JSON-Werte mithilfe von berechneten Spalten verfügbar machen

Mithilfe von berechneten Spalten können Sie Werte aus JSON-Text verfügbar machen und auf diese Werte zugreifen, ohne dass der Wert aus dem JSON-Text erneut abgerufen, erneut ausgewertet und ohne dass die JSON-Struktur erneut analysiert wird. Derartig verfügbar gemachte Werte sind mit einer starken Typisierung versehen und in den berechneten Spalten physisch persistent. Auf JSON-Werte kann mithilfe von permanent berechneten Spalten schneller zugegriffen werden als der direkte Zugriff auf Werte im JSON-Dokument.

Im folgenden Beispiel werden die beiden folgenden Werte aus der JSON Data-Spalte verfügbar gemacht:

  • Das Land/die Region, in dem/der ein Produkt hergestellt wird.
  • Die Herstellungskosten für das Produkt.

In diesem Beispiel werden die berechneten Spalten MadeIn und Cost jedes Mal aktualisiert, wenn sich das in der Spalte Data gespeicherte JSON-Dokument ändert.

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

Indexwerte in JSON-Spalten

Sie können Werte in JSON-Dokumenten mithilfe von speicheroptimierten Indizes indizieren. Indizierte JSON-Werte müssen wie im vorherigen Beispiel mithilfe von berechneten Spalten verfügbar gemacht und mit einer starken Typisierung versehen werden.

Werte in JSON-Spalten können sowohl mit Standard-NONCLUSTERED- als auch mit HASH-Indizes indiziert werden.

  • Mit NONCLUSTERED-Indizes werden Abfragen optimiert, mit denen Zeilenbereiche nach JSON-Werten ausgewählt werden oder mit denen Ergebnisse nach JSON-Werten sortiert werden.
  • HASH-Indizes optimieren Abfragen, die eine einzelne Zeile oder einige Zeilen auswählen, indem sie einen genauen zu suchenden Wert angeben.

Im folgenden Beispiel wird eine Tabelle erstellt, die JSON-Werte verfügbar macht, indem sie zwei berechnete Spalten verwendet. In dem Beispiel wird ein NONCLUSTERED-Index auf einem JSON-Wert erstellt und ein HASH-Index auf einem anderen.

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

Native Kompilierung von JSON-Abfragen

Wenn Ihre Prozeduren, Funktionen und Trigger Abfragen enthalten, die die integrierte JSON-Funktion verwenden, verbessert die native Kompilierung die Leistung dieser Abfragen und vermindert die für deren Ausführung erforderlichen CPU-Zyklen.

Im folgenden Beispiel ist eine nativ kompilierte Prozedur dargestellt, die verschiedene JSON-Funktionen enthält: JSON_VALUE, OPENJSON und 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ächste Schritte

Eine visuelle Einführung in die JSON-Unterstützung, die in SQL Server und Azure SQL-Datenbank integriert ist, finden Sie in den folgenden Videos: