Otimizar o processamento JSON com o OLTP in-memory

Aplica-se a:yes SQL Server 2017 (14.x) e posterior YesBanco de Dados 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);

Otimizar o processamento JSON com recursos adicionais na memória

Os recursos disponíveis no SQL Server e no Banco de Dados SQL do Azure permitem integrar por completo as funcionalidades do JSON às tecnologias existentes do OLTP in-memory. Por exemplo, você pode fazer o seguinte:

Validar colunas JSON

O SQL Server e o Banco de Dados SQL do Azure permitem adicionar restrições CHECK compiladas nativamente que validam o conteúdo de documentos JSON armazenados em uma coluna de cadeia de caracteres. Com as restrições CHECK JSON compiladas nativamente, é possível 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);

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

) WITH (MEMORY_OPTIMIZED=ON);

Valores de índice em colunas JSON

O SQL Server e o Banco de Dados SQL do Azure permitem 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 compilado nativamente 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
		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

Saiba mais sobre JSON no SQL Server e no Banco de Dados SQL do Azure

Vídeos da Microsoft

Observação

Alguns dos links de vídeo nesta seção podem não funcionar no momento. A Microsoft está migrando conteúdo anteriormente no Canal 9 para uma nova plataforma. Atualizaremos os links à medida que os vídeos forem migrados para a nova plataforma.

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: