主页 / dba / 问题 / 295468
Accepted
Avi
Asked: 2021-07-10 07:08:43 +0800 CST

高效的维度和事实连接

  • 772

我有一个大的事实表和一个简单星型模式中的小得多的维度表：

--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;

运行以下简单查询，过滤事实表并加入维度：

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'
)

我们得到以下丑陋的查询计划： 在此处输入图像描述

有趣的是，维度表倾向于在 4 次迭代中扫描所有 500 000 行，但最终在事实表的该日期范围内只需要几千行。

这对于较大的维度表来说是非常低效的，基本上所有的行都是一直扫描的，就像查找表索引甚至不存在一样。

预期的事情是sql server首先将事实表限制在日期范围内，然后使用这个有限的CustomerKeyId范围，它使用索引查找从小维度表中查找CustomerName。

  1. 这真的是星型模式的效率低下，还是我在这里想念什么？
  2. 换句话说，我怎么能强制 sql server 准备有限的 CustomerKeyId 表并只查找那些？（不知何故有CTE？）
index-tuning sql-server-2017

1 个回答

  1. Best Answer

    这是一个可以玩的示例：

    --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'
)

    计划在这里。

    您会看到使用索引查找的循环连接比扫描并行执行的每个线程上的维度并执行哈希连接更昂贵：

    (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

相关问题