Производительность конструкции UNPIVOT и ее аналогов

в 12:10, , рубрики: sql, sql server, unpivot, высокая производительность, метки: , ,

За время моей работы, на должности DBA, я сталкивался с широким кругом задач. Одни задачи требовали монотонной работы, другие сводились к чистому креативу.

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

Оптимизация – это, в первую очередь, поиск оптимального плана запроса. Однако, что делать в ситуации, когда стандартная конструкция языка выдает план, который очень далек от оптимального?

Именно с такой проблемой я столкнулся, когда я применял конструкцию UNPIVOT для преобразования столбцов в строки.

Выход был один – необходимо было найти для UNPIVOT более эффективную альтернативу…

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

IF OBJECT_ID('dbo.UserBadges', 'U') IS NOT NULL
	DROP TABLE dbo.UserBadges

CREATE TABLE dbo.UserBadges
(
	  UserID INT
	, Gold SMALLINT NOT NULL
	, Silver SMALLINT NOT NULL
	, Bronze SMALLINT NOT NULL
	, CONSTRAINT PK_UserBadges PRIMARY KEY (UserID)
)

INSERT INTO dbo.UserBadges (UserID, Gold, Silver, Bronze)
VALUES
	(1, 5, 3, 1),
	(2, 0, 8, 1),
	(3, 2, 4, 11)

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

Чтобы не было лишних вопросов, в душе я небольшой перфекционист, поэтому максимальное удобство, при работе с планами выполнения, я получаю в dbForge Studio for SQL Server. По этой причине, все скриншоты планов сделаны именно при помощи данного инструмента, а не в SSMS.

Перейдём от слов к делу…

1. UNION ALL

В свое время, SQL Server 2000 не предоставлял эффективного способа преобразовывать столбцы в строки. Вследствие чего широко практиковалась практика многократной выборки из одной и той же таблицы, но с разным набором столбцов, объединенных через конструкцию UNION ALL:

SELECT UserID, BadgeCount = Gold, BadgeType = 'Gold' 
FROM dbo.UserBadges
    UNION ALL
SELECT UserID, Silver, 'Silver' 
FROM dbo.UserBadges
    UNION ALL
SELECT UserID, Bronze, 'Bronze' 
FROM dbo.UserBadges

Огромным минус этого подхода — повторные чтения данных, которые существенно снижали эффективность при выполнения такого запроса.

Если взглянуть на план выполнения, то в этом можно легко убедится:

Производительность конструкции UNPIVOT и ее аналогов

2. UNPIVOT

С релизом SQL Server 2005, стало возможным использовать новую конструкцию языка T-SQLUNPIVOT.

Применяя UNPIVOT предыдущий запрос можно упростить до:

SELECT UserID, BadgeCount, BadgeType
FROM dbo.UserBadges
UNPIVOT (
    BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
) unpvt

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

Производительность конструкции UNPIVOT и ее аналогов

3. VALUES

Начиная с SQL Server 2008 стало возможным использовать конструкцию VALUES не только для создания многострочных INSERT запросов, но и внутри блока FROM.

Применяя конструкцию VALUES, запрос выше можно переписать так:

SELECT p.UserID, t.*
FROM dbo.UserBadges p
CROSS APPLY (
    VALUES 
          (Gold,   'Gold')
        , (Silver, 'Silver')
        , (Bronze, 'Bronze')
) t(BadgeCount, BadgeType)

При этом, по-сравнению с UNPIVOT, план выполнения немного упростится:

Производительность конструкции UNPIVOT и ее аналогов

4. Dynamic SQL

Применяя динамический SQL, есть возможность создать «универсальный» запрос для любой таблицы. Главное условие при этом — столбцы, которые не входят в состав первичного ключа, должны иметь совместимые между собой типы данных.

Узнать список таких столбцов можно следующим запросом:

SELECT c.name
FROM sys.columns c WITH(NOLOCK)
LEFT JOIN (
	SELECT i.[object_id], i.column_id
	FROM sys.index_columns i WITH(NOLOCK)
	WHERE i.index_id = 1
) i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id
WHERE c.[object_id] = OBJECT_ID('dbo.UserBadges', 'U')
	AND i.[object_id] IS NULL

Если посмотреть на план запроса, можно заметить, что соединение с sys.index_columns является достаточно затратной:

Производительность конструкции UNPIVOT и ее аналогов

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

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.UserBadges'

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT * 
FROM ' + @table_name + '
UNPIVOT (
    value FOR code IN (
        ' + STUFF((
    SELECT ', [' + c.name + ']'
    FROM sys.columns c WITH(NOLOCK)
    WHERE c.[object_id] = OBJECT_ID(@table_name)
				AND INDEX_COL(@table_name, 1, c.column_id) IS NULL
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
    )
) unpiv'

PRINT @SQL
EXEC sys.sp_executesql @SQL

