artigo SQL Magazine 7 – SQL Server: Turbine suas queries com indexed views

admin 0 Comments Articles

artigo SQL Magazine 7 – SQL Server: Turbine suas queries com indexed views

Tire sua dúvida Marcar como concluído Anotar

O objetivo deste artigo é apresentar o conceito de indexed views do SQL Server e mostrar como implementar e utilizar esse tipo de view para otimizar consultas.

Conceito de view

As views são conhecidas também como “tabelas virtuais”, já que apresentam uma alternativa para o uso de tabelas no acesso a dados. Uma view nada mais é que um comando SELECT encapsulado em um objeto. A sintaxe para a criação de uma view é a apresentada na Listagem 1.

CREATE VIEW nome_da_visão ...) ] AS subconsulta;

Veja um exemplo de criação e uso de view na Listagem 1.

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

Dentre as vantagens da utilização de views, podemos citar :

• Simplificação do código: é possível escrever SELECTs complexos uma única vez, encapsulá-los na view e acioná-los a partir dela, como se fosse uma tabela qualquer;
• Questões de segurança: suponha que você possua informações confidenciais em algumas tabelas e, por isso, deseja que apenas alguns usuários tenham acesso a elas. Contudo, algumas colunas nessas tabelas precisam ser acessadas por todos os usuários. Uma maneira eficaz de resolver esse problema é criar uma view e ocultar as colunas confidenciais. Dessa maneira, pode-se suprir os direitos de acesso à tabela original e liberar o acesso à view;
• Possibilidade de otimização da consulta, por meio de implementação de indexed views.

Indexed views na prática

As views encapsulam comandos SELECT, o que significa que, sempre que elas são acionadas, os comandos SELECT associados a elas são executados. As views não criam repositórios para os para dados que retornam (como faz a tabela). Ora, que bom seria se pudéssemos “materializar” em uma tabela o resultado do comando SELECT encontrado na view, criando índices que facilitassem seu acesso. Pois bem, as indexed views fazem justamente isso. Executar um SELECT em uma indexed view tem o mesmo efeito que executar um select numa tabela convencional.

O principal objetivo das indexed views é aumentar a performance, e a vantagem do SQL Server é permitir que os planos de execução considerem a indexed view como um meio de acesso aos dados, mesmo que o nome da view não tenha sido explicitado na query. Isso é possível na versão Enterprise Edition do SQL Server 2000, onde o otimizador de comandos pode selecionar os dados diretamente na indexed view (em vez de selecionar os dados brutos existentes na tabela), como veremos a seguir.

Criação de uma indexed view passo-a-passo

1. Configurando o ambiente, o primeiro passo é configurar o estado de alguns parâmetros na sessão onde se deseja criar e utilizar a view, pois como a indexed view é “materializada” em uma tabela, nada pode interferir no seu resultado. Imagine, por exemplo, o seguinte cenário:
2. Uma determinada configuração, que afeta o resultado de um SELECT (por exemplo, concat_null_yelds_null), é definida antes da criação da indexed view;
3. A indexed view é criada; observe que o resultado da view será ‘materializado’ no disco de acordo com a configuração definida no passo anterior;
4. Em seguida, a configuração é desativada e a indexed view é executada pelo usuário. Como a view foi materializada, teremos um resultado incoerente com a configuração atual.

Por exemplo, a Listagem 2 mostra a diferença no resultado de um comando quando a propriedade concat_null_yields_null é alterada.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Imagine o que aconteceria se a indexed view fosse criada com a propriedade concat_null_yields_null ativada, mas a sessão atual estivesse com essa propriedade desativada – o mesmo SELECT iria conduzir a resultados diferentes!

Esse problema foi resolvido de forma simples – para criar e utilizar indexed views, é obrigatório configurar o ambiente de acordo com uma lista de valores padrão. Desse modo, é impossível obter resultados diferentes, pois a view simplesmente não funcionará se alguma das configurações estiver definida com um valor fora do padrão.

A Tabela 1 exibe essas configurações e seus respectivos valores padrão.

(*) O id é utilizado no comando sp_configure. Para conferir o que cada configuração faz, leia a seção “Configurações Necessárias para indexed views”. Existem duas maneiras para mudar o valor de uma configuração:

• Diretamente na sessão: execute o comando set ON | OFF
• Alterando o padrão existente no servidor: execute sp_Configure ‘user options’, . O número de id de cada configuração pode ser visualizado na Tabela 1.
  Nota: AritHabort possui o id 64 e Quoted_Identifier possui o id 256. Para ligar, por exemplo, Quoted_Identifier + AritHabort, executaríamos sp_cofigure, passando como parâmetro o resultado de 64+256 (=320): sp_configure ‘user options’, 320. Para obter uma listagem completa dos ids atribuídos a cada configuração, acesse http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

Para confirmar o estado de cada um dos parâmetros da Tabela 1, utilize a função SessionProperty(‘nome do parâmetro’) ou o comando DBCC UserOptions.

Dessa forma, configure todos os parâmetros de acordo com a coluna ‘estado exigido para indexed views’ da Tabela 1 – se isso não for feito, o SQL Server não permitirá criar/executar a indexed view.

