К CTE или не к CTE

Задерживаясь с SQL2000 слишком долго, у меня не было большой подверженности общим табличным выражениям.

Ответы, которые я дал здесь (# 4025380) и здесь (# 4018793), прошли против потока в том, что они не использовали CTE.

Я ценю, что для рекурсии они являются beez kneez, и есть несколько запросов, которые могут быть значительно упрощены их использованием, но в какой момент их использование просто необоснованно? У них отличная производительность при подзапросе или объединении? Действительно ли они упрощают код и делают его более удобным?

Короче говоря, когда рекомендуется использовать CTE над синтаксисом "lesser".

Ответы

Ответ 1

Обычно вы должны использовать CTE над обычным подзапросом, если:

  • Ваш запрос требует рекурсии (как вы отметили)
  • Подзапрос большой или сложный
  • содержащий запрос большой или сложный
  • Подзапрос повторяется (или, по крайней мере, несколько подзапросов могут быть упрощены путем выполнения различных простых операций в общем подзапросе)

Короче говоря, да, они делают запросы более читабельными, когда они хорошо используются.

Ответ 2

Лично, как только мне стало удобно пользоваться ими, я думаю, что они производят более чистый и читаемый код. В качестве примера сравните свой ответ с моим на # 4018793. Мы по существу сделали то же самое; Я использовал CTE, и вы этого не сделали.

Ваш ответ без CTE:

SELECT
    course,
    section,
    grade,
    gradeCount
FROM
    table
INNER JOIN
    (SELECT
        grade,
        Max(gradeCount) as MaxGradeCount
    FROM
        table
    ) MaxGrades
    ON  table.grade = MaxGrades.grade
        AND table.gradeCount = MaxGrades.MaxGradeCount
ORDER BY 
    table.grade

Мой ответ с CTE:

;with cteMaxGradeCount as (
    select 
        grade, 
        max(gradeCount) as MaxGradeCount
    from @Test
    group by grade
)
select 
    t.course, 
    t.SECTION, 
    t.grade, 
    t.gradeCount
from cteMaxGradeCount c
inner join @Test t
    on  c.grade = t.grade
        and c.MaxGradeCount = t.gradeCount
order by t.grade

Ответ 3

Это синтаксический сахар, за исключением иерархических/рекурсивных запросов.

Однако не все, что может быть сделано рекурсивно, должно быть - генерация даты через рекурсивный CTE была едва лучше, чем курсор. Трюк таблицы NUMBERS значительно улучшился.

Ответ 4

CTE дает более быстрый результат в рекурсивном сценарии. Результат CTE неоднократно используется для получения окончательного набора результатов. Поэтому, поскольку вы приняли предложение where или подзапрос в CTE, определенно это покажет улучшение производительности.
Ссылка: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Просто заметьте, во многих сценариях временные таблицы дают более высокую производительность, чем CTE, поэтому вы также должны попытаться создать таблицы temp. Ссылка: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d040d19d-016e-4a21-bf44-a0359fb3c7fb