SQL Server - Views Indexadas

Melhorando desempenho de consultas utilizando Views Indexadas

O artigo tem o objetivo de ensinar a utilização e criação de Views Indexadas, mostrando a melhora de desempenho ao utilizá-la.

O artigo tem o objetivo de ensinar a utilização e criação de Views Indexadas, mostrando a melhora de desempenho ao utilizá-la. Para conseguir exemplificar com mais precisão, mostrarei os conceitos de utilização e criação de Views e Index independentemente um recurso do outro. Nos exemplos deste artigo, utilizaremos o banco AdventureWorks, que vem com o banco de exemplo do SQL Server 2005. Aconselho fortemente que você pesquise no Books Online sobre a criação e uso de sintaxe de Views e Indexes. No artigo vou apresentar a melhora no desempenho e não sua sintaxe. No Books Online você irá encontrar um material rico e completo sobre estes recursos. Entendendo o que é uma View A criação de instruções de consulta é na maioria dos casos simples, mas quando repetidos diversas vezes os mesmos códigos, pode-se começar a ficar cansativa a sua criação.  Para armazenar estas consultas em banco, utiliza-se um recurso já antigo de bancos de dados chamado View. Uma View é uma representação virtual de uma tabela, e pode conter colunas de uma ou varias tabelas físicas ou até mesmo de outras Views. Na maioria das vezes, as Views não armazenam os dados em banco, elas consultam os dados que foram selecionados em suas tabelas de origem. É possível utilizar uma View em qualquer lugar onde se possa utilizar uma tabela. Vamos criar uma View de exemplo, consultando as tabelas Product e ProductModel do banco AdventureWorks. CREATE VIEW vw_ProdutoDescricao WITH SCHEMABINDING AS SELECT P.ProductID Codigo, P.Name Descricao, M.Name Produto FROM Production.Product P INNER JOIN       Production.ProductModel M   ON P.ProductModelID = M.ProductModelID Esta View irá retornar os dados relacionados entre as duas tabelas, e como não foi especificado nenhum Schema e neste caso estou conectado ao banco como usuário SA(System Administrator), a View irá assumir que o Schema é o do DBO. Abaixo estão as sessões de Views do banco AdventureWorks, que exibe onde ficam armazenados todos objetos Views deste banco. Repare que na imagem da esquerda exibe o banco sem a View que criamos, e na imagem da direita, já apresenta a View criada. Para ativar o retorno das estatísticas de IO (Input/Output), e solicitar que seja exibido na aba de Messages não somente a quantidade de linhas que foram retornadas, mas também quais tabelas foram acessadas e quantas leituras lógicas (Logical Reads) foram necessárias em cada tabela para conseguir atingir o resultado solicitado, devemos executar o código abaixo: SET STATISTICS IO ON Para executar a consulta que acabamos de criar, podemos chamar a View vw_ProdutoDescricao, fazendo um SELECT simples nela, como se fosse o de uma tabela. SELECT * FROM vw_ProdutoDescricao Repare que ao executar o SELECT na View, é apresentado o mesmo resultado da execução do SELECT nas tabelas que foram utilizadas em sua criação. Pelo fato das estatísticas de IO estarem ativadas, o resultado é semelhante a este apresentado: (295 row(s) affected) 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. Table "Product". Scan count 1, logical reads 15, physical reads 1, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table "ProductModel". Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.   Neste exemplo são poucos dados que são armazenados na tabela de exemplo, mas já é possível visualizar que foram lidas 17 páginas de arquivos. Sendo 15 páginas da tabela Products e 2 páginas da tabela ProductModel. O que é um Index? O SQL Server é um banco de dados relacional, e em bancos de dados relacionais existe um recurso chamado Index (Indexação). O Index permite que o banco de dados acesse rapidamente linhas em uma tabela baseando-se nos valores de uma ou varias colunas. Para criar Index não se deve somente pensar em código T-SQL, Index é mais complicado do que isso. Criar um Index depende de estudo e análise das estruturas das tabelas para conseguir um resultado satisfatório. Caso você crie um Index em uma tabela que sofra muitas inserções de dados, o tempo para recalcular o Index pode ser mais demorado que se fosse executada uma consulta diretamente na tabela origem sem o Index. Existem dois tips de Index, o Index Clustered e o Index Non-Clustered. O Index Clustered irá determinar a ordem física de armazenamento das linhas na tabela, diferente do Index Non-Clustered, que é um objeto dentro do banco de dados que aponta para uma linha dentro de uma tabela, mas não especifica como estas linhas devem estar armazenadas. Partindo do mesmo princípio da criação de Chaves Simples e Chaves Compostas em tabelas, os Index também podem ser simples ou composto. O Index simples é baseado em apenas uma coluna, enquanto o Index composto é baseado em duas ou mais colunas de uma tabela ou View. A criação de um Index na View que criamos anteriormente será essencial para comprovar o que estou propondo no artigo. A melhora no desempenho. CREATE UNIQUE CLUSTERED INDEX idx_ProdutoDescricao ON vw_ProdutoDescricao(Codigo ASC)  Após a criação, o Index vai ficar armazenado na sessão de Index da View de exemplo. Para visualizar os Index criados em cada View, expanda as opções da View criada anteriormente e em seguida expanda as opções relacionadas aos indexes. Este procedimento pode ser realizado tanto para Views quanto para tabelas. Repare que o Index com o nome que criamos está presente nesta sessão. Como funcionam Views Indexadas. A possibilidade de criação de Index em Views, foi desenvolvida para a versão 2000 do SQL Server e melhorada para a versão 2005. Ao indexar uma View, os dados são armazenados em uma tabela virtual e são atualizados sempre que os dados de suas tabelas originais são alterados. Em vários casos, este recurso pode melhorar o desempenho, visto que os dados não precisarão ser “recalculados” sempre que forem consultados. As Views indexadas são recomendadas, quando os dados das tabelas que são utilizadas nas Views não sofrerem muitas alterações. No caso de existirem muitas alterações nos dados das tabelas, o tempo de processamento para montar o Index da View pode ser maior que o tempo para fazer a consulta sem Index às tabelas. Executando a mesma consulta que foi realizada à View no exemplo anterior, e mantendo as estatísticas de IO ativas, é possível comparar a quantidade de páginas que foram utilizadas para atingir o mesmo retorno. Repare que o SQL Server interpreta a View como sendo uma tabela do banco de dados, e faz leitura em 6 páginas para retornar os dados cadastrados. (295 row(s) affected) Table "vw_ProdutoDescricao". Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.   A velocidade de retorno de uma consulta é proporcional à quantidade de páginas que o SQL Server 2005 necessita ler para conseguir o resultado. Quanto mais páginas forem lidas para mostrar o resultado de uma consulta, mais tempo vai levar para esta consulta ser completada. Toda vez que o SQL Server 2005 realiza uma consulta, ele gera um plano de execução. Este plano de execução é armazenado em memória por um determinado tempo, e se for realizada esta mesma consulta enquanto o plano ainda existir, o SQL Server 2005 vai realizar a consulta de forma mais rápida. Para provar o que estou escrevendo, execute a instrução SELECT que originou a View (não é a execução da View, é a execução do SELECT que está dentro dela), esta instrução fará consulta à tabela vw_ProdutoDescricao, e vai consultar somente 6 páginas. SELECT P.ProductID Codigo, P.Name Descricao, M.Name Produto FROM Production.Product P INNER JOIN       Production.ProductModel M   ON P.ProductModelID = M.ProductModelID No resultado da consulta, na sessão de Messages é apresentada a prova disso.   (295 row(s) affected) Table "vw_ProdutoDescricao". Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                                   Neste artigo fico por aqui, apresentando a melhoria de performance ao utilizar Views indexadas. Espero que este artigo auxilie os desenvolvedores que necessitam melhorar o desempenho de consultas demoradas. Os dados apresentados são poucos, mas mesmo assim a melhora foi superior a 50%. Existem casos em que a melhora chega a ser de 90% ou mais. fonte: http://www.linhadecodigo.com/ArtigoImpressao.aspx?id=1308