如何在Sql Server中为组构建一个索引

本文介绍了如何在Sql Server中为组构建一个索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 以下简单的查询需要很长时间（几分钟）才能执行。 我有一个索引： 在[fctWMAUA]（SourceSystemKey，AsAtDateKey）上创建索引IX $ b SELECT MAX（[t0]。[AsAtDateKey]） [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 / li> 需要31ms t o执行。 $ b SELECT MAX（[t0]。[AsAtDateKey]）AS [Date]， [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 * / 如何快速创建组的索引？解决方案尝试告诉SQL Server使用索引： FROM [fctWMAUA]（NOLOCK，INDEX（IX））AS [t0] ... 确保表格的统计数据是最新的： UPDATE STATISTICS [fctWMAUA] 要获得更好的答案，请为这两个查询启用showplan： SET SHOWPLAN_TEXT ON 并将结果添加到您的问题中。 您也可以在不使用GROUP BY的情况下编写查询。例如，您可以使用专有的LEFT JOIN排除具有较早日期的行： 选择cur.SourceSystemKey，cur.date from fctWMAUA cur left join fctWMAUA next on next.SourceSystemKey = next.SourceSystemKey and next.date> cur.date 其中nextSourceSystemKey为空和cur.SourceSystemKey（1,2,3,4,5,6,7,8,9） 这可能会非常快，但我认为它不会超过联盟。 The following simple query takes a very long time (several minutes) to execute.I have an index: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]The statistics are as follows:logical reads 1827978physical reads 1113read aheads 1806459Taking that exact same query and reformatting it as follows gives me these statistics:logical reads 36physical reads 0read aheads 0It takes 31ms to execute.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 */How do I make an index that does the group by quickly? 解决方案 Try to tell SQL Server to use the index:...FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]...Make sure the statistics for the table are up to date:UPDATE STATISTICS [fctWMAUA]For better answers, turn on the showplan for both queries:SET SHOWPLAN_TEXT ONand add the results to your question.You can also write the query without a GROUP BY. For example, you can use an exclusive LEFT JOIN excluding rows with older dates:select cur.SourceSystemKey, cur.datefrom fctWMAUA curleft join fctWMAUA next on next.SourceSystemKey = next.SourceSystemKey and next.date > cur.datewhere next.SourceSystemKey is nulland cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)This can be surprisingly fast, but I don't think it could beat the UNION. 这篇关于如何在Sql Server中为组构建一个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！