Query Recursiva

Query Recursiva

  • Comments 9

Você sabia que o SQL Server consegue criar uma Query Recursiva? Utilizamos, como exemplo, uma tabela que armazena as informações de MENU de uma página Web.

image

CREATE TABLE tbMenu
    (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    idPai INT NULL, 
    Nome VARCHAR(30) NOT NULL
    )

INSERT tbMenu (idPai,Nome) VALUES 
(NULL,'Menu'),(1,'Vestuario'),(1,'Brinquedo'),(1,'Informatica'),
(2,'Terno'),(2,'Casaco'),(2,'Sapato'),(2,'Meia'),(3,'Carrinho'),
(3,'Boneca'),(4,'Netbook'),(4,'Webcam'),(4,'Desktop')
            
SELECT * FROM tbMenu

Conhecendo a relação entre os itens do MENU, como podemos obter uma visualização de forma hierárquica? Ou como criar uma consulta que verifica que o produto Webcam, da categoria Informática, está dentro do Menu Principal?

 

Conceito

A idéia da query recursiva é montar o resultado por níveis, determinando quem são os registros “raízes”, depois os “descendentes de primeira ordem”, em seguida, “descendentes de segunda ordem”, e por aí vai.

  1. Obter todos os registros de nível 1 – Esses são chamados de registros âncora (anchors)
  2. Com base nos níveis âncoras, selecionar todos os registros do nível 2 (processo recursivo)
  3. Com base nos registros de nível 2, selecionar todos os registros do nível 3
  4. … e assim por diante…

Resultado:

query recursiva usando cte

Sintaxe

A sintaxe utilizada para query recursiva é feita através do auxílio das Common Table Expression (CTE). Sempre utiliza-se uma query inicial determinada de âncora, que contém os registros raízes. Em seguida, os resultados são combinados através de uma operação de UNION ALL com a “query recursiva”: ela possui uma auto-referência.

WITH cteMenuNivel(id,Nome,Nivel,NomeCompleto)
AS
(
    -- Ancora
    SELECT id,Nome,1 AS 'Nivel',CAST(Nome AS VARCHAR(255)) AS 'NomeCompleto' 
    FROM tbMenu WHERE idPai IS NULL
    
    UNION ALL
    
    -- Parte RECURSIVA
    SELECT 
        m.id,m.Nome,c.Nivel + 1 AS 'Nivel',
        CAST((c.NomeCompleto + '/' + m.Nome) AS VARCHAR(255)) 'NomeCompleto' 
    FROM tbMenu m INNER JOIN cteMenuNivel c ON m.idPai = c.id
    
)
SELECT Nivel,NomeCompleto FROM cteMenuNivel

 

Performance

O desempenho de query recursiva usando Common Table Expression é excelente para tabela com pouco volume de dados, mas há uma degradação perceptível quando usada em tabelas críticas de sistema e com mais de 10000 registros (comprovado em cliente!). A explicação para esse fato é feita através do plano de execução:

 

image

 

  • Passo 1: Os resultados da query “âncora” são obtidos a partir de uma operação de leitura da tabela
  • Passo 2: Os operadores de “Compute Scalar” realizam os cálculos e transformações de tipo de dados
  • Passo 3 e 4: O resultado parcial é armazenado em uma Tabela Temporária (Index Spool), que  serve como fonte de dados para a recursão (Passo 4)
  • Passo 5: Como parte do processo recursivo, as informações da tabela são lidas novamente
  • Passo 6: Os novos níveis são identificados através da operação de JOIN entre tabelas. Nesse caso, a operação realizada foi de NESTED LOOP
  • Passo 7: Os registros do nível seguinte são armazenados na Tabela Temporária e o processo volta ao Passo 4
  • Quando não forem produzidos mais registros, o processo termina.

Quanto maior o número de passos executados no processo recursivo, maior o impacto na performance da query. Se fosse usada uma tabela com 1 milhão de registros, porém, houvesse poucas repetições dos passo 4-7, o desempenho seria ótimo.

Portanto, o problema não é exatamente o tamanho da tabela ou sua criticidade, mas a quantidade de registros retornados e o número de passos executados.

  • Somente para informação, SQL Recursivo também é utilizado para 'paginação' de dados e é um método excelente com eficácia comprovada.

  • Olá Fabricio Catae,

    Eu estou usando seu exemplo e queria saber se ali no ancora na condicão

    where = WHERE idPai IS NULL

    Eu consigo passar como parametro um codigo, ficaria WHERE idPai = @idPai

    Att,

  • Fabricio Catae,

    Já resolvi, coloquei dentro de uma procedure e foi...

    Obrigado,

  • Novelo de linha :  -- Ache a ponta.

    A tabela abaixo possui 100 jogos, o exemplo é o registro 1.

    Jogo Casa Coluna1 Coluna2 Coluna3

    1 1 X

    1 2 1 2 X

    1 3 1

    1 4 1 X

    1 5 1

    1 6 X 2

    1 7 1 X

    1 8 1

    1 9 1

    1 10 1 2 X

    1 11 1

    1 12 X

    1 13 1

    1 14 1 X

    1 15 1

    Usando SQL gere todas as possibilidades existente entre as colunas 1, 2 e 3.

    Este é um exemplo pratico de utilizacao de query recurssive aplicada a loteria dos esportes (loteca).

    O resultado deverá ser 144 aposta de 15 elemento pois deve ser basear na coluna CASA.

    primeira linha == X1111x11111x111

    ultima linha ==   Xx1x12x11x1x1x1

  • cOLL

    Your comment is awaiting moderation.

    CREATE TABLE [dbo].[Jogo](

    [Jogo] [float] NULL,

    [Casa] [float] NULL,

    [Coluna1] [nvarchar](3) NULL,

    [Coluna2] [nvarchar](3) NULL,

    [Coluna3] [nvarchar](3) NULL

    ) ON [PRIMARY]

    GO

    Jogo,Casa,Coluna1,Coluna2,Coluna3

    255,1,C1:,NULL,NULL

    255,2,A1:,B1:,C1:

    255,3,A1:,NULL,NULL

    255,4,A1:,C1:,NULL

    255,5,A1:,NULL,NULL

    255,6,C1:,B1:,NULL

    255,7,A1:,C1:,NULL

    255,8,A1:,NULL,NULL

    255,9,A1:,NULL,NULL

    255,10,A1:,B1:,C1:

    255,11,A1:,NULL,NULL

    255,12,C1:,NULL,NULL

    255,13,A1:,NULL,NULL

    255,14,A1:,C1:,NULL

    255,15,A1:,NULL,NULL

    Jogo Casa Coluna1 Coluna2 Coluna3

    255 1 C1: NULL NULL

    255 2 A1: B1: C1:

    255 3 A1: NULL NULL

    255 4 A1: C1: NULL

    255 5 A1: NULL NULL

    255 6 C1: B1: NULL

    255 7 A1: C1: NULL

    255 8 A1: NULL NULL

    255 9 A1: NULL NULL

    255 10 A1: B1: C1:

    255 11 A1: NULL NULL

    255 12 C1: NULL NULL

    255 13 A1: NULL NULL

    255 14 A1: C1: NULL

    255 15 A1: NULL NULL

    Using SQL generate all possible between the columns 1, 2 and 3.

    This is an example of practical utilization of query recurssive applied to sports lottery (loteca).

    The result should be 144 bet as element 15 in the column should be based on CASA.

    FIRST LINE == B1:A1:A1:A1:A1:B1:A1:A1:A1:A1:A1:B1:A1:A1:A1:

    LAST LINE == B1:B1:A1:B1:A1:CA1::B1:A1:A1:B1:A1:B1:A1:B1:A1:

  • Muito bom.

    Estou curioso para ver efeitos de UPDATE recursivo. ;)

  • Ficou gigante o plano de execução, olhando a simplicidade da query nem parece que internamente tem todos aqueles passos, análises

  • Fabricio,

    Estou passando por uma dificuldade fazendo uma consulta na tabela de tarefas do project server 2013. Onde preciso montar a hierarquia de tarefas.

    Aplicando a dúvida ao seu exemplo, como eu faria para buscar somente o item Meia de nível 3 e toda a sua estrutura acima?

    Obrigado.

    Leandro Silva.

  • Olá Leandro, gostei da dúvida e fiquei curioso. Poderia compartilhar o seu exemplo com mais detalhes?

    Abraços, Fabricio

Page 1 of 1 (9 items)
Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post