Início / dba / Perguntas / 199717
Accepted
VansFannel
Asked: 2018-03-09 05:45:26 +0800 CST

Gerar array aninhado com OPENJSON muito lento

  • 772

Acabei de começar a usar OPENJSONcom o SQL Server 2016 SP1.

Eu tenho esta frase:

select c.Serial as Parent,
    (Select co.Serial, agc.Position
      from AggregationChildren agc, Aggregation ag, Code co
      where agc.AggregationId = a.AggregationId 
      and co.CodeId = agc.AggregationChildrenId for json path) as children
    from Aggregation a, Code c
    where c.CodeId = a.AggregationId for json path

Para gerar este JSON:

{"Parent":"4244287599479491","Children":
[{"Serial":"8915753733724633","Position":"1"},
{"Serial":"1247782815710855","Position":"2"},
...]}

Mas é muito muito lento.

Meu problema é com o Childrenarray porque não sei como obtê-lo.

Existe uma maneira de fazer isso mais rápido?

Estas são as tabelas:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ... ],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    [ ... ]
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [ ... ], 
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

A Serialcoluna é uma, nvarchar(20)pois os valores podem ser qualquer combinação de alfanuméricos, embora meu exemplo mostre apenas números.

sql-server json

1 respostas

  1. Best Answer

    Tive dificuldade em analisar sua consulta, porém acredito que isso retorne o mesmo resultado e seja muito mais rápido:

    SELECT Parent = c.Serial
    , Children = (
        SELECT c.Serial 
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        FOR JSON PATH 
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
FOR JSON PATH;

    O plano para a consulta acima se parece com:

    insira a descrição da imagem aqui

    O plano para sua consulta se parece com:

    insira a descrição da imagem aqui

    Se adicionarmos o seguinte índice, podemos tornar a primeira variante ainda mais rápida:

    CREATE NONCLUSTERED INDEX IX_AggregationChildren_IX0
ON dbo.AggregationChildren (AggregationId)
INCLUDE (AggregationChildrenId,Position);

    Claramente, você precisaria avaliar isso em relação à sua carga de trabalho, no entanto.

    Eu criei uma configuração de exemplo completa minimamente viável para usar para testes:

    USE tempdb;

IF OBJECT_ID(N'dbo.AggregationChildren', N'U') IS NOT NULL 
DROP TABLE dbo.AggregationChildren;
IF OBJECT_ID(N'dbo.Aggregation', N'U') IS NOT NULL 
DROP TABLE dbo.Aggregation;
IF OBJECT_ID(N'dbo.Code', N'U') IS NOT NULL 
DROP TABLE dbo.Code;
GO

CREATE TABLE dbo.Code (
    CodeId int NOT NULL
        CONSTRAINT PK_CODE 
        PRIMARY KEY 
        CLUSTERED
    , Serial nvarchar(20) NOT NULL
);


CREATE TABLE dbo.Aggregation (
    AggregationId int NOT NULL
        CONSTRAINT PK_AGGREGATIONS 
        PRIMARY KEY 
        CLUSTERED
        CONSTRAINT FK_Aggregation_Code
        FOREIGN KEY (AggregationId)
        REFERENCES dbo.Code (CodeId)
)

CREATE TABLE dbo.AggregationChildren (
    AggregationChildrenId int NOT NULL
        CONSTRAINT PK_AGGREGATION_CHILDS 
        PRIMARY KEY 
        CLUSTERED
        CONSTRAINT FK_AggregationChildren_Code
        FOREIGN KEY (AggregationChildrenId)
        REFERENCES dbo.Code (CodeId)
    , AggregationId int NOT NULL
        CONSTRAINT FK_AggregationChildren_Aggregation
        FOREIGN KEY (AggregationId)
        REFERENCES dbo.Aggregation (AggregationId) 
        ON DELETE CASCADE
    , Position int NOT NULL
)

    Eu reformulei as cláusulas de restrição para mais gentis com meu cérebro; essencialmente, o código acima é o mesmo que o DDL em sua pergunta.

    Isso preenche as três tabelas com dados suficientes para fazer comparações significativas:

    ;WITH src AS 
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Code (CodeId, Serial)
SELECT s1.Val 
        + (s2.Val * 10)
        + (s3.Val * 100)
        + (s4.Val * 1000)
        + (s5.Val * 10000)
    , CONVERT(bigint, CRYPT_GEN_RANDOM(8))
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5


;WITH src AS 
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Aggregation (AggregationId)
SELECT s1.Val 
    + (s2.Val * 10)
    + (s3.Val * 100)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3;



;WITH src AS 
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.AggregationChildren (AggregationChildrenId, AggregationId, Position)
SELECT s1.Val 
        + (s2.Val * 10)
        + (s3.Val * 100)
        + (s4.Val * 1000)
        + (s5.Val * 10000)
    , s1.Val 
        + (s2.Val * 10)
        + (s3.Val * 100)
    , s1.Val 
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5;

    Estas são as contagens de linhas para cada tabela:

    ╔════════╦═════════════╦════════════════════════╗═══════════╗
║ Código ║ Agregação ║ AgregaçãoCrianças ║
╠════════╬═════════════╬═══════════════════════════════════════
║ 100000 ║ 1000 ║ 100000 ║
╚════════╩═════════════╩═══════════════════════════════════════

    Minha versão da consulta:

    SELECT Parent = c.Serial
    , Children = (
        SELECT c.Serial 
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        FOR JSON PATH 
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
FOR JSON PATH;

    Para comparar a saída de ambas as consultas, criei duas funções definidas pelo usuário, como em:

    CREATE FUNCTION dbo.fn_json_test_1()
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT Parent = c.Serial
            , Children = (
                SELECT c.Serial 
                    , cac.Position
                FROM dbo.Code cc
                    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
                WHERE cac.AggregationId = a.AggregationId
                FOR JSON PATH 
            )
        FROM dbo.Code c
            INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
        FOR JSON PATH
    );
END;
GO


GO
CREATE FUNCTION dbo.fn_json_test_2()
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT c.Serial as Parent,
            (Select co.Serial, agc.Position
              from AggregationChildren agc, Aggregation ag, Code co
              where agc.AggregationId = a.AggregationId 
              and co.CodeId = agc.AggregationChildrenId for json path) as children
        from Aggregation a, Code c
        where c.CodeId = a.AggregationId for json path
    );
