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