Mengoptimalkan pemrosesan JSON dengan OLTP dalam memori

Artikel
08/23/2023

Berlaku untuk: SQL Server 2017 (14.x) dan azure SQL Database Azure SQL Managed Instance yang lebih baru

SQL Server dan Azure SQL Database memungkinkan Anda bekerja dengan teks yang diformat sebagai JSON. Untuk meningkatkan performa kueri yang memproses data JSON, Anda dapat menyimpan dokumen JSON dalam tabel yang dioptimalkan memori menggunakan kolom string standar (jenis nvarchar ). Menyimpan data JSON dalam tabel yang dioptimalkan memori meningkatkan performa kueri dengan menggunakan akses data dalam memori bebas kunci.

Menyimpan JSON dalam tabel yang dioptimalkan memori

Contoh berikut menunjukkan tabel yang dioptimalkan Product memori dengan dua kolom JSON, Tags dan 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

Mengoptimalkan pemrosesan JSON dengan fitur dalam memori tambahan

Anda dapat sepenuhnya mengintegrasikan fungsionalitas JSON dengan teknologi OLTP dalam memori yang ada. Misalnya, Anda dapat melakukan hal-hal berikut:

Validasi struktur dokumen JSON yang disimpan dalam tabel yang dioptimalkan memori dengan menggunakan batasan CHECK yang dikompilasi secara asli.
Mengekspos dan mengetikkan nilai yang disimpan dalam dokumen JSON dengan menggunakan kolom komputasi.
Nilai indeks dalam dokumen JSON dengan menggunakan indeks yang dioptimalkan memori.
Mengkompilasi kueri SQL secara asli yang menggunakan nilai dari dokumen JSON atau yang memformat hasil sebagai teks JSON.

Memvalidasi kolom JSON

Anda dapat menambahkan batasan CHECK yang dikompilasi secara asli yang memvalidasi konten dokumen JSON yang disimpan dalam kolom string, untuk memastikan bahwa teks JSON yang disimpan dalam tabel yang dioptimalkan memori Anda diformat dengan benar.

Contoh berikut membuat Product tabel dengan kolom TagsJSON . Kolom Tags memiliki batasan CHECK yang menggunakan ISJSON fungsi untuk memvalidasi teks JSON di kolom.

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

Anda juga dapat menambahkan batasan CHECK yang dikompilasi secara asli ke tabel yang sudah ada yang berisi kolom JSON.

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

Mengekspos nilai JSON menggunakan kolom komputasi

Kolom komputasi memungkinkan Anda mengekspos nilai dari teks JSON dan mengakses nilai tersebut tanpa mengambil nilai dari teks JSON lagi dan tanpa mengurai struktur JSON lagi. Nilai yang diekspos dengan cara ini ditik dengan kuat dan bertahan secara fisik di kolom komputasi. Mengakses nilai JSON menggunakan kolom komputasi yang dipertahankan lebih cepat daripada mengakses nilai dalam dokumen JSON secara langsung.

Contoh berikut menunjukkan cara mengekspos dua nilai berikut dari kolom JSON Data :

Negara/wilayah tempat produk dibuat.
Biaya manufaktur produk.

Dalam contoh ini, kolom MadeIn komputasi dan Cost diperbarui setiap kali dokumen JSON disimpan dalam Data kolom berubah.

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

Nilai indeks dalam kolom JSON

Anda dapat mengindeks nilai di kolom JSON dengan menggunakan indeks yang dioptimalkan memori. Nilai JSON yang diindeks harus diekspos dan ditik dengan kuat dengan menggunakan kolom komputasi, seperti yang dijelaskan dalam contoh sebelumnya.

Nilai dalam kolom JSON dapat diindeks dengan menggunakan indeks NONCLUSTERED dan HASH standar.

Indeks NONCLUSTERED mengoptimalkan kueri yang memilih rentang baris berdasarkan beberapa nilai JSON atau mengurutkan hasil berdasarkan nilai JSON.
Indeks HASH mengoptimalkan kueri yang memilih satu baris atau beberapa baris dengan menentukan nilai yang tepat untuk ditemukan.

Contoh berikut membangun tabel yang mengekspos nilai JSON dengan menggunakan dua kolom komputasi. Contoh membuat indeks NONCLUSTERED pada satu nilai JSON dan indeks HASH di sisi lain.

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

Kompilasi asli kueri JSON

Jika prosedur, fungsi, dan pemicu Anda berisi kueri yang menggunakan fungsi JSON bawaan, kompilasi asli meningkatkan performa kueri ini dan mengurangi siklus CPU yang diperlukan untuk menjalankannya.

Contoh berikut menunjukkan prosedur yang dikompilasi secara asli yang menggunakan beberapa fungsi JSON: JSON_VALUE, , OPENJSONdan 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

Langkah berikutnya

Untuk pengenalan visual dukungan JSON bawaan di SQL Server dan Azure SQL Database, lihat video berikut ini:

JSON sebagai jembatan antara NoSQL dan dunia relasional

Bagikan melalui