• Artigo

Projetando exibições indexadas

As exibições também são conhecidas como tabelas virtuais, porque o conjunto de resultados retornado pela exibição tem o mesmo formato geral de uma tabela com colunas e linhas, e as exibições podem ser referenciadas da mesma forma que as tabelas nas instruções SQL. O conjunto de resultados de uma exibição padrão não é armazenado permanentemente no banco de dados. Sempre que uma consulta referencia uma exibição padrão, o SQL Server substitui a definição da exibição dentro da consulta internamente até que uma consulta modificada, que referencie apenas tabelas base, seja formada. Ele executa, então, a consulta resultante como sempre. Para obter mais informações, consulte Resolução de exibição.

Para uma exibição padrão, a sobrecarga de construir dinamicamente um conjunto de resultados para cada consulta que referencia uma exibição pode ser significante para as exibições que envolvam processamento complexo de um grande número de linhas, tais como agregando muitos dados ou unindo muitas linhas. Se tais exibições são freqüentemente referenciadas em consultas, você poderá melhorar o desempenho criando um índice clusterizado exclusivo na exibição. Quando um índice clusterizado exclusivo for criado na exibição, o conjunto de resultados será armazenado no banco de dados da mesma forma que uma tabela com um índice clusterizado é armazenada.

Outro benefício de criar um índice em uma exibição é que o otimizador começa usando o índice da exibição em consultas que não nomeiam diretamente a exibição na cláusula FROM. Consultas existentes podem se beneficiar da eficiência melhorada de recuperação de dados da exibição indexada sem terem de ser recodificadas. Para obter mais informações, consulte Resolvendo índices em exibições.

As modificações são feitas aos dados nas tabelas base, as modificações de dados são refletidas nos dados armazenados na exibição indexada. O requisito de que o índice clusterizado da exibição seja exclusivo melhora a eficiência com a qual o SQL Server pode encontrar as linhas, afetadas por qualquer alteração de dados, no índice.

A capacidade do otimizador de consulta para aproveitar as exibições indexadas ao processar consultas foi melhorada em relação às versões anteriores, quando ambas as definições de consulta e de exibição contêm os seguintes elementos:

  • Expressões escalares. Por exemplo, o otimizador de consulta pode corresponder à seguinte consulta com uma expressão escalar em seu predicado:

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    Para um índice criado nesta exibição:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

    As expressões escalares que incluem funções definidas pelo usuário também podem ser correspondidas de uma maneira semelhante.

  • Funções de agregação escalares. Por exemplo, a seguinte consulta que contém uma função de agregação escalar em sua lista SELECT:

    SELECT COUNT_BIG (*) FROM dbo.TableT

    Pode ser correspondida a um índice criado nesta exibição:

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

O otimizador de consulta também considerará o seguinte ao selecionar um plano de consulta:

  • Se um intervalo de valores definido por um predicado de consulta cai dentro de um intervalo definido em uma exibição indexada. Por exemplo, considere um índice criado na seguinte exibição:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

    Agora, considere a seguinte consulta.

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    O otimizador de consultas corresponderá essa consulta com a exibição V1 porque o intervalo entre 3 e 7 definido na consulta está dentro do intervalo entre 1 e 10 definido na exibição indexada.

  • Quanto uma expressão definida em uma consulta é equivalente à definida em uma exibição indexada. O SQL Server tenta corresponder expressões considerando suas referencias de coluna e os operadores lógicos AND, OR, NOT, BETWEEN e IN, e os operadores de comparação =, <>, >, <, >= e <=. Os operadores aritméticos, como + e %, e os parâmetros não são considerados.

    Por exemplo, o otimizador de consulta corresponderia à seguinte consulta:

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    Com um índice criado nesta exibição:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

Lembre-se, como é o caso com todos os índices, o SQL Server escolherá usar uma exibição indexada em seu plano de consulta apenas se o otimizador de consulta determinar que isso ajude.

Podem ser criadas exibições indexadas em qualquer edição do SQL Server 2008. No SQL Server 2008 Enterprise, o otimizador de consulta considera a exibição indexada automaticamente. Para usar uma exibição indexada em todas as outras edições, a dica de tabela NOEXPAND deve ser usada.

Diretrizes para projetar uma exibição indexada.

As exibições indexadas trabalham melhor quando os dados subjacentes forem atualizados com pouca freqüência. A manutenção de uma exibição indexada pode ser maior que o custo de manter um índice da tabela. Se os dados subjacentes forem atualizados freqüentemente, o custo de manter uma exibição indexada poderá ser maior que os benefícios de desempenho em usar uma exibição indexada. Se os dados subjacentes forem atualizados periodicamente em lotes, mas forem tratados primariamente como somente para leitura entre as atualizações, descarte todas as exibições indexadas antes de atualizar e reconstrua-as depois. Isto pode melhorar o desempenho das atualizações.

