За время моей работы, на должности 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
Огромным минус этого подхода — повторные чтения данных, которые существенно снижали эффективность при выполнения такого запроса.
Если взглянуть на план выполнения, то в этом можно легко убедится:
2. UNPIVOT
С релизом SQL Server 2005, стало возможным использовать новую конструкцию языка T-SQL – UNPIVOT.
Применяя UNPIVOT предыдущий запрос можно упростить до:
SELECT UserID, BadgeCount, BadgeType
FROM dbo.UserBadges
UNPIVOT (
BadgeCount FOR BadgeType IN (Gold, Silver, Bronze)
) unpvt
При выполнении мы получим следующий план:
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, план выполнения немного упростится:
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 является достаточно затратной:
Чтобы избавится от этого соединения можно воспользоваться функцией 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:
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 и 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
На плане выполнения видно, что проблема наблюдается в повторном чтении данных и сортировке, которая необходима для упорядочивания данных:
Избавится от повторного чтения достаточно легко, если вспомнить, что в подзапросе допускается использовать столбцы из внешнего блока:
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
Повторные чтения ушли, но операция сортировки никуда не делась:
Посмотрим как ведет себя конструкция 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
План ожидаемо упростился, но сортировка по-прежнему присутствует в плане:
Попробуем обойти сортировку используя аггрегирующую функцию:
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 или 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
