主页 / dba / 问题 / 118655
Accepted
Juan Velez
Asked: 2015-10-21 11:12:28 +0800 CST

为什么在此查询中使用 CTE 比使用 #Temp 表快得多？

  • 772

我有 2 个不同的查询来识别重复（如下）。两个查询之间的唯一区别是一个使用 CTE，另一个使用 #Temp 表。

有谁知道为什么 CTE 比 #Temp 表快得多（0:20 秒 VS. 1:22）？

我宁愿使用 CTE，但我需要使用 CTE 运行 2 个语句（从 CTE 删除，然后从 CTE 插入表），但 SQL Server 只允许您在 CTE 上编写一个语句。

查询一：

WITH DUPS AS(
   Id,
   Column1,
   Column2,
   Column3,
   RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
   FROM mytable
)
  Select top 1 * FROM DUPS WHERE RN > 1

查询 2：

 SELECT    
           Id,
           Column1,
           Column2,
           Column3,
           RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
       INTO #DUPS
       FROM mytable

       Select top 1 * FROM #DUPS WHERE RN > 1
sql-server

2 个回答

  1. Best Answer

    我相信，如果您查看执行计划，您会发现 CTE 为您提供了更好的计划，因为它可以更好地利用TOP. Temp 表必须在 TempDB 中创建表，将所有数据持久化到磁盘，然后选择顶部记录。如果你TOP从两个查询中删除我打赌你会得到更接近的性能。话虽如此，我通常希望 CTE 在像您在这里一样的单一使用场景中执行临时表，因为 CTE 所做的工作比创建/插入临时表要少。

    这可能只是示例代码，但值得一提的是您的结果是不确定的，因为您使用的是TOP不带ORDER BY子句的。

    如需进一步阅读，您应该查看这个问题。这个问题的答案似乎特别适用于您的情况。

    对于您问题的隐含后半部分：

    既然您说您需要至少两次使用 CTE 中的数据，那么您有四个选择：

    1. 将您的 CTE 复制并粘贴到这两个地方
    2. 使用 CTE 将数据插入到Table Variable中，并使用 table 变量中的数据执行接下来的两个操作。
    3. 使用 CTE 将数据插入到Temp Table中，并使用 temp 表中的数据执行接下来的两个操作。
    4. 编写更好的定制 CTE。我假设您正在做不同的事情，因此查询必须略有不同。

    我倾向于选择选项 2（表变量）或选项 4（定制 CTE）方法。我不喜欢复制/粘贴 CTE 的重复和额外维护。我也喜欢表变量在临时表上的显式缩小范围。有关表变量和临时表之间差异的权威处理，请查看此答案。至于选项 2 和 4 之间的选择；这将取决于您的分析要求和/或您必须在哪些限制范围内工作。

    • 7

  2. 我宁愿使用 CTE，但我需要使用 CTE 运行 2 个语句（从 CTE 删除，然后从 CTE 插入表），但 SQL Server 只允许您在 CTE 上编写一个语句。

    您可以使用 OUTPUT 子句在单个语句中执行删除和插入操作：

    with cte as (
 select ...
 )
delete from cte
output deleted.id into #deleted_ids;
    • 3

相关问题