END;
GO

    Agora, posso comparar a saída de ambas as consultas por meio de:

    DECLARE @res1 nvarchar(max) = dbo.fn_json_test_1();
DECLARE @res2 nvarchar(max) = dbo.fn_json_test_2();

SELECT CASE WHEN @res1 <> @res2 THEN 'mismatch' ELSE 'match' END;

    O resultado é:

    Os resultados NÃO coincidem. A saída da minha consulta contém menos nós filho do que sua consulta. Vou voltar à prancheta e simplificar o banco de testes para ver onde está a disparidade.

    O testbed simplificado consiste em 10 linhas na Codetabela, 2 linhas na Aggregationtabela (pai) e 8 linhas na AggregationChildrentabela (filho):

    ;WITH src AS 
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Code (CodeId, Serial)
SELECT s1.Val 
    , CONVERT(bigint, CRYPT_GEN_RANDOM(8))
FROM src s1


;WITH src AS 
(
    SELECT n.Val
    FROM (VALUES (0), (1)) n(Val)
)
INSERT INTO dbo.Aggregation (AggregationId)
SELECT s1.Val 
FROM src s1;



;WITH src AS 
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7)) n(Val)
)
INSERT INTO dbo.AggregationChildren (AggregationChildrenId, AggregationId, Position)
SELECT s1.Val + 2
    , s1.Val % 2
    , s1.Val 
FROM src s1;

    Contagens de linhas:

    SELECT Code = (SELECT COUNT(1) FROM dbo.Code)
    , Aggregation = (SELECT COUNT(1) FROM dbo.Aggregation)
    , AggregationChildren = (SELECT COUNT(1) FROM dbo.AggregationChildren)

    ╔══════╦═════════════╦════════════╗════════╗════════╗═══════
