Crie um site como este com o WordPress.com
Comece agora

Utilizando View Indexada para melhorar a performance de suas consultas

Fala pessoal, tudo bem?  

Há pouco tempo trabalhei em uma query em um ambiente 2008 R2 que retornava dados agregados com um tempo de execução bem alto. Indexei a query, porém, a coluna que realizava o filtro não era nem um pouco seletiva e por conta disso, não obtive ganho expressivo no tempo de execução. 

O objetivo de hoje, é trazer a solução que implantei para este caso utilizando uma view indexada. 

Na imagem abaixo, vemos uma query bem diferente do caso onde atuei, mas que atende os requisitos para passar o que desejo. 

O objetivo da query abaixo é trazer o valor total de todos os clientes por data. 

*LINK PARA OS SCRIPTS NO FINAL DO POST. 

Ao executar a query acima, obtive as seguintes estatísticas: 

Trabalhando na solução com a View indexada. 

      A view indexada mantem armazenado o resultado do código descrito no objeto,  para evitar que este preço seja pago durante a execução de uma consulta. 

Dito isto, já é de se imaginar que além de um aumento no consumo de espaço em disco por conta do índice, pode-se ter um aumento no tempo de operações DML, como delete, update e insert, principalmente em tabelas que ocorrem muitas movimentações. 

Devemos ter bastante cuidado para implantar esta solução, então antes de aplicar na produção, teste em seu ambiente de homologação, pois ao criar uma view indexada podemos ter alguns problemas, como a falha de inserções ou até mesmo resultados diferentes do esperado. (isso acontece por conta das opções definidas na sessão, os SET OPTIONS) 

Como mencionado pela Microsoft devemos seguir os passos abaixo para criação da view indexada: 

  • Verifique se as opções SET estão corretas para todas as tabelas existentes que serão referenciadas na exibição. 
  • Verifique se as opções SET da sessão estão definidas corretamente antes de criar qualquer tabela nova e a exibição. 
  • Verifique se a definição de exibição é determinística. 
  • Crie a exibição usando a opção WITH SCHEMABINDING. 
  • Crie o índice clusterizado exclusivo na exibição. 

Dito isto, vamos a criação da view indexada de acordo com a query enviada acima: 

1 – Além dos pré-requisitos, citados acima, devemos utilizar a função COUNT_BIG(*)  toda vez que utilizarmos o GROUP BY, para não obter erro durante a criação do índice clusterizado em cima da view. 

 2 – Criação do índice clusterizado em cima das colunas que estão sendo feitas as agregações: 

Ao tentar criar este índice iremos obter o seguinte erro: 

Cannot create index on view “Northwind.dbo.VW_CALCULO” because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead. 

Como descrito no erro acima, não é suportado o uso de OUTER JOINS em views indexadas, como solução para este caso, iremos criar a view com INNER JOIN e fazer uso dos OUTER JOINS por fora, para conseguir chegar no resultado desejado. 

Após a alteração do LEFT para INNER JOIN o índice foi criado com sucesso: 

Tendo executado o passo acima, finalizamos a criação da VIEW INDEXADA. 

Voltando para a query, por conta da limitação do uso de OUTER JOINS na view indexada, iremos reescrever a query, chegando no código abaixo: 

Ao executar a query acima, é nítido que houve um ganho de performance, no tempo de execução, consumo de CPU e também na quantidade de leituras. 

Conclusão: 

Como dito acima, a view indexada é um ótimo recurso para melhorar o tempo de execução de queries, porém, devemos ficar atento aos riscos de implantar esta solução. Uma vez que implantada em homologação e validado o custo de operações DML nas tabelas e também não detectado problemas com os SET OPTIONS, as views indexadas costumam ser extremamente válidas e até recomendadas. 

Algumas observações: 

  • Na versão enterprise o otimizador de consultas consegue em tempo de execução identificar que existe uma view indexada e utiliza-la para o ganho de performance; 
  • Na demais versões, além de não conseguir em tempo de execução identificar o mencionado acima, é necessário utilizar o HINT noexpand, para que a view não seja expandida e os objetos acessados, para que os dados sejam obtidos em cima do índice criado em cima da view. 

É isso pessoal, um abraço e até a próxima! 

Referência view indexadahttps://docs.microsoft.com/pt-br/sql/relational-databases/views/create-indexed-views?view=sql-server-2017 

Referência HINT Noexpandhttps://docs.microsoft.com/pt-br/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017#using-noexpand 

Link para os scripts utilizadoshttps://github.com/Nusyc/ViewIndexada 

OBS:  

  • Rodar script 1 (Pode demorar alguns minutos) 
  •  Script 2 contém os selects em questão junto com a criação da view indexada. 

Um comentário em “Utilizando View Indexada para melhorar a performance de suas consultas

Adicione o seu

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Blog no WordPress.com.

Acima ↑

%d blogueiros gostam disto: