[SQL Server] STRING_SPLIT - новая встроенная функция
От: Olaf Россия  
Дата: 11.03.16 20:02
Оценка: 98 (8)
Привет, Всем!

На днях вышел в свет 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. Цикл
  Код функции
if object_id('dbo.Split1') is not null
    drop function dbo.Split1
go

create function dbo.Split1(@input varchar(max), @delimiter varchar(50))
returns @result table(value bigint)  
as
begin
    declare @str varchar(20)  
    declare @ind int

    if(@input is not null)  
    begin 
        set @ind = charindex(@delimiter, @input)  
        while @ind > 0  
        begin 
            set @str = substring(@input, 1, @ind - 1)
            set @input = substring(@input, @ind + datalength(@delimiter), len(@input) - @ind)  

            insert into @result values (@str)  
            set @ind = charindex(@delimiter, @input)  
        end  

        set @str = @input  
        insert into @result values (@str)  
    end
    return
end

go

2. CTE
  Код функции
if object_id('dbo.Split2') is not null
    drop function dbo.Split2
go

create function dbo.Split2(@strString varchar(max), @delimiter varchar(50))
returns @result table(value bigint)
as
begin  
    with StrCTE(start, stop) as
    (  
        select cast(1 as bigint), charindex(@delimiter, @strString)  
        union all
        select stop + datalength(@delimiter), charindex(@delimiter, @strString, stop + datalength(@delimiter))
        from StrCTE  
        where stop > 0  
    )  

    insert into @result  
    select substring(@strString, start, case when stop > 0 then stop - start else 2147483647 end) as stringValue  
    from StrCTE
    option (maxrecursion 0)

    return  
end

go

3. XML
  Код функции
if object_id('dbo.Split3') is not null
    drop function dbo.Split3
go

create function dbo.Split3(@strString varchar(max), @delimiter varchar(50))
returns @result table(value bigint)  
as
begin
    declare @x xml
    select @x = cast('<A>' + replace(@strString, @delimiter, '</A><A>') + '</A>' as xml)

    insert into @result              
    select t.value('.', 'int') as inVal  
    from @x.nodes('/A') as x(t)  
    
    return

end

go

4. Таблица
  Код функции
if object_id('dbo.Split4') is not null
    drop function dbo.Split4
go

create function dbo.Split4(@strString varchar(max), @delimiter varchar(50))  
returns @result table(value bigint)
as
begin
    insert into @result(value)  
    select substring(@strString, N, case when charindex(@delimiter, @strString, N) - N < 0 then datalength(@delimiter) + N else charindex(@delimiter, @strString, N) - N end)
    from dbo.Tally
    where N <= datalength(@strString) + datalength(@delimiter) and substring(@delimiter + @strString + @delimiter, N, datalength(@delimiter)) = @delimiter
    
    return
end

go

5. CLR
  Код функции
if object_id('dbo.Split5') is not null
    drop function dbo.Split5
go

create function dbo.Split5(@input nvarchar(max), @delimiter nvarchar(255))
returns table
(
    value nvarchar(4000) NULL
) with execute as caller
as 
external name Demo.UserDefinedFunctions.SplitString_Multi
go

6. OPENJSON

7. LIKE, PATHINDEX, CHARINDEX

8. STRING_SPLIT

Для сравнения производительности используется вспомогательная таблица dbo.table2, которая содержит массив элементов фиксированной длины. В качестве ключа id выступает идентификатор, совпадающий с количеством элементов в массиве. Разделителем элементов является запятая ',' Тест выполняется с помощью утилиты SQLQueryStress через запуск решения 10 раз, на основании полученных данных вычисляется среднее время выполнения.

Тестовые данные

Для подготовки данных использовал...
  Сценарий
if object_id('dbo.table1') is not null
    drop table dbo.table1
create table dbo.table1(id bigint)

create unique index UIX_Table1_Id on dbo.table1(id)

if object_id('dbo.table2') is not null
    drop table dbo.table2
create table dbo.table2(id int primary key, ids nvarchar(max))

/*** TABLE1 ***/
;with cte as
(
    select * 
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(n)
)
-- Вставляем 10 000 000 записей в dbo.table1
insert into dbo.table1
select top 10000000 t1.n + t2.n * 10 + t3.n * 100 + t4.n * 1000 + t5.n * 10000 + t6.n * 100000 + t7.n * 1000000
from cte t1, cte t2, cte t3, cte t4, cte t5, cte t6, cte t7
order by 1

/*** TABLE2 ***/
declare @delimiter as char(1) = ',' -- Разделитель списка значений
declare @n int
declare numbers cursor fast_forward for

select *
from (values (500), (1000), (5000), (10000), (50000), (100000), (250000), (500000), (1000000)) a(N)

open numbers 
fetch next from numbers into @n

while @@fetch_status = 0
begin
    -- Создаем набор произвольных элементов равный по кол-ву @n
    insert into dbo.table2(id, ids)
    select @n, stuff(
    (
        select top(@n) @delimiter + cast(id as varchar(20))
        from dbo.table1
        order by row_number() over(order by newid())
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '')

    fetch next from numbers into @n
end
close numbers
deallocate numbers

Решение

Реализовано в запросах...
  Сценарий
/* 1-5 */
select tt.Value
from dbo.table2 t2 
cross apply dbo.SplitN(t2.ids, ',') tt
where t2.id = K

/* где N = 1 – Цикл, 2 – CTE, 3 – XML, 4 – Таблица, 5 – CLR */

/* 6 */
select j.value
from dbo.table2 t2
cross apply openjson('[' + t2.ids + ']') j
where t2.id = K

/* 7 */
select t1.*
from dbo.Tally t1, dbo.table2 t2
where t2.id = K and ',' + t2.ids + ',' like '%,' + cast(t1.N as varchar(20)) + ',%'
-- или
where t2.id = K and charindex(',' + cast(t1.N as varchar(20)) + ',', ',' + t2.ids + ',') > 0
-- или
where t2.id = K and patindex('%,' + cast(t1.N as varchar(20)) + ',%', ',' + t2.ids + ',') > 0

/* 8 */
select j.value
from dbo.table2 t2
cross apply string_split(t2.ids, ',') j
where t2.id = K

/* где K = 500, 1000, 5000, 10000, 50000, 100000, 250000, 500000, 1000000 */

Результат

Для наглядности результаты сравнения производительности представлены в виде графика. Ось 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 обладающая всеми преимуществами варианта из "коробки" показала неплохую производительность. Пожалуй единственный недостаток заключается в сепараторе, который может быть не больше одного символа. Возможно при условии, что вы хорошо знаете свои данные этот недочет можно обойти.
Отредактировано 17.12.2016 21:31 AndrewVK (.ru -> .org) . Предыдущая версия . Еще …
Отредактировано 15.03.2016 4:32 Olaf . Предыдущая версия .
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.