Otimizar o processamento JSON com o OLTP in-memory

  • Artigo

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

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

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

O exemplo a seguir mostra uma tabela do 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

Otimizar o processamento JSON com recursos adicionais na memória

Você pode integrar totalmente a funcionalidade JSON com as tecnologias OLTP in-memory existentes. Por exemplo, você pode fazer o seguinte:

Validar colunas JSON

Você pode adicionar restrições CHECK em compilação nativa que validam o conteúdo dos documentos JSON armazenado em uma coluna de cadeia de caracteres, para garantir que o texto JSON armazenado nas tabelas com otimização de memória está formatado corretamente.

O exemplo a seguir cria uma tabela Product com uma coluna JSON Tags. A coluna Tags tem uma restrição CHECK que utiliza 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

Também é possível adicionar a restrição CHECK compilada nativamente a tabelas existentes 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

Colunas computadas permitem expor valores do texto JSON e acessar esses valores sem buscar o valor do texto JSON e sem analisar a estrutura JSON novamente. Os valores expostos dessa maneira são fortemente tipados e fisicamente persistentes nas colunas computadas. O acesso a valores JSON com colunas computadas persistentes é mais rápido do que o acesso a valores diretamente no documento JSON.

O seguinte exemplo mostra como expor estes dois valores por meio da coluna JSON Data:

  • O país/região no qual um produto foi 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 com otimização de memória. Os valores JSON indexados devem ser expostos e fortemente tipados com colunas computadas, conforme mostrado no exemplo anterior.

Os valores em colunas JSON podem ser indexados com índices NONCLUSTERED e HASH padrão.

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

O exemplo a seguir cria uma tabela que expõe valores JSON com 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 os procedimentos, funções e gatilhos contêm consultas que usam funções JSON internas, a compilação nativa aumentará o desempenho dessas consultas e reduzirá os ciclos de CPU necessários para executá-los.

O exemplo a seguir mostra um procedimento em compilação nativa que usa várias funções JSON: JSON_VALUE, OPENJSON e 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

Próximas etapas

Para obter uma introdução visual ao suporte interno para JSON no SQL Server e no Banco de Dados SQL do Azure, consulte os seguintes vídeos: