哪个性能更好:派生表或临时表 [英] Which one have better performance : Derived Tables or Temporary Tables

问题描述

有时我们可以使用派生表和临时表编写查询.我的问题是哪个更好?为什么?

Sometimes we can write a query with both derived table and temporary table. my question is that which one is better? why?

推荐答案

派生表是一个逻辑结构.

Derived table is a logical construct.

它可能存储在 tempdb 中，在运行时通过在每次访问时重新评估底层语句来构建，甚至完全优化.

It may be stored in the tempdb, built at runtime by reevaluating the underlying statement each time it is accessed, or even optimized out at all.

临时表是一种物理结构.它是 tempdb 中的一个表，它被创建并填充了值.

Temporary table is a physical construct. It is a table in tempdb that is created and populated with the values.

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

Which one is better depends on the query they are used in, the statement that is used to derive a table, and many other factors.

例如，SQL Server 中的CTE(公共表表达式)可以(并且很可能会)在每次使用时重新评估.此查询:

For instance, CTE (common table expressions) in SQL Server can (and most probably will) be reevaluated each time they are used. This query:

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

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

在这种情况下，应使用临时表，因为它可以保证其值保持不变.

In this case, a temporary table should be used since it guarantees that its values persist.

另一方面，这个查询:

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

使用派生表更好，因为使用临时表将需要从 master 获取所有值，而此解决方案将仅使用索引扫描前 100 条记录在 id 上.

is better with a derived table, because using a temporary table will require fetching all values from master, while this solution will just scan the first 100 records using the index on id.

这篇关于哪个性能更好:派生表或临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！