При выполнении будет сформирован запрос в соответствии с шаблоном:

SELECT * 
FROM <table_name>
UNPIVOT (
	value FOR code IN (<unpivot_column>)
) unpiv

Даже если брать во внимание оптимизации, которые мы проделали, стоит отметить, что данный способ более медленный.

поскольку, для автоматического формирования запроса UNPIVOT, дополнительно используется выборка из системных представлений и конкатенация строк через XML:

Производительность конструкции UNPIVOT и ее аналогов

5. XML

Более элегантно реализовать динамический UNPIVOT возможно, если использовать следующий трюк с XML:

SELECT
      p.UserID
    , BadgeCount = t.c.value('.', 'INT') 
    , BadgeType = t.c.value('local-name(.)', 'VARCHAR(10)') 
FROM (
    SELECT 
          UserID
        , [XML] = (
                SELECT Gold, Silver, Bronze
                FOR XML RAW('t'), TYPE
            )
    FROM dbo.UserBadges
) p
CROSS APPLY p.[XML].nodes('t/@*') t(c)

В котором для каждой строки формируется XML вида:

<t Column1="Value1" Column2="Value2" Column3="Value3" ... />

После чего парсится имя каждого атрибута и его значения.

В большинстве случаев, при использовании XML получается более медленный план выполнения – это расплата за универсальность.

Производительность конструкции UNPIVOT и ее аналогов

Теперь сравним полученные примеры:

Производительность конструкции UNPIVOT и ее аналогов

Кардинальной разницы в скорости выполнения между UNPIVOT и VALUES не наблюдается. Это утверждение верно, если речь идет о простом преобразовании столбцов в строки.

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

Попробуем решить задачу применяя конструкцию UNPIVOT:

SELECT 
      UserID
    , GameType = (
        SELECT TOP 1 BadgeType 
        FROM dbo.UserBadges b2 
        UNPIVOT (
            BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
        ) unpvt
        WHERE UserID = b.UserID 
        ORDER BY BadgeCount DESC
    ) 
FROM dbo.UserBadges b

На плане выполнения видно, что проблема наблюдается в повторном чтении данных и сортировке, которая необходима для упорядочивания данных:

Производительность конструкции UNPIVOT и ее аналогов

Избавится от повторного чтения достаточно легко, если вспомнить, что в подзапросе допускается использовать столбцы из внешнего блока:

SELECT 
	  UserID
	, GameType = (
		SELECT TOP 1 BadgeType
		FROM (SELECT t = 1) t 
		UNPIVOT (
			BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
		) unpvt
		ORDER BY BadgeCount DESC
	) 
FROM dbo.UserBadges

Повторные чтения ушли, но операция сортировки никуда не делась:

Производительность конструкции UNPIVOT и ее аналогов

Посмотрим как ведет себя конструкция VALUES в данной задаче:

SELECT 
      UserID
    , GameType = (
            SELECT TOP 1 BadgeType
            FROM (
                VALUES
                      (Gold,   'Gold')
                    , (Silver, 'Silver')
                    , (Bronze, 'Bronze')
            ) t (BadgeCount, BadgeType)
            ORDER BY BadgeCount DESC
        ) 
FROM dbo.UserBadges

План ожидаемо упростился, но сортировка по-прежнему присутствует в плане:

Производительность конструкции UNPIVOT и ее аналогов

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

SELECT 
	  UserID
	, BadgeType = (
            SELECT TOP 1 BadgeType
            FROM (
                VALUES
                      (Gold,   'Gold')
                    , (Silver, 'Silver')
                    , (Bronze, 'Bronze')
            ) t (BadgeCount, BadgeType)
			WHERE BadgeCount = (
				SELECT MAX(Value)
				FROM (
					VALUES (Gold), (Silver), (Bronze)
				) t(Value)
			)
		) 
FROM dbo.UserBadges

Мы избавились от сортировки:

Производительность конструкции UNPIVOT и ее аналогов

Небольшие итоги:

В ситуации, когда необходимо произвести простое преобразование столбцов в строки, то наиболее предпочтительно использовать конструкции UNPIVOT или VALUES.

Если после преобразования, полученные данные планируется использовать в операциях агрегирования или сортировки, то более предпочтительно использовать именно конструкцию VALUES, которая, в большинстве случае, позволяет получать более эффективные планы выполнения.

Если число столбцов в таблицы переменчиво, рекомендуется использовать XML, который в отличии от динамического SQL, можно использовать внутри табличных функций.

P.S.

Чтобы адаптировать, часть примеров под особенности SQL Server 2005, конструкцию с применением VALUES:

SELECT * 
FROM (
	VALUES (1, 'a'), (2, 'b')
) t(id, value)

необходимо заменить на комбинацию SELECT UNION ALL SELECT:

SELECT id = 1, value = 'a'
UNION ALL
SELECT 2, 'b'

Автор: AlanDenton

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js