Optimieren der JSON-Verarbeitung mit In-Memory-OLTP

  • Artikel

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

SQL Server- und Azure SQL-Datenbanken ermöglichen die Verwendung von JSON-formatiertem Text. Um die Leistung von Abfragen zu erhöhen, die JSON-Daten verarbeiten, können Sie JSON-Dokumente in speicheroptimierten Tabellen mithilfe von Standardzeichenfolgenspalten (nvarchar-Typ ) speichern. Das Speichern von JSON-Daten in speicheroptimierten Tabellen erhöht die Abfrageleistung mithilfe des sperrfreien Speicherdatenzugriffs.

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 OLTP-Technologien im Arbeitsspeicher integrieren. Sie haben beispielsweise folgende Möglichkeiten:

Überprüfen von JSON-Spalten

Sie können nativ kompilierte CHECK-Einschränkungen hinzufügen, die den Inhalt von JSON-Dokumenten überprüfen, die in einer Zeichenfolgenspalte gespeichert sind, um sicherzustellen, dass JSON-Text, der in Ihren speicheroptimierten Tabellen gespeichert ist, ordnungsgemäß formatiert ist.

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. Auf diese Weise verfügbar gemachte Werte werden stark typisch und physisch in den berechneten Spalten beibehalten. 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 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-Spalten indizieren, indem Sie speicheroptimierte Indizes verwenden. 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.

Das folgende Beispiel zeigt eine nativ kompilierte Prozedur, die mehrere JSON-Funktionen verwendet: JSON_VALUE, , OPENJSONund 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: