Самые креативные задачи, которые я могу сейчас вспомнить, так или иначе, затрагивали вопросы оптимизации запросов.
Оптимизация – это, в первую очередь, поиск оптимального плана запроса. Однако, что делать в ситуации, когда стандартная конструкция языка выдает план, который очень далек от оптимального?
Именно с такой проблемой я столкнулся, когда я применял конструкцию 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' ссылка на оригинал статьи http://habrahabr.ru/post/197476/
Добавить комментарий