如何在Sql Server中为group by构造索引

Question

下面的简单查询需要很长时间（几分钟）来执行。

我有一个索引：

create index IX on [fctWMAUA] (SourceSystemKey, AsAtDateKey)



SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
FROM [fctWMAUA] (NOLOCK) AS [t0]
WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)
GROUP BY [t0].[SourceSystemKey]

统计信息如下：

• 逻辑读取1827978
• 物理读取1113
• 提前阅读1806459

采取完全相同的查询并将其重新格式化为以下格式，可以得到以下统计信息：

• 逻辑读取36
• 物理读取0
• 提前阅读0

执行需要31毫秒。

SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 1
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 2
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]
/* AND SO ON TO 9 */

我如何快速建立索引以进行分组？

Answer 1

尝试告诉SQL Server使用索引：

...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...

确保该表的统计信息是最新的：

UPDATE STATISTICS [fctWMAUA]

为了获得更好的答案，请同时打开两个查询的显示计划：

SET SHOWPLAN_TEXT ON

并将结果添加到您的问题中。

您也可以在没有GROUP BY的情况下编写查询。例如，您可以使用排他的LEFT JOIN排除具有较早日期的行：

select cur.SourceSystemKey, cur.date
from fctWMAUA cur
left join fctWMAUA next
    on next.SourceSystemKey = next.SourceSystemKey
    and next.date > cur.date
where next.SourceSystemKey is null
and cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)

这可能出乎意料的快，但是我认为它不能击败UNION。