Início / dba / Perguntas / 295468
Accepted
Avi
Asked: 2021-07-10 07:08:43 +0800 CST

Dimensão eficiente e junção de fatos

  • 772

Eu tenho uma tabela de fatos grande e uma tabela de dimensões muito menor em um esquema em estrela simples:

--1.
CREATE TABLE dbo.Dim
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(2000)
)
--index
CREATE UNIQUE NONCLUSTERED INDEX uniqueindex1 ON Dim(CustomerName);


--2. 
CREATE TABLE dbo.Fact
(
...
PurchaseDate DATE 
CustomerNameId INT CONSTRAINT fk1 FOREIGN KEY (CustomerNameId) REFERENCES dbo.Dim(Id)
...
)
--index
CREATE CLUSTERED COLUMNSTORE INDEX ccs ON dbo.Fact;

Executando a seguinte consulta simples, que filtra na tabela de fatos e une na dimensão:

SELECT sd.CustomerName,f.*
FROM dbo.Fact f
INNER JOIN dbo.Dim sd ON sd.Id = f.CustomerNameId
WHERE f.PurchaseDate IN (
'20000506',
'20000507',
'20000508',
'20000509',
'20000501',
'20000502',
'20000503'
)

Obtemos o seguinte plano de consulta feio: insira a descrição da imagem aqui

Curiosamente, a tabela de dimensões tende a varrer TODAS as suas 500.000 linhas em 4 iterações, mas no final apenas alguns milhares são necessários nesse intervalo de datas da tabela de fatos.

Isso é muito ineficiente com tabelas de dimensões maiores, basicamente todas as linhas verificadas o tempo todo, como os índices da tabela de pesquisa nem estão lá.

O esperado seria que o sql server primeiro limitasse a tabela de fatos no intervalo de datas e, em seguida, usando esse intervalo limitado de CustomerKeyId, ele procurasse o CustomerName da tabela de dimensão pequena usando uma busca de índice.

  1. Isso é realmente o quão ineficiente é o esquema em estrela ou há algo que sinto falta aqui?
  2. Em outras palavras, como eu poderia forçar o sql server a preparar a tabela CustomerKeyId limitada e pesquisar apenas aquelas? (com CTE de alguma forma?)
index-tuning sql-server-2017

1 respostas

  1. Best Answer

    Aqui está uma amostra para brincar:

    --1.
CREATE TABLE dbo.Dim
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(2000)
)
--index
CREATE UNIQUE NONCLUSTERED INDEX uniqueindex1 ON Dim(CustomerName);

with q as
(
   select top 100000 row_number() over (order by (select null)) rn
   from sys.messages m, sys.objects o
)
insert into dim(CustomerName) 
select concat('CustomerName',rn)
from q

--2. 

CREATE TABLE dbo.Fact
(
PurchaseDate DATE,
CustomerNameId INT CONSTRAINT fk1 FOREIGN KEY (CustomerNameId) REFERENCES dbo.Dim(Id)
)
--index
CREATE CLUSTERED COLUMNSTORE INDEX ccs ON dbo.Fact;


with q as
(
   select top 10000000 row_number() over (order by (select null)) rn
   from sys.messages m, sys.objects o
)
insert into Fact(PurchaseDate,CustomerNameId) 
select dateadd(day,rn%1000,'20000101'), 1+rn%100000
from q


SELECT sd.CustomerName,f.*
FROM dbo.Fact f
INNER JOIN dbo.Dim sd ON sd.Id = f.CustomerNameId
WHERE f.PurchaseDate IN (
'20000506',
'20000507',
'20000508',
'20000509',
'20000501',
'20000502',
'20000503'
)


SELECT sd.CustomerName,f.*
FROM dbo.Fact f
INNER LOOP JOIN dbo.Dim sd ON sd.Id = f.CustomerNameId
WHERE f.PurchaseDate IN (
'20000506',
'20000507',
'20000508',
'20000509',
'20000501',
'20000502',
'20000503'
)

    O plano está aqui .

    Você verá que a junção de loop com a busca de índice é mais cara do que varrer a dimensão em cada thread da execução paralela e fazer uma junção de hash:

    (70000 rows affected)

 SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 64 ms.

(70000 rows affected)

 SQL Server Execution Times:
   CPU time = 108 ms,  elapsed time = 90 ms.
    • 1

relate perguntas