CTE или временные таблицы, что лучше?
Пересказ статьи Brent Ozar. What’s Better, CTEs or Temp Tables?
Мне часто задают этот вопрос, поэтому давайте установим пример с базой данных Stack Overflow.
Цель моего запроса:
1. Найти TOP 5 мест жительства.
2. Получить список пользователей, кто проживает в этих тор 5 местах, упорядочив их в алфавитном порядке по DisplayName.
Существует множество способов написать такой запрос, но в этой публикации я собираюсь только сравнить общие табличные выражения (CTE) и временные таблицы:
Здесь мы рассматриваем относительно небольшую таблицу - менее 1Гб в версии Stack Overflow 2013 (50Гб) - поэтому обе версии запроса отрабатывают довольно быстро.
CTE выполняет обе операции (нахождение топовых мест жительства и нахождение пользователей в этих местах) в одном операторе. Тут есть моменты за и против:
Временная таблица разбивает работу на два этапа; это означает, что к времени выполнения второй операции SQL Server имеет преимущество в знании того, что случилось на первом этапе. Здесь тоже есть свои за и против:
Итак, что лучше и когда?
СТЕ обычно лучше, когда:
Временные таблицы обычно лучше, когда:
Я бы предложил начинать с СТЕ, поскольку их проще писать и читать. Если вы сталкиваетесь с проблемами производительности, попробуйте вытащить СТЕ и записать его во временную таблицу с последующим соединением с этой временной таблицей.
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 может определять, какие части выполнять, а какие просто проигнорировать).
Временные таблицы обычно лучше, когда:
- Вы должны обращаться к выводу много раз, или
- Когда вам необходимо передавать данные между хранимыми процедурами, или
- Когда вам нужно разбить запрос на этапы, чтобы изолировать непредсказуемые элементы, которые оказывают сильное влияние на остальную часть запроса.
Я бы предложил начинать с СТЕ, поскольку их проще писать и читать. Если вы сталкиваетесь с проблемами производительности, попробуйте вытащить СТЕ и записать его во временную таблицу с последующим соединением с этой временной таблицей.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой