Performance issues with PIVOT

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

В синтаксисе T-SQL для выполнения подобного преобразования предусмотрена отдельная конструкция PIVOT . Стоит заметить, что в SQL Server 2000 поддержки конструкции PIVOT еще не было, поэтому аналогичные задачи решались через множественные CASE WHEN.

Собственно, почему я упомянул о CASE WHEN , если есть PIVOT ? Ведь, по определению, PIVOT более элегантная конструкция и, соответственно, должна быть более эффективной.

Проверим это на практике…

Создадим таблицу, которая будет содержать информацию о выходах сотрудников на рабочие места.

IF OBJECT_ID('dbo.WorkOut', 'U') IS NOT NULL DROP TABLE dbo.WorkOut GO CREATE TABLE dbo.WorkOut ( DateOut DATETIME NOT NULL, EmployeeID INT NOT NULL, CONSTRAINT PK_WorkOut PRIMARY KEY CLUSTERED (DateOut, EmployeeID) ) GO

И заполним ее тестовыми данными:

INSERT INTO dbo.WorkOut (EmployeeID, DateOut) SELECT TOP 1500000 ao.[object_id], ao1.modify_date FROM sys.all_objects ao CROSS JOIN sys.all_objects ao1

Далее напишем PIVOT запрос, который будет возвращать количество выходов по каждому сотруднику в разрезе дней:

SELECT * FROM ( SELECT EmployeeID , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p

При выполнении запроса мы получим следующий план и время выполнения:

SQL Server Execution Times:
CPU time = 5662 ms, elapsed time = 8075 ms.

На плане можно увидеть операторы Sort и Hash Match . Их эффективная работа очень сильно зависит от размера входящих данных и доступного объема физической памяти, чтобы эти самые данные обработать.

При невозможности выделить требуемый объем памяти, обработка результатов будет происходить в базе tempdb (восклицательный знак) - это может приводить к ощутимой нагрузке на дисковую подсистему и увеличению времени выполнения запроса:

SQL Server Execution Times:
CPU time = 6193 ms, elapsed time = 9571 ms.

Посмотрим как ведет себя аналогичная по функциональности конструкция из CASE WHEN условий:

SELECT EmployeeID , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , Tuesday = COUNT(CASE WHEN [WeekDay] = 'Tuesday' THEN 1 END) , Wednesday = COUNT(CASE WHEN [WeekDay] = 'Wednesday' THEN 1 END) , Thursday = COUNT(CASE WHEN [WeekDay] = 'Thursday' THEN 1 END) , Friday = COUNT(CASE WHEN [WeekDay] = 'Friday' THEN 1 END) , Saturday = COUNT(CASE WHEN [WeekDay] = 'Saturday' THEN 1 END) , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID

При выполнении мы получим более простой план. При этом время выполнения будет не слишком отличатся от PIVOT (разумеется в рамках погрешности):

SQL Server Execution Times:
CPU time = 5201 ms, elapsed time = 8400 ms.

В условиях нехватки памяти мы получим следующие результаты:

SQL Server Execution Times:
CPU time = 6006 ms, elapsed time = 13883 ms.

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

Теперь посмотрим как себя ведут данные примеры при увеличении чиста столбцов по которым идет агрегация.

1. Группировка в разрезе: сотрудник + год:

SELECT EmployeeID , [Year] , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , ... , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID, [Year]

SQL Server Execution Times:
CPU time = 5569 ms, elapsed time = 9200 ms.

SELECT * FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p

SQL Server Execution Times:
CPU time = 5454 ms, elapsed time = 8878 ms.

Если сравнить планы, то можно заметить, что операция Hash Match более затратна при использовании PIVOT , но время выполнения говорит об обратном.

2. Группировка в разрезе: сотрудник + год + месяц

SELECT EmployeeID , [Year] , [Month] , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , ... , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [Month] = MONTH(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID, [Year], [Month]

SQL Server Execution Times:
CPU time = 6365 ms, elapsed time = 9979 ms.

SELECT * FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [Month] = MONTH(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p

SQL Server Execution Times:
CPU time = 6193 ms, elapsed time = 9861 ms.

Собственно говоря, ситуация повторяется - SQL Server оценивает PIVOT конструкцию как более затратную.

Но время выполнения опять все ставит на свои места.

Из этого можно сделать небольшие выводы: в преобладающем большинстве ситуаций с помощью конструкции PIVOT можно быстрее выполнить преобразования столбцов в строки.

Небольшое замечание при этом следующее: с увеличением числа столбцов, по которым идет агрегация, разница во времени выполнения между PIVOT и CASE WHEN будет сокращаться и в определенный момент будет в рамках погрешности измерений.

PS:

Все эксперименты проводились на SQL Server 2012 SP1 (11.00.3128) .
Планы выполнения были получены через dbForge Studio for SQL Server.