║ Código ║ Agregação ║ AgregaçãoCrianças ║
╠══════╬═════════════╬══════════════════════════════════════
║ 10 ║ 2 ║ 8 ║
╚══════╩═════════════╩══════════════════════════════════════

    O padrão previsto deve ser dois arrays json pai, cada um com 4 arrays filhos.

    Meus resultados:

    [
  {
    "Pai": "-5601362097731340301",
    "Crianças": [
      {
        "Serial": "-5601362097731340301",
        "Posição": 0
      },
      {
        "Serial": "-5601362097731340301",
        "Posição": 2
      },
      {
        "Serial": "-5601362097731340301",
        "Posição": 4
      },
      {
        "Serial": "-5601362097731340301",
        "Posição": 6
      }
    ]
  },
  {
    "Pai": "-8896860091721838065",
    "Crianças": [
      {
        "Serial": "-8896860091721838065",
        "Posição": 1
      },
      {
        "Serial": "-8896860091721838065",
        "Posição": 3
      },
      {
        "Serial": "-8896860091721838065",
        "Posição": 5
      },
      {
        "Serial": "-8896860091721838065",
        "Posição": 7
      }
    ]
  }

    Sua pergunta:

    [
  {
    "Pai": "-5601362097731340301",
    "Crianças": [
      {
        "Serial": "5802227619253639548",
        "Posição": 0
      },
      {
        "Serial": "5802227619253639548",
        "Posição": 0
      },
      {
        "Serial": "4504664379821512162",
        "Posição": 2
      },
      {
        "Serial": "4504664379821512162",
        "Posição": 2
      },
      {
        "Serial": "6561435639659176802",
        "Posição": 4
      },
      {
        "Serial": "6561435639659176802",
        "Posição": 4
      },
      {
        "Serial": "-7417083263182709739",
        "Posição": 6
      },
      {
        "Serial": "-7417083263182709739",
        "Posição": 6
      }
    ]
  },
  {
    "Pai": "-8896860091721838065",
    "Crianças": [
      {
        "Serial": "-7646118996434234523",
        "Posição": 1
      },
      {
        "Serial": "-7646118996434234523",
        "Posição": 1
      },
      {
        "Serial": "-6372739442099935942",
        "Posição": 3
      },
      {
        "Serial": "-6372739442099935942",
        "Posição": 3
      },
      {
        "Serial": "-882384147532911428",
        "Posição": 5
      },
      {
        "Serial": "-882384147532911428",
        "Posição": 5
      },
      {
        "Serial": "4293317573306886053",
        "Posição": 7
      },
      {
        "Serial": "4293317573306886053",
        "Posição": 7
      }
    ]
  }
]

    Sua consulta tem muitos filhos; minha consulta retorna o número previsto de filhos, e retorna os Positionvalores corretos, porém está retornando os Serialvalores incorretos.

    O "bug" na minha consulta aparece na consulta interna. A consulta incorreta é:

    SELECT c.Serial 
    , cac.Position
FROM dbo.Code cc
    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
WHERE cac.AggregationId = a.AggregationId
ORDER BY c.Serial
FOR JSON PATH

    A versão correta é:

    SELECT cc.Serial --changed "c." to "cc."
    , cac.Position
FROM dbo.Code cc
    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
WHERE cac.AggregationId = a.AggregationId
ORDER BY cc.CodeId --not a big deal, but different order for children in output
FOR JSON PATH

    A consulta corrigida agora se parece com:

    SELECT  Parent = c.Serial
    , Children = (
        SELECT cc.Serial 
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        ORDER BY cc.CodeId
        FOR JSON PATH 
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
ORDER BY c.Serial
FOR JSON PATH;

    E retorna o seguinte resultado:

    [
  {
    "Pai": "-195930341251513493",
    "Crianças": [
      {
        "Serial": "-6126601633786720400",
        "Posição": 1
      },
      {
        "Serial": "5216562173012877678",
        "Posição": 3
      },
      {
        "Serial": "-1992909345438478098",
        "Posição": 5
      },
      {
        "Serial": "8329388691987940194",
        "Posição": 7
      }
    ]
  },
  {
    "Pai": "8774608126018975726",
    "Crianças": [
      {
        "Serial": "-3380643917643646211",
        "Posição": 0
      },
      {
        "Serial": "-2042609074595538493",
        "Posição": 2
      },
      {
        "Serial": "7345460002653774160",
        "Posição": 4
      },
      {
        "Serial": "-2126530822210070443",
        "Posição": 6
      }
    ]
  }
]
    • 6

relate perguntas