sql - 如何在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。