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

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Artigo da Revista SQL Magazine -Edição 7.

Atenção: por essa edição ser muito antiga não há arquivo PDF para download.
Os artigos dessa edição estão disponíveis somente através do formato HTML.

capnet43.jpg

Clique aqui para ler todos os artigos desta edição

 

SQL Server: Turbine suas queries com indexed views

 

 

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 seguinte:

 

CREATE VIEW nome_da_visão

[(nome_da_coluna) [ , nome_da_coluna] ...) ] 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

        [Order Details] 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

Listagem 1- Criação e utilização de views

 

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:

 

1)       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;

2)       A indexed view é criada; observe que o resultado da view será ‘materializado’ no disco de acordo com a configuração definida no passo anterior;

3)       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

Listagem 2 - Exemplo da configuração concat_null_yields_null

 

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.

 

Configuração

Id (*)

Estado exigido p/ indexed views

Padrão do SQL Server 2000

Padrão em conexões OLE DB (=ADO) ou ODBC

Padrão em conexões que utilizam         DB Library

ANSI_NULLS

32

ON

OFF

ON

OFF

ANSI_PADDING

16

ON

ON

ON

OFF

ANSI_WARNING

8

ON

OFF

ON

OFF

ARITHABORT

64

ON

OFF

OFF

OFF

CONCAT_NULL_YIELDS_NULL

4096

ON

OFF

ON

OFF

QUOTED_IDENTIFIER

256

ON

OFF

ON

OFF

NUMERIC_ROUNDABORT

8192

OFF

OFF

OFF

OFF

Tabela 1 - Configurações que podem influenciar o resultado de uma view

(*) 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.

 

 

2 ) 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.[Order Details] od

on o.OrderId = od.OrderId

group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate)

go

Listagem 3 - View para totalizar quantidade vendida

 

É 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.

 

3 ) 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.

Listagem 4 - Utilização do comando sp_spaceused em uma view

 

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_mes

go

---------------------------------------------------------

 

ano         mes          qtde_total   contador            

----------- ----------- ------------- --------------------

1996        7              1462          59

1996        8              1322          69

1996        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.

Listagem 5 - Total de I/O associado à view ANTES da criação do índice cluster

 

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ês

go

-----------------------------------------------------

Name

Rows

Reserved

data

index_size

Unused

vi_vendas_mes

23

24 KB

8 KB

16 KB

0 KB

Listagem 6 - Utilização do comando sp_spaceused em uma indexed view

 

4 ) 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.

Listagem 7 - Total de I/O associado à view depois da criação do índice cluster

 

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.

 

 

Figura 1 - Plano de execução de query acessando a indexed view criada

 

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.

 

 

 

 Figura 2 - Plano de execução de query sem acesso à indexed view criada

 

·         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.

 

 

 

Figura 3 - Select “genérico” utilizando indexed view

 

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.

 

Conclusão

 

Quando se trata de otimização de queries, as indexed views sã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 de indexed views. O resultado vale a pena!

 

Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine. Atua como DBA sênior em SQL Server na Livraria e Papelaria Saraiva S/A.

 

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

Listagem 8 - Exemplos da configuração ansi_null

 

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: i) você utilizar funções de sumarização e forem encontrados valores nulos no range da query; ii) 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 3 casas decimais, a operação será abortada. Se o parâmetro estiver desativado, o valor será truncado para duas casas decimais.

 

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?