主页 / dba / 问题 / 13112
Accepted
Rachel
Asked: 2012-02-16 08:47:27 +0800 CST

CTE 和临时表有什么区别？

  • 772

公用表表达式 (CTE) 和临时表有什么区别？我什么时候应该使用其中一个？

CTE

WITH cte (Column1, Column2, Column3)
AS
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte

温度表

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
sql-server cte

7 个回答

  1. Best Answer

    这是相当广泛的，但我会尽可能地给你一个笼统的答案。

    CTE...

    • 不可索引（但可以在引用对象上使用现有索引）
    • 不能有约束
    • 基本上都是一次性VIEW的
    • 仅持续到运行下一个查询
    • 可以递归
    • 没有专用的统计信息（依赖于底层对象的统计信息）

    #临时表...

    • 是存在于 tempdb 中的真实物化表
    • 可以索引
    • 可以有约束
    • 在当前 CONNECTION 的生命周期内持续存在
    • 可以被其他查询或子过程引用
    • 拥有引擎生成的专用统计数据

    至于何时使用它们，它们有非常不同的用例。如果您将有一个非常大的结果集，或者需要多次引用它，请将其放在一个#temp表中。如果它需要是递归的，是一次性的，或者只是为了在逻辑上简化某些东西，aCTE是首选。

    此外， a永远不CTE应该用于 performance。使用 CTE 几乎永远不会加快速度，因为它只是一次性视图。你可以用它们做一些巧妙的事情，但加速查询并不是其中之一。

    • 215

  2. 编辑：

    请参阅以下马丁的评论：

    CTE 没有具体化为内存中的表。它只是封装查询定义的一种方式。在 OP 的情况下，它将被内联并且与做SELECT Column1, Column2, Column3 FROM SomeTable. 大多数情况下，它们没有预先具体化，这就是为什么它不返回任何行WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X，还要检查执行计划。尽管有时可以破解计划以获得线轴。有一个连接项要求对此提供提示。——马丁·史密斯 2012 年 2 月 15 日 17:08

    原始答案

    CTE

    在 MSDN 上阅读更多信息

    CTE 创建在内存中使用的表，但仅对紧随其后的特定查询有效。使用递归时，这可能是一种有效的结构。

    您可能还想考虑使用表变量。这用作使用临时表，并且可以多次使用，而无需为每个连接重新物化。此外，如果您现在需要保留一些记录，在下一次选择之后添加更多记录，在另一个操作之后添加更多记录，然后只返回那少数记录，那么这可能是一个方便的结构，因为它没有' 执行后不需要删除。大多数只是语法糖。但是，如果您将行数保持在较低水平，则它永远不会实现到磁盘。请参阅SQL Server 中的临时表和表变量有什么区别？更多细节。

    温度表

    阅读 MSDN 上的更多信息 - 向下滚动大约 40%

    临时表实际上是在磁盘上创建的表，只是在每个人都知道可以删除的特定数据库中。好的开发人员有责任在不再需要时销毁这些表，但 DBA 也可以清除它们。

    临时表有两种：本地和全局。就 MS Sql Server 而言，您使用#tableName本地##tableName名称和全局名称（注意使用单 # 或双 # 作为标识特征）。

    请注意，对于临时表，与表变量或 CTE 不同，您可以应用索引等，因为这些是正常意义上的合法表。

    通常我会使用临时表来处理更长或更大的查询，如果我已经有一个小数据集并且想快速编写一些小代码的脚本，我会使用 CTE 或表变量。经验和其他人的建议表明，您应该在从中返回少量行的情况下使用 CTE。如果您有大量数据，您可能会受益于在临时表上建立索引的能力。

    • 30

  3. 这里接受的答案是“永远不应该将 CTE 用于性能”——但这可能会产生误导。在 CTE 与临时表的上下文中，我刚刚从一组存储过程中删除了一大堆垃圾，因为有些傻瓜一定认为使用临时表的开销很小或没有。我把这批东西塞进了 CTE，除了那些在整个过程中合法地重复使用的东西。我在所有指标上都获得了大约 20% 的性能。然后我开始删除所有试图实现递归处理的游标。这是我看到最大收获的地方。我最终将响应时间缩短了十倍。

    CTE 和临时表确实有非常不同的用例。我只想强调，虽然不是万能药，但对 CTE 的理解和正确使用可以在代码质量/可维护性和速度方面带来一些真正出色的改进。由于我掌握了它们，我将临时表和游标视为 SQL 处理的最大弊端。我现在几乎可以使用表变量和 CTE 来解决所有问题。我的代码更干净，更快。

    • 19

  4. 一个 CTE 可以在一个查询中被重复调用，并且每次被引用时都会被评估——这个过程可以是递归的。如果它只被引用一次，那么它的行为很像子查询，尽管 CTE 可以参数化。

    临时表是物理持久化的，并且可以被索引。在实践中，查询优化器也可能在后台保存中间连接或子查询结果，例如在假脱机操作中，因此严格来说，CTE 的结果永远不会保存到磁盘上。

    IIRC 表变量（另一方面）始终是内存结构。

    • 16

  5. 临时表是 tempdb 中的真实对象，但 cte 只是对复杂查询的一种包装，以简化一步组织递归的语法。

    • 12

  6. 使用 CTE 的主要原因是访问诸如row_number()和其他各种窗口函数。

    这意味着您可以非常快速和有效地执行诸如获取每个组的第一行或最后一行之类的操作 -在大多数实际情况下比其他方法更有效。

    with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum
