Blog – Fabiano Neves Amorim

Lazy Spool

Continuando a falar sobre os operadores de Spool, o próximo da lista é o Lazy Spool.

Para melhor entendimento do assunto, caso não tenha lido os outros posts onde falei sobre Spool, recomendo que leia.

Operador do dia – Spool

Operador do dia – Eager Spool e “Halloween Problem”

O Lazy Spool é bem parecido com o Eager Spool, porém a grande diferença entre eles é que o Lazy lê os dados conforme as linhas são requisitadas, a cada acesso ao operador, ele lê apenas uma linha, diferentemente do Eager que logo no primeiro acesso lê todos os dados.

Para ilustrar o uso do Lazy Spool criei uma tabela chamada Pedidos. Segue o script para criação e carga dos dados:

IF OBJECT_ID(‘Pedido’) IS NOT NULL

  DROP TABLE Pedido

GO

 

CREATE TABLE Pedido (ID         Int IDENTITY(1,1) PRIMARY KEY,

                     Cliente    Int NOT NULL,

                     Vendedor   VarChar(30) NOT NULL,

                     Quantidade SmallInt NOT NULL,

                     Valor      Numeric(18,2) NOT NULL,

                     Data       DateTime NOT NULL)

 

DECLARE @I SmallInt

SET @I = 0

 

WHILE @I < 50

BEGIN

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT ABS(CheckSUM(NEWID()) / 100000000),

         ‘Fabiano’,

         ABS(CheckSUM(NEWID()) / 10000000),

         ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),

         GetDate() – (CheckSUM(NEWID()) / 1000000)

 

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT ABS(CheckSUM(NEWID()) / 100000000),

         ‘Amorim’,

         ABS(CheckSUM(NEWID()) / 10000000),

         ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),

         GetDate() – (CheckSUM(NEWID()) / 1000000)

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT ABS(CheckSUM(NEWID()) / 100000000),

         ‘Coragem’,

         ABS(CheckSUM(NEWID()) / 10000000),

         ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),

         GetDate() – (CheckSUM(NEWID()) / 1000000)

  SET @I = @I + 1 

END

SET @I = 1

WHILE @I < 3

BEGIN

  INSERT INTO Pedido(Cliente, Vendedor, Quantidade, Valor, Data)

  SELECT Cliente, Vendedor, Quantidade, Valor, Data

    FROM Pedido

  SET @I = @I + 1 

END

GO

 

Exemplo dos dados da tabela:

Para simular o uso do Lazy Spool, criei uma consulta que retorna todos os pedidos que tiveram venda maior que a média de venda,

comparado a todas as vendas para o mesmo cliente.

SELECT Ped1.Cliente, Ped1.Valor

  FROM Pedido Ped1

 WHERE Ped1.Valor < (SELECT AVG(Ped2.Valor)

                       FROM Pedido Ped2

                      WHERE Ped2.Cliente = Ped1.Cliente)

 

Antes de ver o plano vamos entender melhor a consulta.

A SubConsulta retorna a média de venda (AVG(Ped2.Valor)) de um determinado cliente da tabela do from(Ped1.Cliente), depois a consulta principal, pega esta média, e retorna as vendas que tiveram o valor maior que ela.

O plano de execução é o seguinte:

 

  |–Nested Loops(Inner Join)

       |–Table Spool

       |    |–Segment

       |         |–Sort(ORDER BY:([Ped1].[Cliente] ASC))

       |              |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Pedido].[PK__Pedido__6E01572D] AS [Ped1]))

       |–Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[Pedido].[Valor] as [Ped1].[Valor]<[Expr1004]))

            |–Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013]/CONVERT_IMPLICIT(numeric(19,0),[Expr1012],0) END))

            |    |–Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1013]=SUM([tempdb].[dbo].[Pedido].[Valor] as [Ped1].[Valor])))

            |         |–Table Spool

            |–Table Spool

 

Podemos observar que o operador de Spool aparece 3 vezes no plano de execução, isso não significa que existem 3 tabelas temporárias, na verdade os 3 compartilham a mesma tabela temporária, isso pode ser observado analisando os hints exibidos pelos operadores.

Repare que o primeiro Spool tem o Node ID 2, e os outros operadores fazem referencia ao Primary Node 2.

Vou tentar detalhar este plano de execução o máximo possível, para que fique um pouco mais fácil de entender o que está acontecendo.

O primeiro passo do plano é ler os dados envolvidos na consulta e agrupá-los por cliente.

Primeiro o operador de clustered index scan está lendo os dados das colunas clientes e valor.

Input() do operador é:

Output() = Colunas Cliente e Valor.

Ao receber o Output() do clustered index scan, o operador de Sort ordena o resultado pela coluna cliente.

Input() = Output() anterior a ele(clustered index scan)

Output() = Dados ordenados por Cliente

O operador Segment quebra os dados em vários grupos, neste caso, ele recebe os dados ordenados por cliente, e quebra os dados em grupos que compartilham o mesmo cliente. O primeiro grupo serão os dados que compartilham o Cliente “0”. Como os dados estão ordenados por cliente, ele só precisa ir lendo as linhas até encontrar um valor diferente do atual(“0”). Quando isso acontece ele passa seu output() para o próximo operador, e espera o processo voltar até ele para que ele calcule o próximo grupo de dados até terminar sua leitura.

Input() = Output() anterior a ele(Sort)

Output() = Grupo de dados (primeiro grupo “Cliente = 0”)

Aqui vemos o Table Spool trabalhando como Lazy, ou seja, lê os dados conforme requisição. Ele cria uma tabela temporária no tempdb armazenando os dados que contem o mesmo grupo de Cliente.

Input() = Resultado do Segment

Output() = Tabela com os dados armazenados no tempdb

Faz o join entre a tabela do from com a tabela do join. Neste caso, para cada linha do Table Spool faz um scan nas linhas do próximo join retornando as linhas que satisfaçam o join.

Vamos passar para o segundo passo do plano. Onde o SQL vai calcula a média de venda de um determinado cliente.

Lê os dados atuais do Spool, e passa o resultado para o aggregate calcular a média. Lembrando que os registros do Spool são exatamente o grupo de clientes “0”.

O Stream Aggregate lê os dados do Spool e calcula a média da coluna valor, retornando apenas 1 registro como parâmetro de saída.

Converte o resultado da aggregação no tipo Numeric para fazer o join com o valor do Spool.

Faz o join entre a tabela do from com a tabela do join. Neste caso, para cada valor computado, faz um scan no table spool retornando as linhas que satisfaçam o join, ou seja, onde o Valor do Table Spool for maior que o valor retornado pela agregação.

O ultimo table spool é utilizado pelo Nested Loops para fazer o join com o valor do stream aggregate.

Ao terminar este processo o operador de segment é executado novamente, desta vez repetindo todo o processo passando para o spool o próximo grupo de dados.

Ao utilizar o Spool o SQL Server só precisa ler a tabela Pedidos uma única vez, todos os acessos posteriores a primeira leitura são efetuados no spool, que por sua vez só guarda os valores de um segmento por vez.

Vou tentar ilustrar o que aconteceu aqui:

1 – O Nested Loop lê a primeira linha no Table Spool. Faz um scan com todos os clientes que resultaram do 2 Nested Loop, que irá retornar as linhas onde o valor é maior que a média.

2 – Executa o segundo join. Verifica quais as linhas que tem o valor maior que a média.

3 – Calcula a média para o cliente atual(0).

Para o resultado da média, verifica quais as linhas que satisfaçam o join. Por ex: 1275.91 é maior que 362.78? Caso seja verdadeiro então retorna o Cliente e o Valor, senão passa para o próximo registro do table spool. Repete este processo até terminar a leitura de todos os registros do spool.

4 – Faz um Join com o resultado do 2 Nested Loop.

5 – Chama o segment que limpa o Spool e passa para o próximo grupo de dados, iniciando novamente o processo 1.

Pessoal, vimos o uso do Lazy Spool para trabalhar com joins, evitando acessar uma tabela várias vezes, otimizando o processo de execução da consulta.

Ainda falta vermos os RowCount Spool e o Nonclustered Index Spool. Fique de olho.

Abraço.