SQL-Ex blog

Цель моего запроса:
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 может определять, какие части выполнять, а какие просто проигнорировать).

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

• Вы должны обращаться к выводу много раз, или


• Когда вам необходимо передавать данные между хранимыми процедурами, или


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

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