インメモリ OLTP を使用した JSON の処理の最適化

適用対象: SQL Server 2017 (14.x) 以降 Azure SQL データベース Azure SQL Managed Instance

SQL Server と Azure SQL Database では、JSON 形式のテキストを使用できます。 JSON データを処理するクエリのパフォーマンスを上げるには、標準の文字列型の列 (nvarchar 型) を使用してメモリ最適化テーブルに JSON ドキュメントを格納します。 JSON データをメモリ最適化テーブルに格納すると、ロックが発生しない、インメモリ データ アクセスによりクエリのパフォーマンスが向上します。

メモリ最適化テーブルへの JSON の格納

次に、Tags と Data という 2 つの JSON 列があるメモリ最適化 Product テーブルの例を示します。

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 処理の最適化

JSON 機能を既存のインメモリ OLTP テクノロジと完全に統合できます。 たとえば、次を実行できます。

• ネイティブ コンパイルの CHECK 制約を使用して、メモリ最適化テーブルに格納された JSON ドキュメントの構造を検証できます。
• 計算列を使用して JSON ドキュメントに格納された値を公開し、型を厳密に指定できます。
• メモリ最適化インデックスを使用して JSON ドキュメントの値にインデックスを作成できます。
• JSON ドキュメントの値を使用する SQL クエリをネイティブでコンパイルしたり、結果を JSON テキストとして書式設定したりできます。

JSON の列の検証

メモリ最適化テーブルに格納されている JSON テキストの書式が正しいことを保証するために、文字列列に格納されたJSON ドキュメントの内容を検証する、ネイティブ コンパイルされた JSON の CHECK 制約を追加できます。

次の例では、JSON 列 Tags を含む Product テーブルを作成します。 Tags 列には、ISJSON 関数を使用して列の JSON テキストを検証する、CHECK 制約が設定されています。

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

ネイティブ コンパイルの CHECK 制約は、JSON の列がある既存のテーブルに追加することもできます。

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

計算列を使用した JSON 値の公開

計算列では、JSON テキストの値が公開されます。JSON テキストから値を再度取得したり、JSON の構造を再度解析したりすることなくそれらの値にアクセスできます。 このようにして公開される値の型は厳密に指定され、計算列に物理的に保存されます。 保存される計算列を使用した JSON 値へのアクセスは、JSON ドキュメント内の値に直接アクセスするよりも高速です。

次の例では、JSON の Data 列から次の 2 つの値を公開する方法を示します。

• 製品の製造国/地域。
• 製品の製造コスト。

この例では、計算列の MadeIn と Cost は、Data 列に格納された JSON ドキュメントが変更されるたびに更新されます。

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

JSON の列のインデックス値

メモリ最適化インデックスを使用して JSON 列の値にインデックスを作成できます。 インデックスが作成されている JSON 値は、次の例のように、計算列を使用して公開され、型が厳密に指定されている必要があります。

JSON の列の値には、標準の非クラスター化インデックスとハッシュ インデックスの両方を使用してインデックスを作成できます。

• 非クラスター化インデックスは、いくつかの JSON 値による行範囲の選択または JSON 値による結果の並べ替えを行うクエリを最適化します。
• ハッシュ インデックスは、検索対象の正確な値を指定することによって、1 行または少数の行を選択するクエリを最適化します。

次の例では、2 つの計算列を使用して JSON 値を公開するテーブルを構築します。 例では、1 つの JSON 値に非クラスター化インデックスを作成し、もう 1 つの値にハッシュ インデックスを作成します。

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

JSON クエリのネイティブ コンパイル

プロシージャ、関数、およびトリガーに組み込み JSON 関数を使用するクエリが含まれている場合は、ネイティブ コンパイルによって、それらのクエリのパフォーマンスが向上し、クエリの実行に必要な CPU サイクルが減少します。

次の例では、いくつかの JSON 関数 (JSON_VALUE、OPENJSON、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

次のステップ

SQL Server と Azure SQL Database に組み込まれている JSON のサポートの視覚的な紹介は、次のビデオをご覧ください。

• NoSQL とリレーショナル環境間の架け橋としての JSON