Criando a indexed view

Criaremos uma view para totalizar a quantidade diária vendida na tabela Order Details, localizada no database NorthWind. Veja a Listagem 3.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

É necessário observar algumas particularidades ao criar indexed views:

• A view precisa ser determinística. Uma view será determinística se utilizarmos somente funções determinísticas em seu código. Um mesmo comando SELECT executado repetidamente em uma indexed view (considerando-se uma base estática) não pode apresentar resultados diferentes. As funções determinísticas asseguram que o resultado de uma função se manterá inalterado independentemente do número de vezes que ela for executada. A função DatePart, por exemplo, é determinística, já que retorna sempre o mesmo resultado para uma data específica. Já a função getdate() retornará um valor diferente a cada execução. Para obter a relação completa das funções determinísticas do SQL Server 2000, acesse http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• Verifique se não existem restrições de sintaxe. As cláusulas, funções e tipos de queries abaixo relacionadas não podem integrar o schema de uma indexed view:
  • MIN, MAX,TOP
  • VARIANCE,STDEV,AVG
  • COUNT(*)
  • SUM em colunas que permitem valores nulos
  • DISTINCT
  • Função ROWSET
  • Tabelas derivadas, self joins, subqueries, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext e image
  • COMPUTE e COMPUTE BY
  • HAVING, CUBE e ROLLUP

É necessário criar as indexed views com SchemaBinding. Para manter consistente o conteúdo da view, não é possível alterar a estrutura das tabelas que a deram origem. Para evitar esse tipo de problema, é obrigatório utilizar SchemaBinding na criação de indexed views, pois essa opção não permite alterar a estrutura da tabela sem que se elimine antes a view.

Para utilizar a cláusula GROUP BY, é obrigatório incluir a função COUNT_BIG(*). A função count_big(*) faz o mesmo que count(*), porém retorna um valor do tipo bigint (8 bytes).

Informe sempre o owner dos objetos referenciados na indexed view. Utilize select * from dbo.Orders em vez de select * from Orders, já que é possível haver tabelas com o mesmo nome mas com proprietários diferentes. Como a opção de schemabinding é obrigatória, o SQL Server precisa da especificação exata do objeto para coibir a alteração do schema.

Criando um índice cluster na view (materialização)

A view criada no item 2 ainda não se porta como uma indexed view, pois o resultado do comando select não foi materializado em uma tabela. É possível confirmar essa afirmação executando o comando sp_spaceused no Query Analyzer, que retorna o número de linhas e espaço utilizados pelas tabelas (Listagem 4).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Observe na Listagem 5 que o processamento da view é puramente lógico, tanto que o valor de Physical Reads é zero. Anote os valores registrados em Logical Reads e Physical Reads (1672+0+4+0=1676) – utilizaremos esses valores em nossas comparações futuras.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Para verificar se a view pode ser indexada (materializada), ou seja, se ela foi criada dentro dos padrões e configurações necessárias a indexed views, o resultado do SELECT a seguir deverá ser igual a 1.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Confirmados os pré-requisitos, podemos agora criar o índice. A sintaxe possui o mesmo formato utilizado na criação de índices em tabelas convencionais:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)

Note que o índice cluster é obrigatório porque ele gera páginas de dados. Você só poderá criar índices não-cluster em indexed views depois de criar o índice cluster.

Agora o SELECT encontrado na view foi materializado, o que pode ser comprovado com o comando sp_spaceused no Query Analyzer (Listagem 6).

sp_SpaceUsed vi_vendas_mêsgo-----------------------------------------------------Name Rows Reserved data index_size Unusedvi_vendas_mes 23 24 KB 8 KB 16 KB 0 KB

Utilizando indexed views

Uma das maneiras de acessar uma indexed view (assim como uma view convencional) é fazendo referência a seu nome no comando SELECT:

select * from vi_vendas_mes

Compare o volume de páginas movimentadas na Listagem 5 (1672+4=1676) com o da Listagem 7 (2+0=2). A diferença é bastante expressiva – a criação da indexed view reduziu o total de I/O necessário em 1674 páginas.

Set statistics io ON select * from vi_vendas_mes go --------------------------------------------------------- ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 59 1996 9 1124 57 ..... (23 row(s) affected) Table 'vi_vendas_mes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Vejamos outro exemplo. A Figura 1 mostra o plano de execução de uma query. Confirme que a view foi selecionada mesmo sem estar presente na linha do SELECT.

Durante a construção do plano de execução da query, o otimizador constatou que já existiam dados pré-sumariados para a query em vi_vendas_mes e optou por selecionar os dados diretamente na indexed view.

Repare que a query executada na Figura 1 é idêntica à encontrada na view vi_vendas_mes. No entanto, o acesso à view pelo otimizador independe da semelhança entre a consulta executada e a consulta da view. A seleção da indexed view pelo processador de queries leva em conta apenas o custo-benefício. Desse modo, as queries executadas não precisam ser idênticas à view (observe a Figura 3).

Entretanto, é necessário seguir algumas regras para que a indexed view seja considerada pelo otimizador de queries:

