SpeedBirdNine's questions

Eu tenho um cenário onde tenho Datedimensão e Timedimensão junto com algumas outras dimensões. A dimensão de tempo contém apenas Hoursporque não houve necessidade de salvar dados em minuto ou segundo nível.

Uma situação exige que eu calcule o valor Averagede Maxcertas horas do dia. Por exemplo, preciso Maxdo valor entre 01:00 e 04:00 horas e a média desses valores máximos no período de tempo determinado (01 de janeiro de 2015 - 03 de janeiro de 2015 neste exemplo).

Estou filtrando Datee Timedimensões (juntamente com outras dimensões) como Subselect Fromcláusulas em vez de usar Where.

Se os Timemembros da dimensão permanecerem os mesmos para cada valor de Datedimensão selecionado, então Max, Avge StDevfornecer o resultado correto, mas se eu tiver que excluir algumas horas de um dos Datemembros da dimensão, Maxresultará em erro Avge StDevfornecerá valores incorretos.

Consulte o código SQL abaixo para criar o exemplo que usarei nesta pergunta:

create database TestDateTimeDimensions
GO

USE [TestDateTimeDimensions]
GO

create table DimDate (
DateId int not null PRIMARY KEY,
Date [Date] not null
)


insert into DimDate
select 20150101, CONVERT(DATE, '2015-01-01')
UNION ALL
select 20150102, CONVERT(DATE, '2015-01-02')
UNION ALL
select 20150103, CONVERT(DATE, '2015-01-03')
UNION ALL
select 20150104, CONVERT(DATE, '2015-01-04')

create table DimTime(
TimeId int not null PRIMARY KEY,
[Time] Time not null
)

insert into DimTime
select 0, CONVERT(TIME, '00:00:00')
UNION ALL
select 1, CONVERT(TIME, '01:00:00')
UNION ALL
select 2, CONVERT(TIME, '02:00:00')
UNION ALL
select 3, CONVERT(TIME, '03:00:00')
UNION ALL
select 4, CONVERT(TIME, '04:00:00')
UNION ALL
select 5, CONVERT(TIME, '05:00:00')
UNION ALL
select 6, CONVERT(TIME, '06:00:00')
UNION ALL
select 7, CONVERT(TIME, '07:00:00')
UNION ALL
select 8, CONVERT(TIME, '08:00:00')
UNION ALL
select 9, CONVERT(TIME, '09:00:00')
UNION ALL
select 10, CONVERT(TIME, '10:00:00')
UNION ALL
select 11, CONVERT(TIME, '11:00:00')
UNION ALL
select 12, CONVERT(TIME, '12:00:00')
UNION ALL
select 13, CONVERT(TIME, '13:00:00')
UNION ALL
select 14, CONVERT(TIME, '14:00:00')
UNION ALL
select 15, CONVERT(TIME, '15:00:00')
UNION ALL
select 16, CONVERT(TIME, '16:00:00')
UNION ALL
select 17, CONVERT(TIME, '17:00:00')
UNION ALL
select 18, CONVERT(TIME, '18:00:00')
UNION ALL
select 19, CONVERT(TIME, '19:00:00')
UNION ALL
select 20, CONVERT(TIME, '20:00:00')
UNION ALL
select 21, CONVERT(TIME, '21:00:00')
UNION ALL
select 22, CONVERT(TIME, '22:00:00')
UNION ALL
select 23, CONVERT(TIME, '23:00:00')

CREATE TABLE Fact (
Id int identity not null PRIMARY KEY,
DateId int not null REFERENCES DimDate(DateId),
TimeId int not null REFERENCES DimTime(TimeId),
value int not null
)

insert into Fact

select DateId, TimeId, ROUND(RAND(CONVERT(varbinary, NEWID())) * 100, 2)
FROM DimDate, DimTime

A MDXconsulta que funciona corretamente é:

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))

SET DateSet as EXISTING [Dim Date].[Date Id].[Date Id]
SET TimeSet as EXISTING [Dim Time].[Time Id].[Time Id]

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax
}
on 0,
{
    DateSet * TimeSet
} on 1

FROM 
(
    SELECT ({[Dim Date].[Date Id].&[20150101] : [Dim Date].[Date Id].&[20150103]}, {[Dim Time].[Time Id].&[1] : [Dim Time].[Time Id].&[4]})
    on 0
    FROM [Test Date Time Dimensions]
)

Ele exibe o resultado corretamente como:

Observe que os itens incluídos no Eixo 1 não são usados ​​e a consulta real retorna apenas uma linha. Eles são apenas para este exemplo.

A consulta que não funciona como esperado é:

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))

SET DateSet as EXISTING [Dim Date].[Date Id].[Date Id]
SET TimeSet as EXISTING [Dim Time].[Time Id].[Time Id]

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax
}
on 0

,
{
    DateSet * TimeSet
} on 1

FROM 
(
    SELECT {({[Dim Date].[Date Id].&[20150101] : [Dim Date].[Date Id].&[20150102]} * {[Dim Time].[Time Id].&[1] : [Dim Time].[Time Id].&[4]})
    , ([Dim Date].[Date Id].&[20150103] * [Dim Time].[Time Id].&[3] : [Dim Time].[Time Id].&[4])}
    on 0
    FROM [Test Date Time Dimensions]
)

Aqui, excluí Horas 01:00e a coluna '02:00 from Date3 January 2015 . The maximum value (70) is at hour01:00 of3 January 2015 but is still being used to calculate Avg andStDev , andMax` está dando erro, conforme mostra a imagem:

Aqui, Média e Desvio Padrão estão incorretos.

Por favor, ajude-me a encontrar uma maneira de calcular corretamente os resultados neste segundo caso e explique por que Max está dando erro e Avgas StDevfunções ainda estão calculando, incluindo o valor máximo que não está incluído na Subselectcláusula filtrada.