As exibições indexadas melhoram o desempenho dos seguintes tipos de consultas:

  • Junções e agregações que processam muitas linhas.

  • Operações de junção e de agregação executadas freqüentemente por muitas consultas.

    Por exemplo, em um banco de dados OLTP (online transaction processing) que está registrando inventários, muitas consultas deverão unir as tabelas ProductMaster, ProductVendor e VendorMaster. Embora cada consulta executando essa junção não possa processar muitas linhas, o processamento de junções geral de centenas de milhares dessas consultas pode ser significante. Porque essas relações provavelmente não são atualizadas com freqüência, o desempenho geral de todo o sistema poderia ser melhorado definindo uma exibição indexada que armazene os resultados das junções.

  • Cargas de trabalho de apoio à decisão.

    Sistemas de análise são caracterizados por armazenamento resumido, dados agregados raramente atualizados. Além disso, a agregação de dados e a união de várias linhas caracterizam muitas consultas de apoio à decisão. Também, sistemas de apoio à decisão, às vezes, contêm tabelas largas com muitas colunas ou colunas que são grandes ou ambos. Consultas que referenciam um estreito subconjunto dessas colunas podem se beneficiar de uma exibição indexada que inclua apenas as colunas na consulta ou de um estreito superconjunto dessas colunas. A criação de exibições indexadas estreitas contendo um subconjunto das colunas de uma única tabela é conhecida como uma estratégia de particionamento vertical, porque divide as tabelas verticalmente. Por exemplo, considere a seguinte tabela e exibição indexada:

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    A seguinte consulta pode ser respondida usando apenas v_abc:

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    A exibição v_abc ocupa muitos menos páginas que a tabelawide_tbl. Portanto, pode ser melhor o otimizador escolhê-la como caminho de acesso para resolver a consulta anterior.

    Se você quiser dividir uma tabela completa verticalmente, em vez de um subconjunto dela, recomendamos usar um índice não clusterizado na tabela que use a cláusula INCLUDE para incluir apenas as colunas desejadas, não uma exibição indexada. Para obter mais informações, consulte CREATE INDEX (Transact-SQL).

As exibições indexadas normalmente não melhoram o desempenho dos seguintes tipos de consultas:

  • Sistemas OLTP que tenham muitas gravações.

  • Bancos de dados que tenham muitas atualizações.

  • Consultas que não envolvam agregações ou junções.

  • Agregações de dados com um grau alto de cardinalidade para a chave GROUP BY. Um grau alto de cardinalidade significa que a chave contém muitos valores diferentes. Uma chave exclusiva tem o mais alto grau de cardinalidade possível porque cada chave tem um valor diferente. As exibições indexadas melhoram o desempenho reduzindo o número de linhas que uma consulta tem de acessar. Se o conjunto de resultados da exibição tiver quase tantas linhas quanto a tabela base, há pouco benefício de desempenho em usar a exibição. Por exemplo, considere a seguinte consulta em uma tabela com 1.000 linhas:

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

    Se a cardinalidade da chave de tabela for 100, uma exibição indexada, construída usando o resultado dessa consulta, teria apenas 100 linhas. Consultas que usem a exibição requerem, em média, um décimo das leituras necessárias em relação à tabela base. Se a chave for uma chave exclusiva, a cardinalidade da chave será 1000 e o conjunto de resultados da exibição retornará 1000 linhas. Se a exibição e a tabela base ExampleTable tiverem linhas do mesmo tamanho, a consulta não terá nenhum ganho de desempenho usando uma exibição indexada, em vez de ler diretamente a tabela base.

  • Expandindo junções. Essas são exibições que têm conjuntos de resultados maiores que os dados originais nas tabelas base.

Combinando exibições indexadas com consultas

Apesar das restrições nos tipos de exibições que podem ser exibições indexadas poderem impedi-lo de projetar uma exibição que solucione completamente um problema, você poderá projetar várias exibições indexadas menores que acelerem partes do processo.

Considere os seguintes exemplos:

  • Uma consulta freqüentemente executada agrega dados em um banco de dados, agrega dados em outro banco de dados e une os resultados. Porque uma exibição indexada não pode referenciar tabelas de mais de um banco de dados, não será possível projetar uma única exibição que execute todo o processo. Porém, você pode criar uma exibição indexada em cada banco de dados que faça a agregação para aquele banco de dados. Se o otimizador puder corresponder às exibições indexadas em relação às consultas existentes, ao menos o processo de agregação será acelerado sem a necessidade de recodificar as consultas existentes. Embora o processamento unido não seja mais rápido, a consulta geral será mais rápida porque usa as agregações armazenadas nas exibições indexadas.

  • Uma consulta freqüentemente executada agrega dados de várias tabelas e então usa a UNION para combinar os resultados. UNION não é permitida em uma exibição indexada. Você pode projetar exibições novamente para fazer cada uma das operações de agregação individuais. O otimizador pode, então, selecionar as exibições indexadas para acelerar consultas sem um requisito para recodificar as consultas. Enquanto o processamento UNION não é aprimorado, os processos de agregação individuais são.

Projete exibições indexadas que possam atender a várias operações. Porque o otimizador pode usar uma exibição indexada mesmo quando não estiver especificada na cláusula FROM, uma exibição indexada bem projetada pode acelerar o processo de muitas consultas.

Por exemplo, considere a criação de um índice na seguinte exibição:

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

Essa exibição, não apenas atende consultas que diretamente referenciam as colunas de exibição, mas também pode ser usada para atender consultas na tabela base e conter expressões como SUM(Colx), COUNT_BIG(Colx), COUNT(Colx) e AVG(Colx). Todas essas consultas serão mais rápidas porque elas apenas precisam recuperar um pequeno número de linhas de uma exibição em vez de ler todas as linhas das tabelas base.

Semelhantemente, uma exibição indexada que agregue dados e grupos por dia pode ser usada para atender a consultas que agreguem em diferentes intervalos de mais de 1 dia, como 7, 30 ou 90 dias.