Сообщение [SQL Server] STRING_SPLIT - новая встроенная функция от 11.03.2016 20:02
Изменено 15.03.2016 4:32 Olaf
На днях вышел в свет SQL Server 2016 Release Candidate (RC0). В качестве заявленных дополнений фигурирует встроенная функция STRING_SPLIT для деления строки на части с возможностью указать разделитель.
Казалось бы, в наши дни такой функциональностью уже никого не удивишь, т.к. существует около десятка способов реализовать аналог самостоятельно, причем один из вариантов появился совсем недавно в версии 2016 с функцией openjson. Тем не менее, хотелось бы посмотреть на реализацию из «коробки» и сравнить с производительностью существующих решений.Syntax
STRING_SPLIT ( string , separator )
Arguments
string — Is an expression of any character type (i.e. nvarchar, varchar, nchar or char).
separator — Is a single character expression of any character type (e.g. nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings.
Return Types
Returns a single-column table with fragments. The name of the column is value. Returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise returns varchar. The length of the return type is the same as the length of the string argument.
Мой тест будет основываться на постах из обсуждения split(somestring,',') в T-SQL, которое здесь недавно проходило, с учетом ошибки, которую я допустил для варианта №4 на большом количестве элементов. Кроме того, все пользовательские функции я снабдил дополнительным параметром – много символьный разделитель (!)
Рассматривается 8 способов поделить строку на части через разделитель:
1. Цикл
Код | |
| |
2. CTE
Код | |
| |
3. XML
Код | |
| |
4. Таблица
Код | |
| |
5. CLR
Код | |
| |
6. OPENJSON
7. LIKE, PATHINDEX, CHARINDEX
8. STRING_SPLIT
Для сравнения производительности используется вспомогательная таблица dbo.table2, которая содержит массив элементов фиксированной длины. В качестве ключа id выступает идентификатор, совпадающий с количеством элементов в массиве. Разделителем элементов является запятая ',' Тест выполняется с помощью утилиты SQLQueryStress через запуск решения 10 раз, на основании полученных данных вычисляется среднее время выполнения.
Тестовые данные
Для подготовки данных использовал скрипт...
Скрытый текст | |
| |
Решение
Реализовано в запросах...
Скрытый текст | |
| |
Результат
Для наглядности результаты сравнения производительности представлены в виде графика. Ось Y — логарифмическая.
Исходные данные содержатся в таблице.
Испытание полностью выдержали только 6-ть решений из 8-и, т.е. решения у которых среднее время выполнения запроса меньше 10 минут, результат преобразования правильный и не возникло исключительных ситуаций в ходе выполнения. Первое место по-прежнему удерживает вариант номер 5 с CLR реализацией. Причем исходя из данных представленных в таблице видно, что время выполнения увеличивается пропорционально количеству данных. Второе место уверенно сохраняет OPENJSON решение. Стоит отметить, что начиная со значения в 50 000 элементов наблюдается расхождение в два раза по сравнению с CLR функцией, в то время как предыдущие результаты отличались в третьем знаке после запятой. Новая встроенная функция STRING_SPLIT незначительно отстает от варианта с OPENJSON и занимает 3-е место. XML подход показал стабильное время на всех участках эксперимента и переходит на 4-е место. 5-я строчка рейтинга достается решению с использованием CTE, причем до отметки в 100 000 элементов, данный подход отставал в третьем знаке после запятой от реализации с использованием справочной таблицы с числами. В итоге замыкает шестерку решение под номером 4, показатели которого стали ухудшаться с отметки в 100 000 элементов.
Еще пара слов о решениях, которые выбыли из конкурса.
Вариант номер 7 (LIKE, PATINDEX,CHARINDEX) — длительное время выполнения даже на маленьких объемах данных заставило исключить его. Для решения номер 1 с циклом начиная с 250 000 элементов увеличилось время выполнения и перевалило за 10 минут, поэтому было принято решение исключить и этот вариант.
P.S. Новая встроенная функция STRING_SPLIT обладающая всеми преимуществами варианта из "коробки" показала неплохую производительность. Пожалуй единственный недостаток заключается в сепараторе, который может быть не больше одного символа. Возможно при условии, что вы хорошо знаете свои данные этот недочет можно обойти.
На днях вышел в свет SQL Server 2016 Release Candidate (RC0). В качестве заявленных дополнений фигурирует встроенная функция STRING_SPLIT для деления строки на части с возможностью указать разделитель.
Syntax
STRING_SPLIT ( string , separator )
Arguments
string — Is an expression of any character type (i.e. nvarchar, varchar, nchar or char).
separator — Is a single character expression of any character type (e.g. nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings.
Return Types
Returns a single-column table with fragments. The name of the column is value. Returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise returns varchar. The length of the return type is the same as the length of the string argument.
Казалось бы, в наши дни такой функциональностью уже никого не удивишь, т.к. существует около десятка способов реализовать аналог самостоятельно, причем один из вариантов появился совсем недавно в версии 2016 с функцией openjson. Тем не менее, хотелось бы посмотреть на реализацию из «коробки» и сравнить с производительностью существующих решений.
Мой тест будет основываться на постах из обсуждения split(somestring,',') в T-SQL, которое здесь недавно проходило, с учетом ошибки, которую я допустил для варианта №4 на большом количестве элементов. Кроме того, все пользовательские функции я снабдил дополнительным параметром – много символьный разделитель (!)
Рассматривается 8 способов поделить строку на части через разделитель:
1. Цикл
Код функции | |
| |
2. CTE
Код функции | |
| |
3. XML
Код функции | |
| |
4. Таблица
Код функции | |
| |
5. CLR
Код функции | |
| |
6. OPENJSON
7. LIKE, PATHINDEX, CHARINDEX
8. STRING_SPLIT
Для сравнения производительности используется вспомогательная таблица dbo.table2, которая содержит массив элементов фиксированной длины. В качестве ключа id выступает идентификатор, совпадающий с количеством элементов в массиве. Разделителем элементов является запятая ',' Тест выполняется с помощью утилиты SQLQueryStress через запуск решения 10 раз, на основании полученных данных вычисляется среднее время выполнения.
Тестовые данные
Для подготовки данных использовал...
Сценарий | |
| |
Решение
Реализовано в запросах...
Сценарий | |
| |
Результат
Для наглядности результаты сравнения производительности представлены в виде графика. Ось Y — логарифмическая.
Исходные данные содержатся в таблице.
Вывод
Испытание полностью выдержали только 6-ть решений из 8-и, т.е. решения у которых среднее время выполнения запроса меньше 10 минут, результат преобразования правильный и не возникло исключительных ситуаций в ходе выполнения. Первое место по-прежнему удерживает вариант номер 5 с CLR реализацией. Причем исходя из данных представленных в таблице видно, что время выполнения увеличивается пропорционально количеству данных. Второе место уверенно сохраняет OPENJSON решение. Стоит отметить, что начиная со значения в 50 000 элементов наблюдается расхождение в два раза по сравнению с CLR функцией, в то время как предыдущие результаты отличались в третьем знаке после запятой. Новая встроенная функция STRING_SPLIT незначительно отстает от варианта с OPENJSON и занимает 3-е место. XML подход показал стабильное время на всех участках эксперимента и переходит на 4-е место. 5-я строчка рейтинга достается решению с использованием CTE, причем до отметки в 100 000 элементов, данный подход отставал в третьем знаке после запятой от реализации с использованием справочной таблицы с числами. В итоге замыкает шестерку решение под номером 4, показатели которого стали ухудшаться с отметки в 100 000 элементов.
Еще пара слов о решениях, которые выбыли из конкурса.
Вариант номер 7 (LIKE, PATINDEX,CHARINDEX) — длительное время выполнения даже на маленьких объемах данных заставило исключить его. Для решения номер 1 с циклом начиная с 250 000 элементов увеличилось время выполнения и перевалило за 10 минут, поэтому было принято решение исключить и этот вариант.
P.S. Новая встроенная функция STRING_SPLIT обладающая всеми преимуществами варианта из "коробки" показала неплохую производительность. Пожалуй единственный недостаток заключается в сепараторе, который может быть не больше одного символа. Возможно при условии, что вы хорошо знаете свои данные этот недочет можно обойти.