MCSA – Subquery

ESSE POST FOI ATUALIZADO, FAVOR ACESSE AQUI: SUBQUERY

Podem ser auto relacionadas independente da query externa ou correlatas, quando temos a query interna referenciando a externa. No caso, igualando.

Neste post sobre subqueries vamos estudar as de valores únicos e valores múltiplos

SUBQUERY AUTO CONTIDA

São as subqueries que não possuem dependência com a query externa. Neste tipo de query, podemos executar  a query interna de forma isolada

Por padrão a subquery abaixo é categorizada como escalar e deveria retornar múltiplos resultados, porém, o filtro where limita. 

Como podem ver, selecionando apenas a query interna para execução na subquery.

Veja a subquery abaixo utilizando IN retornando múltiplos valores sendo que as tabelas são diferentes e podem ser executadas separadamente.

Consulta vai retornar todos que estão na tabela person e são funcionários.

Subqueries de valor único ou escalar podem ser utilizadas normalmente na cláusula where.

ALL, SOME & ANY EM SUBQUERIES

TSQL suporta alguns elementos extras quando utilizamos subquery como: all, some e any.

Embora não sejam muito conhecidos nem utilizados, achei interessante abordar neste post para mostrar uma variedade na elaboração das suas subqueries.

O operador all irá retornar resultado se todos os valores da subquery forem verdadeiros em comparação com a cláusula where da query externa.

Por exemplo na query abaixo, todos os ID’s de território da query externa que foram encontrados(avaliados como verdadeiro) na query interna são retornados na consulta.

Para any e some a lógica é a mesma. Retorna verdadeiro para todos os valores se pelo menos um valor encontrado for verdadeiro.

Lógica aqui é retornar qualquer (any) ou algum (some) valor da query externa que seja igual da query interna.

SUBQUERIES CORRELATAS

A subquery correlata é um tipo de construção onde a consulta interna referencia a consulta externa através de uma coluna em comum.

Pode ser utilizado com uma opção ao join se a sua mostra de resultados não for tão grande. O join normalmente possui uma performance melhor.

Igualamos as queries na cláusula where. 

Não é possível executar a query interna de forma independente como nas subqueries auto contidas.

Veja a primeira query a quantidade de linhas retornadas e o resultado de forma resumida.

Agora, veja quando executamos a subquery por completo, veja o resultado também resumido.

E ainda podemos unificar com uma terceira consulta na mesma subquery, veja:

Percebam que a medida que vamos adicionando mais consultas, a query vai ficando mais complexa e menos legível e fácil de manusear e realizar manutenção. Neste caso, prefira usar o join.

E por falar em join. Veja que tipo de operação ocorre no plano de execução desta query. Por mais que pareça um pouco complexo, apenas veja a operação em destaque.

Como podem ver, a própria execução lógica da subquery é feita com base no inner join!!!

EXISTS

Muito utilizado por exemplo em consultas baseadas em datas que no filtro where, passamos um valor específico.

Veja o exemplo abaixo de uma subquery correlata:

Agora, um simples exemplo da utilização do EXISTS com uma subquery auto contida.

Percebam que mesmo a subquery possuindo a mesma estrutura do primeiro exemplo da subquery correlata com exists, o filtro where age complementamente diferente não limitando apenas nas datas que foram passadas como filtro.

Isso ocorre porque a subquery auto contida não é capaz de realizar um filtro específico para retornar o resultado para  a query externa. 

Compare inclusive os planos de execução.

O primeiro plano de execução é para a subquery correlata que retornou apenas os registros dentro do filtro de datas passado.

Agora repare o plano de execução da subquery auto contida e como ele operou para nos retornar os registros.

Alguns pontos que podemos perceber:

• No primeiro plano, embora o banco tenha estimado 317 linhas, ele leu de fato apenas 301. Isto ocorre por conta das operações de igualdade da data para retornar o período correto.
• Em todas as operações realizadas no primeiro plano, ele sempre retornou menos linhas do que o estimado
• No segundo plano, embora o banco tenha lido e estimado apenas uma linha, em todas as operações ele acabou lendo e retornando todas.
• E ainda que as duas queries internas tenham utilizado o recurso de index seek, o que costuma ser mais rápido, a segunda query é bem mais custosa e não utiliza do recurso da melhor forma.

Veja por exemplo, quando executamos as duas queries juntas:

A query 1 sublinhada em vermelho e a 2 em preto.

Diferente do que ocorre quando utilizamos o IN. Tanto a subquery correlata quanto a auto contida possuem basicamente o mesmo plano de execução.

Executem as subqueries abaixo com o plano de execução habilitado e comparem.

No geral, quando utilizamos a subquery, ela irá retornar true se pelo menos uma linha do conjunto de resultados assim o for. 

Um fato curioso sobre o EXISTS é que não existe uma forma dele retornar um valor ‘unknown’ nas suas avaliações. Logicamente o exists funciona apenas como verdadeiro ou falso

E se quiser negar um determinado resultado, utilize o not exists. O mesmo vale para not in.

ORDENAMENTO

Como sabemos, não é possível utilizar order by nas subqueries convencionais que vimos nos exemplos anteriores. Para isso, temos uma solução de contorno, mas que não é tão trivial e que deixa o código um pouco confuso, dependendo do caso.

Como utilizamos a cláusula top, o order by passa a ser permitido dentro das subqueries, assim, favorecendo nossa solução de contorno. 

Nota: este seria um caso onde o uso de join seria mais proveitoso.

Subqueries x Joins

Nem sempre uma ferramenta será melhor que a outra. Uma avaliação para cada caso se faz necessário para não comprometer a performance.

Nestes exemplos, vamos averiguar apenas os casos onde o SQL Server performa melhor em cada uma das escolhas.

Nosso primeiro caso é com agregações baseadas no mesmo conjunto de linhas e para isso, vamos criar um index com base nas colunas que vamos utilizar. 

A query abaixo realiza um cálculo com base no frete e custo por cliente. Quando realizamos este tipo de query utilizando subquery, a performance não é das melhores já que o SQL Server acessa o disco por subquery.

Antes de executar esta query, aperte o control+m para habilitar o query execution plan e veja a quantidade de index scan que o SQL Server faz na tabela. Cada operação indica quantos acessos ele fez ao disco ou a memória, caso a query já tenha sido executada. 

Na imagem abaixo, irei mostrar apenas a criação da subquery. 

Agora veja a mesma subquery sendo executada com join mostrando que o número de acessos a disco diminuiu 

Uma dica: execute as duas queries juntas e veja o quanto cada query consome no processamento!!

E no último caso, veja esta subquery e sua execução. Veja que ela é executada bem mais rápida que um join.

Antes do teste, execute o pequeno insert na tabela salesreason, como mostra a imagem. Não esqueça de executar as duas queries juntas. 

RESUMINDO

São um ótimo recurso para consultas, mas que fique claro que cada subconsulta gera um acesso a disco e isto pode ser um problema de performance.

Nem sempre um join será a melhor opção assim como nem sempre a subquery o será. Avalie cada caso.

Em alguns casos ou a maioria, a subquery correlata será melhor em questão de performance do que uma auto contida, salvo se utilizar o IN.

Para fazer download do script utilizado, clique aqui.

Espero que tenham gostado, saúde!