Partilhar via

Otimize o processamento JSON com OLTP na memória

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceSQL database no Microsoft Fabric

O SQL Server e o Banco de Dados SQL do Azure permitem que você trabalhe com texto formatado como JSON. Para aumentar o desempenho de consultas que processam dados JSON, você pode armazenar documentos JSON em tabelas com otimização de memória usando colunas de cadeia de caracteres padrão (tipo nvarchar). O armazenamento de dados JSON em tabelas com otimização de memória aumenta o desempenho da consulta usando acesso a dados na memória sem bloqueio.

Armazene JSON em tabelas com otimização de memória

O exemplo a seguir mostra uma tabela Product com otimização de memória com duas colunas JSON, Tags e 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

Otimize o processamento JSON com recursos adicionais na memória

Você pode integrar totalmente a funcionalidade JSON com as tecnologias OLTP existentes na memória. Por exemplo, você pode fazer o seguinte:

Validar colunas JSON

Você pode adicionar restrições CHECK compiladas nativamente que validam o conteúdo de documentos JSON armazenados em uma coluna de cadeia de caracteres, para garantir que o texto JSON armazenado em suas tabelas com otimização de memória seja formatado corretamente.

O exemplo a seguir cria uma tabela Product com uma coluna JSON Tags. A coluna Tags tem uma restrição CHECK que usa a função ISJSON para validar o texto JSON na coluna.

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

Você também pode adicionar a restrição CHECK compilada nativamente a uma tabela existente que contém colunas JSON.

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

Expor valores JSON usando colunas computadas

As colunas computadas permitem expor valores do texto JSON e acessar esses valores sem buscar o valor do texto JSON novamente e sem analisar a estrutura JSON novamente. Os valores expostos desta forma são tipados com rigor e armazenados fisicamente nas colunas computadas. Acessar valores JSON usando colunas computadas persistentes é mais rápido do que acessar valores no documento JSON diretamente.

O exemplo a seguir mostra como expor os dois valores a seguir da coluna JSON Data:

  • O país/região onde um produto é fabricado.
  • O custo de fabricação do produto.

Neste exemplo, as colunas computadas MadeIn e Cost são atualizadas sempre que o documento JSON armazenado na coluna Data é alterado.

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

Valores de índice em colunas JSON

Você pode indexar valores em colunas JSON usando índices otimizados para memória. Os valores JSON indexados devem ser expostos e fortemente tipados usando colunas computadas, conforme descrito no exemplo anterior.

Os valores nas colunas JSON podem ser indexados usando os índices padrão NONCLUSTERED e HASH.

  • Os índices NONCLUSTERED otimizam consultas que selecionam intervalos de linhas por algum valor JSON ou classificam os resultados por valores JSON.
  • Os índices HASH otimizam consultas que selecionam uma única linha ou algumas linhas especificando um valor exato para localizar.

O exemplo a seguir cria uma tabela que expõe valores JSON usando duas colunas computadas. O exemplo cria um índice NONCLUSTERED em um valor JSON e um índice HASH no outro.

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

Compilação nativa de consultas JSON

Se seus procedimentos, funções e gatilhos contiverem consultas que usam as funções JSON internas, a compilação nativa aumenta o desempenho dessas consultas e reduz os ciclos de CPU necessários para executá-las.

O exemplo a seguir mostra um procedimento compilado nativamente que usa várias funções JSON: JSON_VALUE, OPENJSONe 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

Conteúdo relacionado

Para obter uma introdução visual ao suporte JSON integrado, consulte os seguintes vídeos:

  • Last updated on