Cross Apply e Outer Apply
O operador APPLY surgiu no SQL Server 2005, e permite a combinação de duas tabelas, de forma muito semelhante ao operador JOIN. A query chamada pelo operador APPLY é executada para cada linha da tabela referenciada, que foi previamente declarada na query principal. Como você deve ter imaginado, a query que trata a tabela referenciada é executada primeiro, e só então a query do operador APPLY é executada para cada linha resultante da query da tabela referenciada. O resultado final contem todas as colunas selecionadas na query principal, seguida de todas as colunas selecionadas na query do operador APPLY.
O operador APPLY permite a execução de uma query para cada linha retornada pela query associada a ela. Para isso, associamos o APPLY com as colunas da query principal.
O operador APPLY tem duas variações:
CROSS APPLY: a query da tabela referenciada só retorna linhas que correspondam ao resultado da query do operador APPLY.
OUTER APPLY: a query da tabela referenciada retorna todas as linhas, inclusive aquelas que não correspondam ao resultado da query do operador APPLY. As colunas da query do operador APPLY serão exibidas com NULL no resultado final da query, se mencionadas.
A pergunta agora é: se eu consigo o mesmo resultado usando o operador JOIN, porque e quando devo usar o operador APPLY? Embora o mesmo resultado possa ser alcançado com JOIN, utilizamos o APPLY quando temos uma expressão de valor de tabela na parte direita da query. E, em alguns casos, o uso do operador APPLY pode aumentar o desempenho da consulta. Vamos ver alguns exemplos.
Para começar, vamos criar duas tabelas: Aula e Aluno. Cada aluno está alocado em uma aula, e isso é garantido pelo relacionamento entre as tabelas.
Primeiro, vamos analisar a sintaxe do APPLY. A regra vale tanto para CROSS como para OUTER:
Em seguida, vamos executar estas queries:
A primeira query é executada em duas etapas. Primeiro, a query seleciona os dados da tabela Aula. Em seguida, executa o CROSS APPLY para avaliar a tabela Alunos em relação a cada linha retornada da tabela Aula. Observe que apenas as Aulas que possuem Alunos são exibidas no resultado da query. A segunda query usa INNER JOIN para conseguir o mesmo resultado.
Analisando o plano de execução destas queries, vemos que são iguais, e cada passo tem o mesmo peso.
Agora, vamos mudar um pouquinho as queries. Substituindo CROSS por OUTER e INNER por LEFT. Em seguida, vamos analisar o plano de execução das queries:
Novamente, a primeira query é executada em duas etapas. Primeiro, a query seleciona os dados da tabela Aula. Em seguida, executa o OUTER APPLY para avaliar a tabela Alunos em relação a cada linha retornada da tabela Aula. Porém, desta vez, todos os registros da tabela Aula são exibidos, e as colunas que não possuem valores correspondentes na tabela Aluno exibem NULL, como podemos ver nas linhas 5 e 6. A segunda query usa LEFT JOIN para conseguir o mesmo resultado.
E as queries retornam o mesmo resultado. Só que, desta vez, o Plano de Manutenção é um pouco diferente. Embora o custo total das queries também continue igual, a query com OUTER APPLY usou um operador Computer Scalar (com um custo estimado de 0,0000103% do custo total da query) antes do operador Nested Loops avaliar e produzir o resultado final da query.
Até aqui, os exemplos que utilizamos não aproveitaram toda a capacidade do operador APPLY. Vamos cadastrar mais alguns alunos antes de prosseguirmos.
Observe este script:
O objetivo desta consulta é retornar uma lista de aulas e quantidade de alunos, desde que a Aula tenha mais de 4 alunos.
Analisando os resultados, vemos que a primeira query (com APPLY), retornou todas as aulas cadastradas, e aquelas com 4 ou menos alunos foram exibidos com NULL. Já a query com LEFT JOIN, retornou apenas as Aulas que atenderam à condição da cláusula HAVING, o que não era o objetivo da consulta.
Uma das aplicações mais interessantes para APPLY é a execução de uma função. Veja este exemplo:
Uma função pode ser chamada usando CROSS ou OUTER. Nestes exemplos, o SQL Server executa a parte inicial da query (SELECT * FROM Aula), e para cada IdtAula retornado, ele executa a função que retorna um conjunto de linhas e são associadas às linhas da query inicial. Este é um exemplo onde não é possível utilizar JOIN.
Usando o CROSS APPLY para otimizar queries
Vamos analisar um caso onde o uso de CROSS APPLY ajudou a otimizar JOINs em condições com BETWEEN. Eu vi este exemplo num blog em inglês. Infelizmente (vergonha minha) não salvei a URL para publicar aqui. Mas fica o registro que este código não é meu, foi traduzido, mas eu acho ótimo!
Primeiro, vamos criar nossa massa de dados:
O script acima cria a tabela Contador, usada como base para criar registros nas demais tabelas. A tabela Comercial armazena comerciais para cada minuto do ano. Cada comercial da tabela tem até um minuto de duração, e eles não se sobrepõem. É possível criar restrições para garantir a integridade destas regras, mas vamos manter o script simples para este exemplo.
Esta query retorna apenas 181 linhas, e demora 3 segundos para executar:
Observe as estatísticas geradas pela query:
Table 'Chamada'. Scan count 525600, logical reads 1696424, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Comercial'. Scan count 3, logical reads 2857, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
E os tempos de execução:
SQL Server Execution Times:
CPU time = 3156 ms, elapsed time = 1903 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Para cada chamada o SQL Server lê todos os comerciais que começam antes do horário da chamada, o que é caro para o otimizador. A razão é simples: o otimizador não sabe que os comerciais são curtos, e que os comerciais não se sobrepõem, então ele deve ler todas as possíveis equivalências, que são todos os comerciais que começam antes do horário da chamada.
Como resolver?
Como os comerciais não se sobrepõem, nós precisamos no máximo de uma equivalência. Traduzir esta regra para o plano de execução do SQL é simples, e a query roda muito, muito rápido:
E as estatísticas mudaram para:
Table 'Comercial'. Scan count 181, logical reads 603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Chamada'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
E os tempos de execução:
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 3 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Uau! A diferença entre as duas sintaxes é gritante!
Como estamos considerando duas colunas da tabela Comercial, CROSS APPLY é a melhor alternativa para atender a esta demanda. Se apenas uma coluna de Comercial fosse necessária na análise, o uso de uma subquery também atenderia esta demanda com sucesso.
Como os comerciais são curtos, não há a necessidade de pesquisar os comerciais que começam mais do que o comprimento máximo do comercial antes da chamada. De novo, traduzir esta informação para o SQL Server é bem simples, e novamente a query roda muito mais rápida, até mais do que o exemplo anterior.
Vamos ver como ficaram as estatísticas:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
Table 'Chamada'. Scan count 196, logical reads 854, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Comercial'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Observem que a mudança de sinal na cláusula WHERE, de <= para <, alterou profundamente o plano de execução da query.
Como em toda query desenvolvida no SQL Server, é importante sempre escrever a query para retornar exatamente a informação necessária pela aplicação. E a análise das estatísticas pode nos ajudar a encontrar a melhor sintaxe para a consulta.