Uma solução alternativa que não é ideal é adicionar uma nova DateTimedimensão que tenha Datee Time, e usar essa DateTimedimensão na Subselectcláusula e utilizar a Timedimensão existente para calcular Maxe Datedimensionar para obter média e desvio padrão, como no código abaixo :

Crie e preencha a DateTimedimensão:

create table DimDateTime
(
    DateTimeId int not null PRIMARY KEY,
    [Date] Date not null,
    [Time] Time not null 
)

insert into DimDateTime

select ((DateId * 100) + TimeId) DateTimeId, DimDate.Date, DimTime.Time
from DimDate, DimTime

alter table Fact add DateTimeId int References DimDateTime(DateTimeId)

update Fact set DateTimeId = ((DateId * 100) + TimeId)

A MDXconsulta retornando o resultado correto:

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))

SET DateSet as NONEMPTY ([Dim Date].[Date Id].[Date Id])
SET TimeSet as NONEMPTY ([Dim Time].[Time Id].[Time Id])

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax
}
on 0,
{
    NONEMPTY(DateSet * TimeSet)
} on 1

FROM 
(
    SELECT ({[Dim Date Time].[Date Time Id].&[2015010101] : [Dim Date Time].[Date Time Id].&[2015010104],
            [Dim Date Time].[Date Time Id].&[2015010201] : [Dim Date Time].[Date Time Id].&[2015010204],
            [Dim Date Time].[Date Time Id].&[2015010303] : [Dim Date Time].[Date Time Id].&[2015010304]})
    on 0
    FROM [Test Date Time Dimensions]
)

Mas prefiro evitar a criação de uma nova dimensão, além das duas dimensões existentes contendo os mesmos dados. Em segundo lugar, quero evitar colocar os dados no aplicativo e calcular o resultado nos métodos da API. Então a preferência é fazer com que as funções Maxfuncionem corretamente.AvgStDev

Usando o SQLServer 2012.

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:

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:

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:

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:

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.

Eu estava experimentando índices para acelerar as coisas, mas no caso de uma junção, o índice não está melhorando o tempo de execução da consulta e, em alguns casos, está diminuindo a velocidade.

A consulta para criar a tabela de teste e preenchê-la com dados é:

CREATE TABLE [dbo].[IndexTestTable](
    [id] [int] IDENTITY(1,1) PRIMARY KEY,
    [Name] [nvarchar](20) NULL,
    [val1] [bigint] NULL,
    [val2] [bigint] NULL)

DECLARE @counter INT;
SET @counter = 1;

WHILE @counter < 500000
BEGIN
    INSERT INTO IndexTestTable
      (
        -- id -- this column value is auto-generated
        NAME,
        val1,
        val2
      )
    VALUES
      (
        'Name' + CAST((@counter % 100) AS NVARCHAR),
        RAND() * 10000,
        RAND() * 20000
      );

    SET @counter = @counter + 1;
END

-- Index in question
CREATE NONCLUSTERED INDEX [IndexA] ON [dbo].[IndexTestTable]
(
    [Name] ASC
)
INCLUDE (   [id],
    [val1],
    [val2])

Agora a consulta 1, que foi melhorada (apenas um pouco, mas a melhoria é consistente) é:

SELECT *
FROM   IndexTestTable I1
       JOIN IndexTestTable I2
            ON  I1.ID = I2.ID
WHERE  I1.Name = 'Name1'

Estatísticas e plano de execução sem índice (neste caso, a tabela usa o índice clusterizado padrão):

(5000 row(s) affected)
Table 'IndexTestTable'. Scan count 2, logical reads 5580, 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 = 109 ms,  elapsed time = 294 ms.

Agora com o índice ativado:

(5000 row(s) affected)
Table 'IndexTestTable'. Scan count 2, logical reads 2819, 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 = 94 ms,  elapsed time = 231 ms.

Agora, a consulta que fica mais lenta devido ao índice (a consulta não tem sentido, pois é criada apenas para teste):

SELECT I1.Name,
       SUM(I1.val1),
       SUM(I1.val2),
       MIN(I2.Name),
       SUM(I2.val1),
       SUM(I2.val2)
FROM   IndexTestTable I1
       JOIN IndexTestTable I2
            ON  I1.Name = I2.Name
WHERE   
       I2.Name = 'Name1'
GROUP BY
       I1.Name

Com o índice clusterizado ativado:

(1 row(s) affected)
Table 'IndexTestTable'. Scan count 4, logical reads 60, 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.
Table 'Worktable'. Scan count 1, logical reads 155106, 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 = 17207 ms,  elapsed time = 17337 ms.

Agora com o Index desabilitado:

(1 row(s) affected)
Table 'IndexTestTable'. Scan count 5, logical reads 8642, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 165212, 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 = 17691 ms,  elapsed time = 9073 ms.

As perguntas são:

1. Mesmo que o índice seja sugerido pelo SQL Server, por que ele retarda as coisas por uma diferença significativa?
2. Qual é a junção Nested Loop que está demorando mais e como melhorar seu tempo de execução?
3. Há algo que estou fazendo de errado ou deixei passar?
4. Com o índice padrão (somente na chave primária), por que leva menos tempo e com o índice não clusterizado presente, para cada linha na tabela de junção, a linha da tabela unida deve ser encontrada mais rapidamente, porque a junção está na coluna Nome na qual o índice foi criado. Isso se reflete no plano de execução da consulta e o custo de Index Seek é menor quando o IndexA está ativo, mas por que ainda mais lento? Além disso, o que está na junção externa esquerda do loop aninhado que está causando a lentidão?

Usando o SQLServer 2012