from sometable
)
select *
from reallyfastcte
where rownum = 1;

    您可以使用相关子查询或使用子查询来运行与上述类似的查询，但 CTE 在几乎所有场景中都会更快。

    此外，CTE 确实可以帮助简化您的代码。这可以带来性能提升，因为您对查询了解得更多，并且可以引入更多业务逻辑来帮助优化器更具选择性。

    此外，如果您了解业务逻辑并知道应该首先运行查询的哪些部分，CTE 可以提高性能 - 通常，将您最具选择性的查询放在首位，这会导致结果集可以在下一次连接中使用索引并添加option(force order)查询暗示

    最后，CTE 默认不使用 tempdb，因此您可以通过使用它们来减少对该瓶颈的争用。

    如果您需要多次查询数据，或者如果您测量查询并发现通过插入临时表然后添加索引来提高性能，则应使用临时表。

    • 10

  7. 这里似乎对 CTE 有点消极。

    我对 CTE 的理解是，它基本上是一种临时视图。SQL 既是一种声明性语言，又是一种基于集合的语言。CTE 是声明集合的好方法！无法索引 CTE 实际上是一件好事，因为您不需要！它实际上是一种语法糖，使查询更容易读/写。任何体面的优化器都会使用基础表上的索引来制定最佳访问计划。这意味着您可以通过遵循基础表上的索引建议来有效地加快您的 CTE 查询。

    此外，仅仅因为您将集合定义为 CTE，并不意味着必须处理集合中的所有行。根据查询，优化器可能会处理“刚好足够”的行来满足查询。也许您的屏幕只需要前 20 个左右。如果您构建了一个临时表，那么您确实需要读/写所有这些行！

    基于此，我会说 CTE 是 SQL 的一个很棒的特性，可以在任何使查询更易于阅读的地方使用。我只会考虑一个真正需要处理每条记录的批处理的临时表。即使这样，afaik 也不是很推荐，因为在临时表上，数据库很难帮助您进行缓存和索引。最好有一个永久表，其中包含您的事务唯一的 PK 字段。

    我不得不承认我的经验主要是使用 DB2，所以我假设 CTE 在这两种产品中的工作方式相似。如果 CTE 在 SQL server 中表现不佳，我会很高兴地得到纠正。;)

    • 7

相关问题