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 ^[1].

Автор: AlanDenton

Источник ^[2]