SQL-Ex blog

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

Для примеров здесь используется дамп данных 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 и т.п., знание как выполнить рефакторинг коррелирующего подзапроса к производной таблице может иметь решающее значение для потенциального улучшения производительности.