主页 / dba / 问题 / 14490
Accepted
Royi Namir
Asked: 2012-03-07 08:19:00 +0800 CST

公用表表达式 (CTE) 的好处？

  • 772

来自msdn：

与派生表不同，CTE 可以是自引用的，并且可以在同一查询中多次引用。

我经常使用 CTE，但我从未深入思考过使用它们的好处。

如果我在同一个查询中多次引用 CTE：

  • 有任何性能优势吗？
  • 如果我正在执行自联接，SQL Server 会扫描目标表两次吗？
sql-server cte

3 个回答

  1. Best Answer

    通常，CTE 永远不会提高性能。

    CTE 本质上是一次性视图。没有存储额外的统计信息，没有索引等。它用作子查询的简写。

    在我看来，它们很容易被过度使用（我在工作中看到很多代码过度使用）。 这里有一些很好的答案，但是如果您需要多次引用某些内容，或者它超过几十万行，请将其放入#temp表中并对其进行索引。

    • 26

  2. 除了递归之外，我发现 CTE 非常有用的一个地方是在创建复杂的报告查询时。我使用一系列 CTE 来获取我需要的数据块，然后在最终选择中组合。我发现它们比使用大量派生表或 20 个连接做同样的事情更容易维护，而且我发现我可以更确信它返回正确的数据而不受多条记录的影响，因为其中的一对多关系所有不同的连接。让我举一个简单的例子：

    ;WITH Conferences (Conference_id)
AS 
(select  m.Conference_id
FROM mydb.dbo.Conference m 
WHERE client_id = 10
    and Conference_id in 
            (select Conference_id from mydb.dbo.Expense 
            where amount <>0
            and amount is not null)
     )
--select * from Conferences
,MealEaters(NumberMealEaters, Conference_id, AttendeeType)
AS
(Select count(*) as NumberMealEaters, m.Conference_id,  AttendeeType 
from mydb.dbo.attendance ma 
join Conferences m on m.Conference_id = ma.Conference_id
where (ma.meals_consumed>0 or meals_consumed is null)and attended = 1
group by m.Conference_id)
--select * from MealEaters

,Expenses (Conference_id,expense_date, expenseDescription,  RecordIdentifier,amount)
AS
(select Conference_id,max(expense_date) as Expense_date, expenseDescription,  RecordIdentifier,sum(amount) as amount
    FROM
        (SELECT Conference_id,expense_date,  amount, RecordIdentifier
        FROM mydb.dbo.Expense
        WHERE  amount <> 0 
            and Conference_id IN 
            (SELECT  Conference_id
            FROM mydb.dbo.Conferences ) 
        group by Conference_id, RecordIdentifier) a
)
--select * from Expenses
Select m.Conference_id,me.NumberMealEaters, me.AttendeeType, e.expense_date,         e.RecordIdentifier,amount
from Conferences m
join mealeaters me on m.Conference_id = me.Conference_id
join expenses e on e.Conference_id = m.Conference_id

    因此，通过分离出您想要的不同信息块，您可以单独检查每个部分（使用注释掉的选择，通过单独取消注释每个部分并且只运行该选择）以及是否需要更改费用计算（在此示例中），比将它们混合在一起形成一个海量查询更容易找到。当然，我使用它的实际报告查询通常比示例复杂得多。

    • 14

  3. 与往常一样，这取决于但在某些情况下性能会大大提高。我在 INSERT INTO SELECT 语句中看到它，您使用 CTE 进行选择，然后在 INSERT INTO 中使用它。这可能与为数据库设置 RCSI 有关，但对于那些选择很少的时候，它可以提供相当多的帮助。

    • 0

相关问题