索引一次性临时表

Question

一位同事在一家使用Microsoft SQL Server的公司工作。他们的团队创建存储过程，每天执行这些存储过程以创建数据提取。底层表是巨大的（有些表有数十亿行），因此大多数存储过程都是这样设计的：首先，它们只将这些巨大表的相关行提取到临时表中，然后将临时表彼此连接，并与其他较小的表连接，以创建最终的提取。类似的东西：

SELECT COL1, COL2, COL3
INTO #TABLE1
FROM HUGETABLE1
WHERE COL4 IN ('foo', 'bar');

SELECT COL1, COL102, COL103
INTO #TABLE2
FROM HUGETABLE2
WHERE COL14 = 'blah';

SELECT COL1, COL103, COL306
FROM #TABLE1 AS T1
JOIN #TABLE2 AS T2
ON T1.COL1 = T2.COL1
LEFT JOIN SMALLTABLE AS ST
ON T1.COL3 = ST.COL3
ORDER BY T1.COL1;

通常，临时表在创建后不会被修改（因此没有后续的ALTER、UPDATE或INSERT操作）。出于本讨论的目的，让我们假设临时表稍后只使用一次（因此只有一个SELECT查询会依赖它们）。

问题是：在创建这些临时表之后，在随后的查询中使用它们之前，索引这些临时表是一个好主意吗？

我的同事认为，创建索引将使联接和排序操作更快。但是，我相信总时间会更长，因为索引创建需要时间。换句话说，我假设除了边缘情况（如临时表本身非常大，或者最终的 SELECT 查询非常复杂）之外，SQL Server 将使用它对临时表的统计信息来优化最终查询，这样做它将有效地为临时表编制索引。

换句话说，我习惯于认为创建索引只有在您知道经常使用表的情况下才有用；存储过程完成后删除的一次性临时表不值得索引。

我们俩都对SQL Server优化器知之甚少，不知道我们在哪些方面是对的，哪些方面是错误的。你能帮助我们更好地理解我们的哪些假设更接近真理吗？

Answer 1

如果您正在执行包含数十亿行的日常数据提取，我建议您使用临时表而不是临时表。这将使用 tempdb 将数据提取与其他资源隔离开来。

问题是：在创建这些临时表之后，在随后的查询中使用它们之前，索引这些临时表是一个好主意吗？

将数据加载到临时表后创建索引。这将消除碎片并创建统计数据。

优化器将使用统计数据生成最佳计划。因此，如果您没有统计数据，它可能会极大地影响查询性能，特别是对于大型数据集。

下面示例查询临时表中索引创建前后的比较：

/* Create index after data load into temp table -- stats is created */
CREATE TABLE #temp ( [text] varchar(50), [num] int);
INSERT INTO #temp([text], [num]) VALUES ('aaa', 1), ('bbb', 2) , ('ccc',3);
CREATE UNIQUE CLUSTERED INDEX [IX_num] ON #temp (num);
DBCC SHOW_STATISTICS ('tempdb..#temp', 'IX_num');

/* Create index before data load into temp table -- stats is not created */
CREATE TABLE #temp_nostats ( [text] varchar(50), [num] int);
CREATE UNIQUE CLUSTERED INDEX [IX_num] ON #temp_nostats (num);
INSERT INTO #temp_nostats([text], [num]) VALUES ('aaa', 1), ('bbb', 2) , ('ccc',3);
DBCC SHOW_STATISTICS ('tempdb..#temp_nostats', 'IX_num');

您需要测试索引是否对您有帮助。您需要平衡可以拥有的索引数，因为如果索引太多，它也会影响性能。

Answer 2

您的朋友可能是正确的，因为即使一个表将在一个查询中使用，在没有看到该查询的情况下(即使我们看到了，我们仍然不知道它的执行计划是什么样子的)，我们也不知道SQL Server将需要多少次在这些表的各个列中查找数据以进行连接、排序等。

然而，我们永远无法确定，直到它实际以两种方式完成，并测量和比较结果。