• O join presente na view precisa “estar contido” na query: se a query efetuar um join entre as tabelas A e B, e a view executar um join entre A e C, a view não será acionada no plano de execução. No entanto, se a query executar um join entre A,B e C, a view poderá ser acionada.
• As condições estabelecidas na query precisam estar de acordo com as condições na view: no SELECT da Figura 2, a indexed view vi_vendas_mes não será considerada, pois a cláusula where não estava presente no código da view, o que fez com que as linhas com Quantity <= 5 fossem computadas no agrupamento.

Por outro lado, se a query possuir a condição where sum(Quantity) > 5, a view vi_vendas_mes será considerada no plano de execução, pois a condição da pesquisa é um subconjunto do SELECT presente na view.

As colunas com funções de agregação na query precisam “estar contidas” na definição da view: se a view retorna a coluna qtde=sum(Quantity) e a query possui uma coluna vlr_unitario=sum(UnitPrice), a view não será considerada.

A Figura 3 mostra um comando SELECT que permite comprovar a inteligência do otimizador de comandos – o cálculo AVG(Quantity) foi substituído pela divisão entre SUM(Quantity) / Count_Big(*), representada pelo ícone Compute Scalar. O predicado where sum(Quantity) > 1500 (representado pelo ícone Filter) também é considerado.

Considerações gerais sobre a utilização de indexed views:

• As indexed views podem ser criadas em qualquer versão do SQL Server 2000. Entretanto, somente na versão Enterprise Edition serão selecionadas automaticamente pelo otimizador de queries.
• Em versões diferentes da Enterprise, é necessário utilizar o hint NoExpand para acessar a indexed view como uma tabela convencional. Se não for empregado NoExpand, a indexed view será considerada uma view “normal”.
  Nota: O hint Expand faz o inverso de NoExpand: trata a indexed view como uma view “normal”, forçando o SQL Server a executar o comando SELECT durante a fase de run-time.
• A manutenção de uma indexed view é automática (como nos índices); ela não requer nenhum tipo de sincronização adicional. Pela sua própria característica (normalmente armazena dados pré-sumarizados), sua atualização tende a ser um pouco mais lenta que a dos índices convencionais.
• A utilização de indexed views em bases OLTP exige cautela, pois embora apresentem ótima performance em queries, causam overhead nos processos que modificam as tabelas relacionadas na view. Em situações onde é necessária alta performance de escrita, com atualizações freqüentes, a criação de indexed views não é recomendada.
• Como faz com os índices, o otimizador analisará o código da view nas versões Enterprise como parte do processo de escolha do melhor plano de execução de uma query. No entanto, se houver muitas indexed views passíveis de execução para uma mesma query, poderá ocorrer um aumento substancial nesse tempo de escolha, já que todas as views serão analisadas. Portanto, utilize bom senso ao implementar as views.

Configurações Necessárias para indexed views

ANSI_NULLS

Define a forma como as comparações com valores nulos são efetuadas (Listagem 8).

set ANSI_NULLS ON declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' ------------------------------------- FALSO set ANSI_NULLS OFF declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' -------------------------------------- VERDADEIRO

ANSI_PADDING

Determina como devem ser armazenadas as colunas char, varchar, binary e varbinary quando seu conteúdo for menor que o tamanho definido na estrutura da tabela. O padrão do SQL Server 2000 é manter ansi_padding ativado (=ON); nessa condição valem as regras abaixo:

• Ao atualizar colunas do tipo char, serão acrescentados espaços em branco no final da string, se esta possuir tamanho menor que o definido na estrutura da coluna. A mesma regra vale para colunas binary (nesse caso, o espaço é preenchido por uma seqüência de zeros)
• As colunas varchar ou varbinary não seguem a regra acima: mantêm sempre seu tamanho original.

ARITHABORT

Quando ativado, finaliza a execução da query ao encontrar uma divisão por zero ou algum tipo de overflow.

QUOTED_IDENTIFIER

Quando ativado, permite o uso de aspas duplas para especificar nomes de tabelas, colunas etc. – dessa forma, esses nomes poderão possuir espaços e\ou caracteres especiais.

CONCAT_NULL_YELDS_NULL

Controla o resultado da concatenação de strings com valores nulos. Quando ativado, determina que essa junção deve retornar um valor nulo; caso contrário, retornará a própria string.

ANSI_WARNINGS

Quando ativado, determina a geração de mensagens de erro quando:

1. você utilizar funções de sumarização e forem encontrados valores nulos no range da query;
2. forem encontradas divisões por zero ou arithmetic overflow.

NUMERIC_ROUNDABORT

Controla como o SQL Server deve proceder ao encontrar perda de precisão numérica em operações aritméticas. Se o parâmetro estiver ativado e uma variável com precisão de duas casas decimais receber um valor com três casas decimais, a operação será abortada. Se o parâmetro estiver desativado, o valor será truncado para duas casas decimais.

Conclusão

Quando se trata de otimização de queries, asindexed viewssão uma boa escolha para alavancar a performance. Portanto, avalie minuciosamente as queries que lidam com sumarizações e que são executadas com certa freqüência e parta para a criação deindexed views. O resultado vale a pena!