Início / dba / Perguntas / 292746
Accepted
HeyJude
Asked: 2021-06-04 14:12:37 +0800 CST

Entendendo a relação entre um Spool de Tabela e uma subconsulta correlacionada

  • 772

Estou aprendendo sobre planos de execução e tenho uma dúvida sobre a relação entre um Spool de Tabela e uma subconsulta correlacionada (estou seguindo este tutorial , não se importe com os erros de digitação).

Criei a seguinte tabela:

CREATE TABLE student (
  ID INT IDENTITY(1, 1),
  CX_Name VARCHAR(50),
  CX_PhoneNum VARCHAR(50),
  CX_Address VARCHAR(MAX),
  CX_Credit INT
)

Em seguida, insira os valores:

INSERT INTO student
VALUES (
  'Alen',
  '9625788954',
  'London',
  500
) 
GO 100
 
INSERT INTO student
VALUES (
  'Frank',
  '962445785',
  'Germany',
  1400
)
GO 100

Em seguida, executou a seguinte consulta, que inclui uma subconsulta correlacionada:

SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)

O plano de execução é:

insira a descrição da imagem aqui

O tutorial explica (o negrito é meu):

O SQL Server Engine lê os dados da tabela primeiro, classifica os dados antes de dividi-los em segmentos e, em seguida, cria uma tabela temporária para armazenar os grupos de dados .

Na outra parte do plano de explicação, o SQL Server Engine lê o Spool de Tabela e calcula o valor médio de crédito para cada grupo usando o operador Stream Aggregate.

O último operador Table Spool lerá os dados agrupados e os unirá para recuperar os valores superiores ao valor médio.

Os três operadores Table Spool usarão a mesma tabela temporária criada na primeira vez.

Eu não entendo a frase em negrito, de duas maneiras:

  1. A subconsulta correlacionada deve ser executada novamente para cada registro de aluno. Então, o que o agrupamento tem a ver aqui?
  2. De que forma um grupo é "armazenado" dentro do carretel da mesa?
sql-server execution-plan

1 respostas

  1. Best Answer

    Como funciona o plano de execução

    O Segment Spool armazena as linhas de um grupo por vez. A subárvore é executada uma vez por grupo. Ao final do processamento de cada grupo, o spool é truncado e o processamento se repete para o próximo grupo de linhas.

    Eu escrevi sobre os detalhes completos em Partitioning and the Common Subexpression Spool .

    O iterador de Spool de Segmento sempre aparece como o pai imediato de um iterador de Segmento . Os dois iteradores de Spool de Tabela em nível de folha mostrados no plano são spools secundários , que reproduzem apenas as linhas salvas pelo spool primário.

    O Spool de Segmento grava linhas preguiçosamente em sua tabela de trabalho, até que o início de um novo grupo seja sinalizado. Depois que o Spool de segmento tiver um grupo completo em sua tabela de trabalho, uma linha (não o grupo inteiro) será retornada ao seu pai (o operador de loops aninhados de nível superior neste exemplo).

    Os valores de dados armazenados nesta linha não são importantes; não contribuem para o resultado final. O ponto é que essa única linha é recebida na entrada externa do iterador de loops aninhados pai. Isso faz com que o iterador execute sua entrada interna uma vez por grupo .

    Seu exemplo

    No seu exemplo, o agrupamento está implícito na correlação em ID:

    WHERE CX1.ID = CX2.ID

    Onde CX1.IDé uma referência externa.

    Dada a consulta original (alias CX2 ausente adicionado dentro do AVG):

    SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX2.CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)

    Sim, em princípio , cada linha de CX1 resulta em uma média sendo calculada em todas as linhas de CX2, onde IDcorresponde ao IDvalor atual na linha externa. É nesse sentido que os grupos são formados.

    Em geral, executar a consulta literalmente dessa maneira seria bastante ineficiente e resultaria no cálculo da mesma média várias vezes. É por isso que temos um otimizador; para encontrar um plano físico equivalente que produza os mesmos resultados lógicos, apenas com mais eficiência. Nesse caso, isso significaria calcular a média do grupo uma vez e juntá-la às linhas do grupo atual reproduzindo o spool.

    Mais ao ponto, o carretel aqui resolve o problema de calcular um agregado sobre linhas que ainda não vimos no fluxo. Considere que o plano final só acessa a tabela base uma vez, apesar de haver duas referências a ela na consulta original. Pode ser mais eficiente salvar as linhas em um grupo uma vez e reproduzi-las, em vez de acessar a tabela base uma vez por linha externa.

    Por exemplo, digamos que impedimos que o otimizador transforme a especificação de consulta em uma "aplicação de agrupamento":

    SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX2.CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)
OPTION (QUERYRULEOFF GenGbApplySimple);

    O plano de execução agora tem dois acessos à tabela:

    Sem aplicação de agrupamento

    Se formos ainda mais longe na restrição dos truques de otimização disponíveis, nos aproximaremos da interpretação literal do original:

    SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
    SELECT AVG(CX2.CX_Credit)
    FROM student CX2
    WHERE CX1.ID = CX2.ID
)
OPTION 
(
    QUERYRULEOFF GenGbApplySimple, 
    LOOP JOIN, 
    FORCE ORDER, 
    NO_PERFORMANCE_SPOOL
);

    Bem literal

    Uma especificação de consulta equivalente que você pode achar mais intuitiva é:

    SELECT
    S1.ID,
    S1.CX_Name,
    S1.CX_Credit
FROM 
(
    SELECT 
        S.*, 
        avg_credit = AVG(S.CX_Credit) OVER (
            PARTITION BY S.ID)
    FROM dbo.student AS S
) AS S1
WHERE
    S1.CX_Credit >= S1.avg_credit;

    O exemplo dado não é muito útil porque IDé efetivamente único. O otimizador não pode garantir isso sem algum tipo de restrição, então ele adiciona um spool defensivamente. Se garantirmos que IDé único:

    CREATE UNIQUE INDEX i ON dbo.student (ID);

    A consulta original produz um plano de junção sem agregação (já que uma agregação em no máximo uma linha é redundante):

    Junte

    Tente os exemplos nas postagens do meu blog abaixo. Você pode usar https://dbfiddle.uk/ que tem a opção de começar com uma nova cópia do banco de dados AdventureWorks a cada vez.

    Leitura adicional

    Outros posts relacionados meus:

    • 2

relate perguntas