Início / dba / Perguntas / 143208
Accepted
John
Asked: 2016-07-08 00:57:37 +0800 CST

Agrupar por em visualizações materializadas e planejamento de consulta [duplicado]

  • 772

Em parte por curiosidade, me perguntei se poderia usar uma exibição indexada (materializada) para acelerar uma consulta de contagem em alguma tabela base.

A consulta é algo como

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

então eu criei uma visão

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

com um índice agrupado

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

E isso funciona, agora posso escrever a consulta original como

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

e obter o resultado desejado muito mais rápido.

Infelizmente, dificilmente é muito útil para mim, pois minhas consultas geralmente não são feitas à mão. Eu realmente preciso que a consulta original se torne mais rápida usando a exibição indexada como uma espécie de índice para BaseTable- e acho que li em algum lugar que isso pode acontecer em algumas circunstâncias, mas de acordo com meus testes, não neste.

Então minha(s) pergunta(s) seria(m):

  • As exibições indexadas ainda podem me ajudar neste cenário de alguma forma?
  • Alguém pode recomendar fontes/literatura que explique em quais casos as exibições indexadas são usadas para otimizações de consultas nas tabelas em que se baseiam?

EDIT: Na pergunta duplicada - estou mais interessado no GROUP BY e no aspecto agregado de exibições indexadas. A resposta me ajudou a encontrar um erro estúpido que cometi, agora está funcionando para mim também.

JOYOUS ADDENDUM : Agora que funcionou, testei com sucesso que funciona até mesmo nos casos em que a consulta contém uma junção à esquerda naqueles casos em que a junção à esquerda pode de fato ser otimizada (ou seja, a cláusula on cobre um índice exclusivo na tabela unida).

Isso é realmente incrível, porque significa que, mesmo em casos de consulta com junções à esquerda, é possível projetar o esquema de forma que obter uma contagem total de linhas de tudo ou agrupamentos específicos seja rápido.

sql-server materialized-view

1 respostas

  1. Best Answer

    O recurso é chamado de 'correspondência de visualização indexada' e pode ser complicado acertar. Há vários pré-requisitos descritos aqui: Resolução de índices em exibições , mais a exibição indexada precisa ser capaz de responder à pergunta que sua consulta está fazendo. Eu esperaria que funcionasse no Developer/Enterprise Edition para cenários diretos e consegui que funcionasse com um esquema e uma consulta simples:

    -- Indexed view matching
USE tempdb
GO

IF OBJECT_ID('dbo.BaseTable') IS NOT NULL DROP TABLE dbo.BaseTable
GO

CREATE TABLE dbo.BaseTable (

    rowId       INT IDENTITY PRIMARY KEY,

    slot        INT NOT NULL,
    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
)
GO


-- Add dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.BaseTable ( slot, someDate, someData )
SELECT
    rn % 999,
    DATEADD( day, rn % 333, '1 Jan 2016' ), 
    NEWID()
FROM cte

CHECKPOINT
GO --10


DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

CREATE VIEW dbo.IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;
GO

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);
GO


-- Indexed view matching
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO


-- Indexed view not used as it can't 'cover' the query
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
AND rowId = 42
OPTION ( RECOMPILE );
GO


-- Query Indexed view directly with NOEXPAND hint
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView WITH ( NOEXPAND )
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO


-- EXPAND VIEWS hint ensures Indexed view is NOT used.
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView 
WHERE Slot = @slot
OPTION ( RECOMPILE, EXPAND VIEWS );
GO

    Plano de execução:

    Correspondência de visualização indexada

    Existem algumas outras consultas no meu script de teste acima e você pode ver que a exibição indexada não é usada para algumas delas.

    Aqui estão algumas citações sobre exibições indexadas de um dos arquitetos do otimizador de consulta, Conor Cunningham:

    O otimizador de consulta contém lógica para usar esse índice nos casos em que o texto da consulta original referenciou explicitamente a exibição, bem como nos casos em que o usuário envia uma consulta que usa os mesmos componentes da exibição (em qualquer ordem equivalente).

    mas ...

    [complexidade] ... torna difícil para o otimizador de consulta considerar planos que executam primeiro a avaliação da exibição e, em seguida, processam o restante da consulta. A correspondência arbitrária de árvores é um problema computacionalmente complexo e o conjunto de recursos de visualizações é muito grande para executar essa operação com eficiência.

    'Internos do Microsoft SQL Server 2008', Capítulo 8: O otimizador de consultas - Conor Cunningham

    ...exibições indexadas funcionam para consultas SPJG básicas (SELECT-PROJECT-JOIN-GROUPBY). As uniões devem ser uniões internas, não autojunções, e devem ser unificadas em uma chave. Cada operadora tem uma série de restrições...

    fonte

    Acho que essas citações são bastante reveladoras. Você também pode querer considerar índices filtrados.

    • 6

relate perguntas