Что лучше, CTE или временные таблицы?

Пересказ статьи Brent Ozar. What’s Better, CTEs or Temp Tables?

Мне часто задают этот вопрос, поэтому давайте установим пример с базой данных Stack Overflow.

Цель моего запроса:
1. Найти TOP 5 мест жительства.
2. Получить список пользователей, кто проживает в этих тор 5 местах, упорядочив их в алфавитном порядке по DisplayName.

Существует множество способов написать такой запрос, но в этой публикации я собираюсь только сравнить общие табличные выражения (CTE) и временные таблицы:

/* Создадим индекс, чтобы облегчить выполнение запроса: */
CREATE INDEX IX_Location ON dbo.Users(Location);
 
/* Общее табличное выражение, CTE: */
WITH TopLocations AS
  (SELECT TOP 5 Location
   FROM dbo.Users
   GROUP BY Location
   ORDER BY COUNT(*) DESC
   )
SELECT u.DisplayName, u.Location, u.WebsiteUrl, u.Reputation, u.Id
  FROM TopLocations tl
  INNER JOIN dbo.Users u ON tl.Location = u.Location
  ORDER BY u.DisplayName;
GO
 
/* Версия с временной таблицей: */
CREATE TABLE #TopLocations (Location NVARCHAR(100));
INSERT INTO #TopLocations (Location)
   SELECT TOP 5 Location
   FROM dbo.Users
   GROUP BY Location
   ORDER BY COUNT(*) DESC;
 
SELECT u.DisplayName, u.Location, u.WebsiteUrl, u.Reputation, u.Id
  FROM #TopLocations tl
  INNER JOIN dbo.Users u ON tl.Location = u.Location
  ORDER BY u.DisplayName;
GO

Здесь мы рассматриваем относительно небольшую таблицу — менее 1Гб в версии Stack Overflow 2013 (50Гб) — поэтому обе версии запроса отрабатывают довольно быстро.

Вот план выполнения для CTE:

CTE выполняет обе операции (нахождение топовых мест жительства и нахождение пользователей в этих местах) в одном операторе. Тут есть моменты за и против:

  • Хорошо: У SQL Server нет необходимости материализовать тор 5 мест на диске.
  • Хорошо: Имеется точная оценка, что из СТЕ должно вернуться 5 мест.
  • Плохо: Неизвестно, какие это будут 5 мест, поэтому…
  • Плохо: Сильно недооцененное число пользователей, которые будут обнаружены в этих местах (оценка: 66 строк, фактически: 50073).
  • Плохо: Выбран поиск по индексу + поиск закладок в общем количестве 158916 чтений.

Вот план выполнения для временной таблицы:

Временная таблица разбивает работу на два этапа; это означает, что к времени выполнения второй операции SQL Server имеет преимущество в знании того, что случилось на первом этапе. Здесь тоже есть свои за и против:

  • Хорошо: SQL Server точно оценил, что 5 мест должны прийти из временной таблицы.
  • Отлично: Даже известно, какие это 5 мест должны быть, поэтому…
  • Отлично: Дается значительно более точная оценка о том, сколько пользователей живет в этих местах (оценка: 24657 строк, фактически: 50073).
  • Отлично: Выбрано сканирование таблицы, что дает меньше логических чтений (всего 49900 для обеих операций).

Итак, что лучше и когда?
СТЕ обычно лучше, когда:

  • SQL Server может хорошо оценить, сколько строк будет возвращено из него, и каково будет содержимое этих строк, или
  • Когда то, что возвращает СТЕ, не оказывает фактического влияния на поведение остального запроса, или
  • Когда вы не уверены, какая часть данных СТЕ будет фактически необходима для остального запроса (поскольку SQL Server может определять, какие части выполнять, а какие просто проигнорировать).

Временные таблицы обычно лучше, когда:

  • Вы должны обращаться к выводу много раз, или
  • Когда вам необходимо передавать данные между хранимыми процедурами, или
  • Когда вам нужно разбить запрос на этапы, чтобы изолировать непредсказуемые элементы, которые оказывают сильное влияние на остальную часть запроса.

Я бы предложил начинать с СТЕ, поскольку их проще писать и читать. Если вы сталкиваетесь с проблемами производительности, попробуйте вытащить СТЕ и записать его во временную таблицу с последующим соединением с этой временной таблицей.

Добавить комментарий