View vs Function
No artigo anterior, fiz uma breve introdução sobre Views e comentei que não há diferença de desempenho em usar Tabela, View ou CTE.
Agora vamos criar uma View e uma Function para fazer a comparação. Note que a sintaxe do CREATE FUNCTION é um pouco mais extensa, pois define o tipo de dado a ser retornado e depois retorna um comando SELECT. Essa é uma FUNCTION do tipo “Single Statement”.
Se consultarmos a DMV sys.dm_exec_cached_plans, observamos que ambos são armazenados como Parse Tree – isso significa que representam apenas uma definição. Portanto, o desempenho entre Function Single-Statement é igual a uma Tabela, View ou CTE.
Multi-Statement Functions
Usar FUNCTION ao invés de VIEW traz um grande benefício: a flexibilidade em seu uso. Podemos compor múltiplos comandos para retornar os registros. A sintaxe é composta por dois blocos:
- Definição da tabela de retorno (@tb)
- Bloco composto por um ou mais comandos (BEGIN.. END)
Vamos começar vendo a DMV sys.dm_exec_cached_plans, que mostra a função sendo representada por um “Compiled Plan – Proc”.
- Function Single-statement é uma definição: representado por “parser tree”
- Function Multi-statement é um contexto de execução: representado por “compiled plan”
Isso significa que, por baixo dos panos, existe uma diferença significativa entre uma FUNCTION Single-statement e Multi-statement. Uma diferença clara está no plano de execução de consultas usando essas funções. Primeiro, vamos usar uma FUNCTION single-statement, que retorna um único plano de execução:
Por outro lado, uma FUNCTION multiple-statement tem seu próprio contexto de execução e executa separadamente do contexto do SELECT. Isso é observado no plano de execução, que é composto por dois planos de execução: query 1 corresponde ao SELECT; query 2 corresponde à FUNCTION.
Por causa desse comportamento de múltiplos contextos, as FUNCTION Multi-statement são geralmente menos eficientes em relação às FUNCTION Single-statement (e views, CTE, tabelas).
Predicate Push-Down
Predicate Push-Down é uma otimização do SQL Server, na qual a query é reescrita com os filtros aplicados às tabelas. Por exemplo, quem está acostumado com programação procedural (C++, C#, Java, etc), poderia pensar que a consulta a seguir executa a função fnUsuarios() e depois realiza os filtros de id = 1 e nome = b.
Entretanto, a otimização do “predicate push-down” empurra os filtro “id=1 and nome = b” para dentro da função fnUsuarios. Isso significa que a função fnUsuarios executaria o seguinte comando:
(Note que esse comando acima é fictício apenas para ilustrar o predicate push-down) Por conta da otimização, a função usa os índices disponíveis para retornar somente os registros que satisfazem ID e NOME
Essa otimização ocorre no momento em que as Parse Trees são convertidas em um Compile Plan de execução. Por causa disso, não é possível aplicar a otimização de Predicate Push-Down em contexto de execuções distintos, como é o caso do SELECT com FUNCTION Multi-statement. Por outro lado, o predicate push-down ocorre naturalmente em todas as consultas SELECT, sub-SELECT, assim como também se aplica a Views, CTE e Function Single-statement.
Conclusão
Analisando o plano de execução de fnUsuariosMulti, observamos que todos os registros são inseridos na tabela @tb (Query 2) e depois o comando SELECT filtra os resultados desejados (Query 1). Nesse caso, nenhum índice é utilizado. Em nenhum momento, os índices são utilizados para acelerar a consulta.
Se a função fnUsuariosMulti fosse substituída por uma View ou uma Function Single-statement, então o otimizador conseguiria agregar todas as Parse Trees em uma e depois a converteria em um Compiled Plan único. Nesse momento, a otimização de Predicate Push-Down seria aplicado diretamente à tabela e um índice seria usado para acessar os dados. O plano final se reduziria a um simples Clustered Index Seek:
No próximo artigo, gostaria de propor um desafio. Existe um erro muito comum de programação que impacta diretamente na performance. Será que você consegue resolver? [Spoiler alarm] Adianto que a solução é simples: use Views.