Optimización del procesamiento de OLTP en memoria JSON

  • Artículo

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance

SQL Server y Azure SQL Database permiten trabajar con texto en formato JSON. Para aumentar el rendimiento de las consultas que procesan datos JSON, puede almacenar documentos JSON en tablas optimizadas para memoria mediante las columnas de cadena estándar (tipo nvarchar). Al almacenar datos JSON en tablas optimizadas para memoria, se aumenta el rendimiento de consulta gracias a que se utiliza el acceso a los datos en memoria sin bloqueo.

Almacenamiento de datos JSON en tablas optimizadas para memoria

En el ejemplo siguiente se crea una tabla Product optimizada para memoria con dos columnas: Tags y 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

Optimización de procesamiento JSON con características en memoria adicionales

Puede integrar completamente la funcionalidad JSON con las tecnologías OLTP existentes en memoria. Por ejemplo, puede realizar las siguientes tareas:

Validación de columnas JSON

Puede añadir restricciones CHECK compiladas de forma nativa que validen el contenido de los documentos JSON almacenados en una columna de cadena para asegurarse de que el texto JSON almacenado en sus tablas optimizadas para memoria tiene el formato correcto.

En el ejemplo siguiente, se crea una tabla Product con una columna JSON Tags. La columna Tags tiene una restricción CHECK que usa la función ISJSON para validar el texto JSON en la columna.

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

También puede agregar la restricción CHECK compilada de forma nativa a una tabla existente que contiene columnas JSON.

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

Exposición de valores JSON mediante columnas calculadas

Las columnas calculadas permiten exponer valores del texto JSON y obtener acceso a esos valores sin capturar el valor del texto JSON y sin analizar nuevamente la estructura JSON. Los valores expuestos de esta manera están fuertemente tipados y persisten físicamente en las columnas calculadas. Acceder a los valores JSON mediante columnas calculadas persistentes es más rápido que hacerlo directamente a los valores del documento JSON.

En el ejemplo siguiente se muestra cómo exponer los dos valores siguientes de la columna Data JSON:

  • El país/región donde se fabricó un producto.
  • El costo de fabricación del producto.

En este ejemplo, las columnas calculadas MadeIn y Cost se actualizan cada vez que cambia el documento JSON almacenado en la columna Data.

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

Indexación de valores en las columnas JSON

Puede indexar los valores de las columnas JSON con índices optimizados para memoria. Los valores JSON que se indexan se deben exponer y tipar fuertemente mediante el uso de columnas calculadas, tal como se describe en el ejemplo anterior.

Los valores de las columnas JSON se pueden indexar con los índices NONCLUSTERED y HASH estándar.

  • Los índices NONCLUSTERED optimizan las consultas que seleccionan rangos de filas por algún valor JSON u ordenan los resultados por valores JSON.
  • Los índices HASH optimizan las consultas que seleccionan una sola fila o algunas filas mediante la especificación de un valor exacto que se debe buscar.

En el ejemplo siguiente se crea una tabla que expone valores JSON mediante el uso de dos columnas calculadas. En el ejemplo, se crea un índice NONCLUSTERED en uno de los valores JSON y un índice HASH en el otro.

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

Compilación nativa de consultas JSON

Si los procedimientos, las funciones y los desencadenadores contienen consultas que usan las funciones JSON integradas, la compilación nativa aumenta el rendimiento de estas consultas y disminuye los ciclos de CPU que se requieren para ejecutarlos.

En el ejemplo siguiente, se muestra un procedimiento compilado de forma nativa que usa varias funciones JSON: JSON_VALUE, OPENJSON y 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

Pasos siguientes

Para obtener una introducción visual a la compatibilidad integrada de JSON en SQL Server y Azure SQL Database, vea los siguientes vídeos: