Início / dba / Perguntas / 52845
Accepted
SpeedBirdNine
Asked: 2013-11-06 12:49:20 +0800 CST

O que pode ser feito para aprimorar ainda mais o desempenho de consultas de junção múltipla e agregação?

  • 772

Eu tenho um típico esquema em estrela simulado aqui e estou mencionando duas consultas: a primeira consulta simplesmente une a tabela de fatos com 2 tabelas de dimensão e 1 tabela de calendário, e a segunda consulta une e agrega.

Eu experimentei e criei índices estudando o plano de execução e alguns lendo os índices sugeridos e todos eles melhoraram o desempenho em alguma extensão.

Minha dúvida é o que mais pode ser feito neste caso, quais índices podem ser aplicados ou como a consulta pode ser modificada para obter melhor desempenho e reduzir o tempo de execução?

Então primeiro a consulta para criar e preencher as tabelas e para criar os Índices:

CREATE TABLE FactTable (id BIGINT IDENTITY PRIMARY KEY, FKDim1 BIGINT NOT NULL, FKDim2 BIGINT, DateRef DATETIME, Fact1 MONEY, Fact2 MONEY)
CREATE TABLE Dim1Table (id BIGINT IDENTITY PRIMARY KEY, Dim1Name NVARCHAR(20), Dim1Val1 MONEY, Dim1Val2 MONEY)
CREATE TABLE Dim2Table (id BIGINT IDENTITY PRIMARY KEY, Dim2Name NVARCHAR(20), Dim2Val1 MONEY, Dim2Val2 MONEY)
CREATE TABLE CalendarTable (id BIGINT IDENTITY PRIMARY KEY, [Date] DATETIME UNIQUE NONCLUSTERED, [Weekday] NVARCHAR(10), [Month] NVARCHAR(10))

ALTER TABLE FactTable ADD CONSTRAINT FK_Dim1 FOREIGN KEY (FKDim1 ) REFERENCES Dim1Table(ID);
ALTER TABLE FactTable ADD CONSTRAINT FK_Dim2 FOREIGN KEY (FKDim2 ) REFERENCES Dim1Table(ID);
ALTER TABLE FactTable ADD CONSTRAINT FK_Calendar FOREIGN KEY (DateRef) REFERENCES CalendarTable([Date]);

DECLARE @counter INT;
SET @counter = 1;

WHILE @counter < 10000
BEGIN
INSERT INTO Dim1Table(Dim1Name,Dim1Val1,Dim1Val2)VALUES('Dim1-'+CAST((@counter % 100) AS NVARCHAR),RAND() * 10000,RAND() * 20000);
INSERT INTO Dim2Table(Dim2Name,Dim2Val1,Dim2Val2)VALUES('Dim2-'+CAST(@counter AS NVARCHAR),RAND() * 10000,RAND() * 20000);
SET @counter = @counter + 1;
END

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = CAST('1/1/1995' AS DATETIME)
SET @EndDate = DATEADD(d, 3650, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO CalendarTable([Date],[Weekday],[Month])SELECT @StartDate, DATENAME(dw, @StartDate), DATENAME(MONTH, @StartDate)
SET @StartDate = DATEADD(dd, 1, @StartDate)
END

SET @counter = 1;
WHILE @counter < 500000
BEGIN
INSERT INTO FactTable
(FKDim1,FKDim2,DateRef,Fact1,Fact2)VALUES(@counter % 10000,@counter % 10000, DATEADD(dd, @counter % 3650, CAST('1/1/1995' AS DATETIME)), RAND() * 10000, RAND() * 20000)
SET @counter = @counter + 1
END

Código para criar índices:

CREATE NONCLUSTERED INDEX [Dim1TableIndex1] ON [dbo].[Dim1Table]([Dim1Name] ASC)INCLUDE([id], [Dim1Val1], [Dim1Val2]);
CREATE NONCLUSTERED INDEX [Dim1TableIndex2] ON [dbo].[Dim2Table]([Dim2Name] ASC)INCLUDE([id], [Dim2Val1], [Dim2Val2]);
CREATE NONCLUSTERED INDEX [FactTableIndex1] ON [dbo].FactTable(FKDim1 ASC)INCLUDE(FKDim2, DateRef, Fact1, Fact2);
CREATE NONCLUSTERED INDEX [FactTableIndex2] ON [dbo].FactTable(FKDim2 ASC)INCLUDE(FKDim1, DateRef, Fact1, Fact2);
CREATE UNIQUE NONCLUSTERED INDEX [CalnedarIndex1] ON [dbo].[CalendarTable]([Date] ASC)INCLUDE ([id],[Weekday],[Month]);

Consulta 1: junção simples da tabela de fatos com as tabelas de calendário e dimensão e uma cláusula where:

SELECT D1.Dim1Name,
       D2.Dim2Name,
       C.[Date],
       C.[Weekday],
       C.[Month],
       D1.Dim1Val1,
       D2.Dim2Val2,
       F.Fact1,
       F.Fact2
FROM   FactTable F
       JOIN Dim1Table D1
            ON  D1.id = F.FKDim1
       JOIN Dim2Table D2
            ON  D2.id = F.FKDim2
       JOIN CalendarTable C
            ON  F.DateRef = C.Date

Detalhes de execução com índices desativados (todos os 5 mencionados acima)

    (15000 row(s) affected)
Table 'CalendarTable'. Scan count 9, logical reads 82, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 9, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim1Table'. Scan count 9, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 9, logical reads 3890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 159 ms,  elapsed time = 475 ms.

E plano de execução: insira a descrição da imagem aqui

Com índices ativados:

(15000 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 'FactTable'. Scan count 300, logical reads 1083, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim1Table'. Scan count 3, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CalendarTable'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 389 ms.

E Plano de Execução: insira a descrição da imagem aqui

Segunda consulta, que agrega após a junção:

SELECT D1.Dim1Name,
       C.[Month],
       Sum(D1.Dim1Val1) SumDim1Val1,
       Sum(D2.Dim2Val2) SumDim2Val2,
       Sum(F.Fact1) SumFact1,
       Avg(F.Fact2) Fact2Avg
FROM   FactTable F
       JOIN Dim1Table D1
            ON  D1.id = F.FKDim1
       JOIN Dim2Table D2
            ON  D2.id = F.FKDim2
       JOIN CalendarTable C
            ON  F.DateRef = C.Date
GROUP BY D1.Dim1Name, C.[MONTH]

Desempenho com todos os índices desativados:

(1200 row(s) affected)
Table 'Dim1Table'. Scan count 9, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CalendarTable'. Scan count 9, logical reads 82, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 9, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 9, logical reads 3890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2436 ms,  elapsed time = 554 ms.

E Plano de Execução: insira a descrição da imagem aqui

E com índices ativados:

(1200 row(s) affected)
Table 'Dim1Table'. Scan count 9, logical reads 181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CalendarTable'. Scan count 9, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 9, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 9, logical reads 3710, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2060 ms,  elapsed time = 518 ms.

E finalmente o plano de execução: insira a descrição da imagem aqui

As melhorias que obtive não são muito significativas, mas quando considero um grande número de linhas, por exemplo, remova a cláusula where da consulta 1, os índices reduzem o tempo de execução de cerca de 9,5 segundos para 8,3 segundos.

Vou reapresentar minhas dúvidas aqui:

  1. Como os índices podem ser redesenhados ou novos índices adicionados para melhorar o desempenho?
  2. Como o desempenho pode ser aprimorado reprojetando as consultas?
  3. O que pode ser feito além de índices e redesenho das consultas?

Apresentei exemplos simples, mas tentei cobrir alguns cenários típicos e tipos de consultas em um esquema em estrela, pois o conceito por trás das respostas dessas perguntas específicas também se aplicará de maneira geral. E usando o SQL Server 2012.

sql-server index

2 respostas

  1. Best Answer

    Raramente há qualquer necessidade, ponto ou benefício tentando micro otimizar consultas de esquema em estrela com índices não agrupados carregados com colunas incluídas. As tabelas de fatos são construídas para serem verificadas.

    Os índices que você criou em seus exemplos são cópias de subconjuntos da tabela pai, que estão sendo verificadas (sem buscas). As pequenas melhorias de desempenho vêm da digitalização de páginas marginalmente menores do que a tabela pai. Dado que os esquemas em estrela são construídos para suportar padrões de consulta ad hoc, não é viável criar os índices para suportar todas as consultas possíveis.

    • Crie seu índice clusterizado da tabela de fatos na chave de data. A maioria das consultas (típicas) da tabela de fatos inclui um elemento de tempo e o agrupamento na chave de data permite a varredura de intervalo das linhas da tabela de fatos.
    • Adicione índices não agrupados nas chaves estrangeiras de suas tabelas de fatos para auxiliar em consultas altamente seletivas. Chaves estrangeiras para tabelas de dimensão podem ser criadas com NOCHECK para evitar qualquer impacto no ETL.
    • Agrupe suas tabelas de dimensão em suas chaves substitutas.
    • Crie um índice não clusterizado na chave natural de cada tabela de dimensão.
    • Pare.

    O otimizador detecta padrões de consulta de esquema em estrela e tem estratégias para lidar com eles de forma eficiente, utilizando varreduras e hash joins no Standard Edition ou filtragem de bitmap no Enterprise. Siga a estratégia de indexação descrita acima e deixe o otimizador lidar com o resto.

    • 8

  2. Além da excelente resposta de Mark, existem algumas outras estratégias que você pode adicionar ao seu sistema existente (esta não é uma lista exaustiva, é claro):

    1. Tabelas pré-agregadas ou exibições indexadas. Isso materializará fisicamente os resultados (ou resultados intermediários) da consulta, de modo que o SQL Server acabará verificando índices muito menores para retornar o conjunto de resultados completo. Isso mantém seu projeto dentro do mesmo banco de dados, usando a tecnologia que você conhece.

    2. Serviços de análise. Pode valer a pena examinar isso se o plano for oferecer suporte a muitos cortes e cortes dos dados. O Analysis Services foi criado para pré-agregar os dados automaticamente de acordo com os parâmetros inseridos. A desvantagem disso é que provavelmente é uma tecnologia totalmente nova para você. Embora não seja um especialista nessa área, direi que há uma curva de aprendizado. É uma ferramenta muito poderosa.

    3. Cache de resultados. Se não houver muitas linhas voltando e você descobrir que os usuários estão executando as mesmas consultas repetidamente, armazene os resultados em cache e invalide o cache quando novos dados forem carregados (ou descubra uma maneira de invalidar seletivamente com base em os novos dados).

    Dependendo dos requisitos exatos do seu projeto, eles podem não ser aplicáveis, mas oferecem benefícios de desempenho/tempo de resposta se puderem ser implementados (individualmente ou em combinação).

    • 5

relate perguntas