SQL-Ex blog

Цель этой статьи - показать на примерах подводные камни в запросах, влияющие на их производительность, и как переписать такие запросы, чтобы сгенерировать другие планы, которые улучшат производительность. В примерах используется дамп данных StackOverflow 2014, который вы можете использовать у себя для тестов.

Кто впервые заработал каждый значок (badge)

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

Я захотел написать запрос, чтобы выяснить, кто был первым, кого наградили каждым из таких значков. В случае, если таких людей было несколько, я хочу возвращать пользователя с наименьшим UserId.

Оконные функции позволяют просто написать запрос, решающий нашу задачу:

SELECT DISTINCT

    Name,

    FIRST_VALUE(UserId) OVER (PARTITION BY Name ORDER BY Date,UserId) AS UserId

FROM

    dbo.Badges b

ORDER BY

    Name,UserId

Даже если вы не использовали ранее функцию FIRST_VALUE, этот запрос должен быть легко интерпретирован: для каждого значка Name вернуть первый UserId при сортировке по Date (самая ранняя дата получения значка) и UserId (выбираем наименьший UserId при одной и той же дате).

Этот запрос было легко написать и легко понять. Однако его производительность не выдающаяся: 46 секунд до окончательной выдачи результатов на моей машине.

Замечание: Я предполагаю, что эта таблица имеет следующий индекс:

CREATE NONCLUSTERED INDEX IX_Badges__Name_Date_UserId ON [dbo].[Badges] (Name,Date,UserId);

Почему так медленно?

Если мы включим статистику (SET STATISTICS IO ON), то заметим, что SQL Server считывает 46767 страниц из некластеризованного индекса. Поскольку мы не фильтруем наши данные, мало что можно сделать, чтобы ускориться.

Читая план справа налево, следом мы видим два оператора Segment. Они не добавляют большой нагрузки, поскольку наши данные уже отсортированы по сегментам/группам. Поэтому для SQL Server тривиально определить, когда отсортированные строки изменяют значения.

Следующий оператор Window Spool, который "расширяет каждую строку в набор строк, которые представляют связанное с ней окно." Хотя этот оператор выглядит невинно из-за низкой относительной стоимости, он записывает 8 миллионов строк/читая 16 миллионов строк (поскольку так работает Window Spool) из tempdb. Ох.

После чего оператор Stream Aggregate и операторы Compute Scalar проверяют, является ли первое значение в каждом окне, возвращаемом из Window Spool, NULL-значением, после чего возвращают первое не-NULL значение. Эти операторы также относительно безболезненны, т.к. потоки данных уже отсортированы.

Затем оператор Hash Match удаляет дубликаты данных для нашего DISTINCT, после чего мы сортируем остальные 2k строк на вывод.

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

Устранение использования tempdb старомодным способом

Когда я говорю "старомодный", я имею в виду переписывание нашей оконной функции на использование традиционных агрегатных функций и GROUP BY:

SELECT

    b.Name,

    MIN(b.UserId) AS UserId

FROM

    dbo.Badges b

    INNER JOIN

    (

    SELECT

        Name,

        MIN(Date) AS Date

    FROM

        dbo.Badges

    GROUP BY

        Name

    ) m

        ON b.Name = m.Name

        AND b.Date = m.Date

GROUP BY

    b.Name

ORDER BY

    Name,UserId

Я думаю, что большинству людей этот запрос нелегко читать. Хотя и не слишком сложный, он занимает значительно больше места на экране и усложняется несколькими GROUP BY и производной таблицей.

И хотя этот запрос может выглядеть уродливо со стороны, реально имеет значение только то, что лежит под капотом:



Какой замечательно простой план выполнения. И он выполняется практически мгновенно.

Давайте разберемся, что происходит. Сначала мы стартуем с операторов Index Scan и Segment, аналогичных предыдущему запросу.

Вы могли уже заметить, что хотя в запросе написаны два предложения GROUP BY и две функции MIN, которые затем соединяются вместе, здесь нет двух Index Scans, двух раборов агрегации, и никаких соединений не видно в плане выполнения.

SQL Server может использовать оптимизацию с оператором TOP, который позволяет взять отсортированные данные и вернуть только строки с Name и UserId для топовых значений Name и Date в пределах группы (по существу соответствует логике MIN). Это прекрасный пример того, как оптимизатор может взять декларативный запрос SQL и решить, как эффективно вернуть требуемые данные.

Здесь оператор TOP отфильтровывает из наших 8 миллионов строк около 30k строк. Исключение дубликатов среди 30k выполняется значительно быстрей с помощью оператора Stream Aggregate, и, поскольку данные уже отсортированы, нам не требуется дополнительный оператор Sort.

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

Так стоит ли использовать оконные функции?

Не обязательно искать компромисс.

Я почти всегда начинаю с оконных функций из-за легкости их написания и чтения. Однако если оконная функция должна писать/читать множество данных в/из tempdb, и это отрицательно влияет на общую производительность вашего запроса, возможно стоит переписать его.

Я, скорее, предпочту использовать громоздкий синтаксис, чтобы увеличить производительность в 2000 раз.