主页 / dba / 问题 / 84577
Accepted
Shaul Behr
Asked: 2014-12-09 01:05:51 +0800 CST

使用完全外部联接对视图进行索引优化

  • 772

表结构：

Foo             FooChild                Bar
---             --------                ---
ID              ID                      ID
Date            FooID                   Date
GroupID         UserID                  UserID
Notes           Amount                  GroupID
                                        IsComplete
  • FooDate在+上有唯一索引GroupID
  • FooChildFooID有一个到 Foo 的 FK，以及一个关于+的唯一索引UserID，其中包括Amount
  • BarDate在++上有一个唯一索引UserID，GroupID其中包括IsComplete

现在我需要创建一个报告，显示所有 FooChild 金额的总和以及任何给定日期范围内完整条形图的数量。用户还希望能够查看每个组或每个用户的统计信息。这似乎是编写视图的好地方：

create view vFooBar as
select f.Date, f.GroupID, fc.UserID, fc.Amount, b.IsComplete
from Foo f join FooChild fc on fc.FooID = f.ID
left join Bar b on f.Date = b.Date and f.GroupID = b.GroupID and fc.UserID = b.UserID
union
select b.Date, b.GroupID, b.UserID, x.Amount, b.IsComplete
from Bar b left join 
    (select f.Date, f.GroupID, fc.UserID, fc.Amount
    from Foo f join FooChild fc on fc.FooID = f.ID) x
on x.Date = b.Date and x.GroupID = b.GroupID and x.UserID = b.UserID

（这就是我以这种方式编写视图的原因。）

现在我可以轻松地编写这样的查询：

select UserID, sum(Amount) FooAmount, sum(cast(IsCompleted as int)) CompletedBars
from vFooBar
where Date between @fromDate and @toDate
group by UserID

但是这里有一个障碍。一旦日期范围开始变得相对较大，执行计划就会变成梨形。它在 上使用日期索引Foo，但不是在FooID上使用索引FooChild，而是执行聚集索引扫描，然后进行哈希匹配FooID以与 上的结果连接Foo。它在总体计划中做了两次；我猜每个聚合一次。那真的很痛。

我知道使用我创建的索引FooChild可能效率不高，因为FooID给定日期的值可能是离散的，尽管通常它们以大致相同的顺序插入。

我可以非规范化，将 and 添加Date到GroupIDFooChild 表，然后索引这些列，我很确定这会大大提高性能。但这感觉不对。

还有其他想法吗？

index sql-server-2012

1 个回答

  1. Best Answer

    优化器根据成本估算做出选择。成本模型是通用的，可能并不总是为您的特定硬件选择最佳计划，并且其假设可能并不总是对您的情况有效。

    在这种情况下，当估计要连接的行数很大时，优化器将散列连接评估为比嵌套循环更便宜的选择。如果您确定嵌套循环连接总是比散列连接更可取，您可以考虑（并测试！）强制查找而不是扫描FooChild视图中的表：

    SELECT 
    f.TheDate, 
    f.GroupID, 
    fc.UserID, 
    fc.Amount, 
    b.IsComplete
FROM dbo.Foo AS f 
JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
    ON fc.FooID = f.ID
LEFT JOIN dbo.Bar AS b 
    ON f.TheDate = b.TheDate 
    AND f.GroupID = b.GroupID 
    AND fc.UserID = b.UserID
UNION
SELECT 
    b.TheDate, 
    b.GroupID, 
    b.UserID, 
    x.Amount, 
    b.IsComplete
FROM dbo.Bar AS b 
LEFT JOIN 
(
    SELECT 
        f.TheDate, 
        f.GroupID, 
        fc.UserID, 
        fc.Amount
    FROM dbo.Foo AS f 
    JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
        ON fc.FooID = f.ID
) AS x
    ON x.TheDate = b.TheDate 
    AND x.GroupID = b.GroupID 
    AND x.UserID = b.UserID;

    旁注：考虑到您表的当前唯一性约束，虽然从原始完全连接进行的这种转换是有效的，但请查看您上一个问题的答案并考虑按照我的编辑中的建议重写完整连接。

    • 3

相关问题