Коррелирующие подзапросы или производные таблицы?

Пересказ статьи Bert Wagner. Correlated Subqueries vs Derived Tables

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

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

Для примеров здесь используется дамп данных StackOverflow 2014.

Когда каждым пользователем был заработан первый значок?

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

Я хочу написать запрос, который выяснит дату, когда каждый пользователь получил свой первый значок.

С помощью коррелирующего подзапроса я могу написать запрос следующим образом:

SET STATISTICS IO, TIME ON;
SELECT DISTINCT
    UserId,
    FirstBadgeDate = (SELECT MIN(DATE) FROM dbo.Badges i WHERE o.UserId = i.UserId)
FROM
    dbo.Badges o

Синтаксис коррелирующего подзапроса делает ясным, что для каждого UserId мы хотим вернуть MIN(Date), связанную с этим UserId из таблицы badges.

Взглянув на план выполнения и статистику по времени и вводу/выводу, видим:

(1318413 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, ...
Table 'Workfile'. Scan count 0, logical reads 0, ...
Table 'Badges'. Scan count 2, logical reads 43862, ...
(1 row affected)
 SQL Server Execution Times:
   CPU time = 3625 ms,  elapsed time = 8347 ms.

Итак, что тут происходит? Мы читаем ~8 миллионов строк данных из индекса на таблице dbo.Badges и затем вычисляем MIN(Date) для каждого UserId. Это «коррелирующая» часть запроса, которая затем соединяется с таблицей dbo.Badges с помощью оператора Hash Match join.

Наше соединение не исключает никаких строк, поэтому поток из ~8 миллионов строк продолжает движение почти до конца, где мы имеем другой оператор Hash Match, который теперь используется для исключения дубликатов строк в соответствии с частью запроса DISTINCT, уменьшая окончательный результат до ~1 миллиона строк.

Исключение коррелирующего подзапроса

Что изменится, если мы перепишем этот коррелирующий подзапрос в виде производной таблицы в предложении FROM?

SELECT DISTINCT
    o.UserId,
    FirstBadgeDate
FROM
    dbo.Badges o
    INNER JOIN 
        (SELECT 
            UserId, 
            MIN(DATE) AS FirstBadgeDate 
        FROM 
            dbo.Badges GROUP BY UserId
        ) i
    ON o.UserId = i.UserId

(1318413 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, ...
Table 'Worktable'. Scan count 0, logical reads 0, ...
Table 'Badges'. Scan count 2, logical reads 43862, ...
(1 row affected)
 SQL Server Execution Times:
   CPU time = 2516 ms,  elapsed time = 5350 ms.

Если мы посмотрим на статистику ввода/вывода, интересно отметить, что разницы в чтениях между этими двумя запросами нет.

Однако статистика времени CPU показывает, что запрос с производной таблицей приблизительно на 33% быстрей, чем пример с коррелирующим подзапросом. Почему так?

Взгляд на план выполнения обнаруживает некоторые детали: на этом плане можно увидеть, что мы читаем из индекса dbo.Badges и переходим прямо в оператор Hash Match. Верхний поток исключает дубликаты наших данных по UserId, сокращая их с ~8 миллионов до ~1 миллиона строк. Нижний поток делает то же исключение при вычислении MIN(Date) для каждого сгруппированного UserId.

Оба эти потока соединяются вместе, финальный оператор hash match выполняет только соединение ~1 миллиона строк с ~1 миллионом строк (в противоположность первому запросу, который соединял ~8 миллионов строк с ~1 миллионом строк).

Последнее соединение и есть причина улучшения производительности: поскольку этот план выполнения может сократить число строк раньше, и финальному соединению предстоит выполнить меньше работы. Помимо этого, записи поступают в соединение уже уникальными, избавляя нас от лишней работы по исключению дубликатов.

Дальнейшее сокращение избыточности

Можно заметить, что оба этих запроса слегка избыточны: они оба вызывают лишний раз таблицу dbo.Badges. Лучшим способом улучшить производительность запроса было бы переписать его таким образом:

SELECT 
    UserId, 
    MIN(DATE) AS FirstBadgeDate 
FROM 
    dbo.Badges 
GROUP BY 
    UserId

При том, что это наиболее эффективный запрос из трех, большинство реальных запросов и сценариев не так легко упростить.

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

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