哪一个性能更好：派生表还是临时表？

Question

有时我们可以同时使用派生表和临时表来编写查询。我的问题是，哪一个更好？为什么？

- masoud ramezani

---

@tableVariables怎么样？;-) - KM.

@KM: 你在这里玩弄可怕的力量。 - Quassnoi

Answer 1

派生表是一个逻辑概念，可以在tempdb中存储，在每次访问时通过重新评估基础语句来构建，甚至可以被优化掉。

临时表是一个物理概念。它是在tempdb中创建并填充值的表。

哪个更好取决于它们所使用的查询、推导表所用的语句以及许多其他因素。

例如，SQL Server中的公共表达式(CTE)会在每次使用时被重新评估。以下查询:

WITH    q (uuid) AS
        (
        SELECT  NEWID()
        )
SELECT  *
FROM    q
UNION ALL
SELECT  *
FROM    q

很可能会产生两个不同的NEWID()。

在这种情况下，应该使用临时表，因为它保证了其值的持久性。

另一方面，这个查询：

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ) q
WHERE   rn BETWEEN 80 AND 100

使用派生表会更好，因为使用临时表会需要从master中获取所有值，而这个解决方案只需使用在id上的索引扫描前100条记录。

- Quassnoi

---

在哪些简单查询中使用派生表更好？ - masoud ramezani

你为什么在这里使用“很可能”？ - “很可能会产生两个不同的NEWID()。” - thewpfguy

@thewpfguy：因为这不是一种可保证的行为。 - Quassnoi

@Quassnoi 临时表需要从tempdb中获取所有值，而不是masterdb。 - sam

@Sam：什么是主数据库？ - Quassnoi

Answer 2

这要看具体情况。

派生表的优点：

1. 派生表是更大查询的一部分，将在整个查询的上下文中得到优化。如果查询优化有助于性能（通常会有帮助，但也有例外情况），这可能是一个优点。例如：如果你填充了一个临时表，然后在第二个查询中使用结果，实际上就将数据库引擎绑定到了一种执行方法（完全运行第一个查询，保存整个结果集，然后运行第二个查询），而使用派生表时，优化器可能能够找到更快的执行方法或访问路径。

2. 派生表只存在于查询执行计划中——它是纯粹的逻辑结构。实际上并没有表格存在。

临时表的优点：

1. 该表格“存在”——也就是说，在内存中，它被实体化为一个包含结果集的表格，并且可以重复使用。

2. 在某些情况下，当你必须对数据进行一些复杂的转换时，性能可能会得到改善或阻塞会减少——例如，如果你想从繁忙的基础表中获取一个“Snapshot”行集，然后对该集合进行一些复杂的计算，在尽可能快地获取基础表中的行并将其解锁后，独立完成工作可能会减少竞争。在某些情况下，相对于并发优势，真正的临时表的开销很小。

- onupdatecascade

Answer 3

我想在这里加入一个轶事，因为它引导我提出与被接受的答案相反的建议。我同意被接受的答案中提出的思路，但它大多是理论性的。我的经验使我建议使用临时表而不是派生表、公共表达式和表值函数。我们广泛使用派生表和公共表达式，并取得了很大的成功，这是基于与被接受的答案一致的思路，直到我们开始处理更大的结果集和/或更复杂的查询。然后我们发现优化器无法很好地优化派生表或CTE。

今天我看了一个运行了10:15的例子。我将派生表的结果插入到一个临时表中，在主查询中连接这个临时表，总时间降至0:03。通常，当我们遇到性能问题时，我们可以通过这种方式快速解决。因此，除非您的查询相对简单并且您确定它不会处理大数据集，否则我建议使用临时表。

- Jim Clark

Answer 4

最大的区别在于您可以在临时表上设置约束，包括主键。对于大型数据（我指的是数百万条记录），有时使用临时表可以获得更好的性能。我有一个需要5个连接的关键查询（每个连接都相似）。当我只有2个连接时，性能还可以，但第三个连接时性能变差，查询计划变得混乱。即使使用提示，我也无法纠正查询计划。尝试将连接重构为派生表，仍然存在相同的性能问题。使用临时表可以创建主键（然后在填充时按PK排序）。当SQL可以连接这5个表并使用PK时，性能从几分钟提高到几秒钟。我希望SQL支持派生表和CTE上的约束（即使只有PK）。

- paparazzo