Início / dba / Perguntas / 14490
Accepted
Royi Namir
Asked: 2012-03-07 08:19:00 +0800 CST

Benefícios da Expressão de Tabela Comum (CTE)?

  • 772

Do msdn :

Ao contrário de uma tabela derivada, uma CTE pode ser auto-referenciada e pode ser referenciada várias vezes na mesma consulta.

Estou usando bastante CTEs, mas nunca pensei profundamente sobre os benefícios de usá-los.

Se eu fizer referência a um CTE várias vezes na mesma consulta:

  • Existe algum benefício de desempenho?
  • Se eu estiver fazendo uma autojunção, o SQL Server verificará as tabelas de destino duas vezes?
sql-server cte

3 respostas

  1. Best Answer

    Como regra, um CTE NUNCA melhorará o desempenho .

    Um CTE é essencialmente uma visão descartável. Não há estatísticas adicionais armazenadas, índices, etc. Funciona como um atalho para uma subconsulta.

    Na minha opinião, eles podem ser facilmente usados ​​​​em excesso (eu vejo muito uso excessivo de código no meu trabalho). Algumas boas respostas estão aqui, mas se você precisar se referir a algo mais de uma vez, ou for mais do que algumas centenas de milhares de linhas, coloque-o em uma #temptabela e indexe-o.

    • 26

  2. Um lugar além da recursão onde acho os CTEs incrivelmente úteis é ao criar consultas de relatórios complexos. Eu uso uma série de CTEs para obter partes dos dados de que preciso e, em seguida, combino na seleção final. Acho que eles são mais fáceis de manter do que fazer a mesma coisa com muitas tabelas derivadas ou 20 junções e acho que posso ter mais certeza de que ele retorna os dados corretos sem efeito de vários registros devido aos relacionamentos um-muitos em todas as diferentes junções. Deixe-me dar um exemplo rápido:

    ;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

    Então, separando os diferentes pedaços de informação que você deseja, você pode verificar cada parte individualmente (usando os selects comentados, descomentando cada um individualmente e executando apenas até aquele select) e se você precisar fazer uma alteração na despesa cálculo (neste exemplo), é mais fácil de encontrar do que quando todos estão misturados em uma consulta massiva. É claro que as consultas de relatórios reais para as quais uso isso geralmente são muito mais complicadas do que o exemplo.

    • 14

  3. Como sempre depende, mas há casos em que o desempenho é muito melhorado. Eu vejo isso com instruções INSERT INTO SELECT onde você usa um CTE para selecionar e depois usa isso no INSERT INTO. Pode ter a ver com o RCSI sendo definido para o banco de dados, mas para aqueles momentos em que muito pouco é selecionado, pode ajudar bastante.

    • 0

relate perguntas