Otimizar o processamento JSON com o OLTP in-memory
Aplica-se a:
SQL Server 2017 (14.x) e posterior Banco 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 a estrutura de documentos JSON armazenados em tabelas com otimização de memória usando restrições CHECK compiladas nativamente.
- Expor e tipar fortemente os valores armazenados em documentos JSON usando colunas computadas.
- Indexar valores em documentos JSON usando índices com otimização de memória.
- Compilar nativamente consultas SQL que usam valores de documentos JSON ou formatar os resultados como um texto JSON.
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: