Tag Archives: Strings

SQL Server 2016: STRING_SPLIT, описание и сравнение производительности

Свершилось! Наконец-то в SQL Server 2016 появляется встроенная функция для разбивки строк. Пожалуй, это одна из самых распространённых функций, которые, пожалуй, присутствуют на каждом SQL Server, но до текущей версии были реализованы своими силами. Это та функция, для которой существует множество самых различных реализаций, и, зачастую, не самых быстрых. Давайте поближе взглянем на возможности функции STRING_SPLIT, ее ограничения и сравним ее по скорости с самыми распространенными решениями.

Функция принимает всего 2 параметра: собственно, саму строку, которую необходимо разбить и разделитель. Существует одно очень важное и не очень понятное ограничение, разделитель может быть только длиной один символ. Поэтому, если вам требуется разбить строку по большему разделителю, то эта функция для вас не подойдет, и вам нужно искать другое решение.

На текущий момент одним из самых быстрых решений по разбивки строк считается CLR функция, написанная Adam Machanic (ссылка на статью). Также я решил привести пару других примеров функции, реализация в лоб, а также через inline функцию с соединением с таблицей чисел. Последний из этих вариантов на строках небольшой длины обычно показывает неплохие результаты по сравнению с реализацией в лоб. Ну и сравним скорость со стандартной функцией, входящей в SQL Server 2016. Для начала подготовим таблицу с числами и добавим 2 вышеуказанные функции. Как скомпилировать и добавить CLR функцию, я не привожу, т.к. надеюсь, каждый сможет это сделать самостоятельно.

use test;
go

if object_id('dbo.num', 'U') is not null
	drop table dbo.num;
go

create table dbo.num (
	n int primary key clustered
);
go

insert into dbo.num(n)
select top (1000000) row_number() over (order by (select 1)) as rn
from sys.all_columns as t1
cross apply sys.all_columns as t2
go

if object_id('dbo.fn_split', N'TF') is not null
	drop function dbo.fn_split;
go

create function dbo.fn_split
(
    @str nvarchar(max),
	@del nchar(1)
)
returns @returntable table 
(
	st nvarchar(4000)
)
as
begin
    
	while charindex(@del, @str) > 0
	begin
		insert into @returntable select substring(@str, 1, charindex(@del, @str) - 1)
		set @str = substring(@str, charindex(@del, @str) + 1, len(@str))
	end
	if @str <> ''
		insert into @returntable values(@str)

	return
end
go

if object_id('dbo.fn_split2', 'if') is not null
	drop function dbo.fn_split2;
go

create function dbo.fn_split2 (
	@str nvarchar(max),
	@del nchar(1) = ','
) returns table
as
return
	select
		substring(@str, n, case when charindex(@del, @str, n) = 0 then len(@str) else charindex(@del, @str, n) - n end) as st
	from dbo.num
	where
		n <= len(@str)
		and (n = 1 or substring(@str, n - 1, 1) = @del)
go

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

set statistics time on

declare @str varchar(max) = (select top (20000) t1.name + ',' as 'text()'
from sys.all_columns as t1
cross apply sys.all_columns as t2
for xml path(''))

set @str = substring(@str, 1, len(@str) - 1)

select *
from dbo.split_string_clr(@str, ',');

select *
from dbo.fn_split(@str, ',') as f;

select *
from dbo.fn_split2(@str, ',') as f;

select *
from string_split(@str, ',');

Ну и приведу один из полученных результатов.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 113 ms.

SQL Server Execution Times:
CPU time = 13468 ms, elapsed time = 13759 ms.

SQL Server Execution Times:
CPU time = 143875 ms, elapsed time = 143989 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 216 ms.

Как мы видим, CLR функция и STRING_SPLIT оставляют далеко позади остальные решения, а вот выбрать, кто из них быстрее становится проблематично, т.к. их время запуска практически не отличается и варьируется от теста к тесту. В данном случае расходы на CPU одинаковые, а время выполнения медленнее. Однако, в каких-то запусках, время выполнения становится сравнимым, поэтому я не берусь отдать пальму первенства ни одной из них. Это действительно круто, что наконец-то появляется встроенное решение с превосходной производительностью. Но, если вам потребуется использовать разделитель больше чем в один символ, то рекомендую пользоваться CLR решением.

Про скорость поиска по строкам и таблицу с настройками

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

use [tempdb];
go


if object_id('dbo.settings', 'U') is not null
	drop table dbo.settings;
go

create table dbo.settings (
	[name] varchar(128) not null,
	[value] sql_variant null,

	constraint [pk_settings] primary key clustered ([name])
);
go

Настройки могут быть самые разные: какие-то процессы могут постоянно обновлять свой статус, хранить дату последней обработки данных; также это могут быть какие-то параметры для внешних сервисов или ETL процессов. В общем, способов применения очень много, и такой подход встречается очень часто. Но я, конкретно, хотел бы остановиться на одной довольно распространенной ошибке, которая встречается в этом случае, да и в некоторых других. Очень часто названия параметров содержат довольно большой одинаковый префикс, например: Process1_config_param1, Process1_last_active_time, Process2_config_param1, Process2_last_active_time и т.п. Я надеюсь, вы уловили смысл. У нас в базе данных могут быть десятки, сотни или даже тысячи строк, у которых 5-30 первых символов совпадает. И это может повлиять на производительность, т.к. при поиске по названию придется проверять все эти строки. Давайте устроим небольшой тест на производительность и посмотрим, как себя ведет SQL Server.

В первом тесте я вставлю 1000 значений в таблицу, у которых первые 10 символов будут одинаковые, а последние 4 уникальные, и запущу подряд 3 миллиона выборок по одному из значений.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select replicate('A', 10) + replicate('0', 4 - ceiling(log(rn + 1, 10))) + cast(rn as varchar(10)), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;

declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = 'AAAAAAAAAA0100'

	set @i += 1

end

В следующем тесте я сделаю первые 4 символа уникальные, а остальные совпадающие. Т.е. я убрал все повторяющиеся куски в конец строки, а уникально идентифицирующие в начало.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000') + replicate('A', 10), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100AAAAAAAAAA'

	set @i += 1

end

Ну и в финальном тесте я вообще убрал повторяющиеся значения, а оставил только короткие уникальные наименования.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000'), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100'

	set @i += 1

end

Вы можете запустит все 3 теста у себя и сравнить результаты. Я же хочу поделиться своими. Тесты запускались несколько раз, результаты были достаточно стабильны. Точное время выполнения приводить не имеет смысла, поделюсь лишь сравнительными итогами. Если взять за основу первое решение, то второй вариант относительно него выполняется на 10% быстрее, что очень существенно. Если взять третий тест, то он будет приблизительно на 15% быстрее чем первый и чуть больше, чем на 5% быстрее, чем второй.

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