Query Recursiva

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.

 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:

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:

 

 

• 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.

Comments

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

• Anonymous
  August 23, 2011
  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,

• Anonymous
  August 23, 2011
  Fabricio Catae, Já resolvi, coloquei dentro de uma procedure e foi... Obrigado,

• Anonymous
  April 04, 2012
  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

• Anonymous
  April 04, 2012
  The comment has been removed

• Anonymous
  July 19, 2012
  Muito bom. Estou curioso para ver efeitos de UPDATE recursivo. ;)

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

• Anonymous
  November 19, 2013
  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.

• Anonymous
  November 21, 2013
  Olá Leandro, gostei da dúvida e fiquei curioso. Poderia compartilhar o seu exemplo com mais detalhes? Abraços, Fabricio

• Anonymous
  September 01, 2014
  Obrigado pela ajuda! Estava procurando uma solução como essa! Parabéns!

• Anonymous
  September 04, 2014
  Obrigado pela ajuda, foram excelentes, tanto a explicação quanto o exemplo, simples e rápido para entender. Abraços

• Anonymous
  November 17, 2017
  Catae, bom dia.Gostei do post, parabéns, simples e direto.