Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Непонятки с производительностью STRING_SPLIT  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Думал встроенная STRING_SPLIT будет явно быстрее моего велосипеда (не помню откуда стыреного и мной допиленного).

create FUNCTION [dbo].[fn_splitter]( @String NVARCHAR(4000), @ch char )
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN 
(
    WITH Split( stpos, endpos ) 
    AS(
        SELECT 0          AS stpos, CHARINDEX(@ch, @String)              AS endpos
        UNION ALL
        SELECT endpos + 1 AS stpos, CHARINDEX(@ch, @String, endpos + 1 ) AS endpos 
          FROM Split
          WHERE endpos > 0
    )
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id,
           LTRIM(RTRIM(SUBSTRING( @String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String) + 1) - stpos))) AS Item
      FROM Split
)
GO


В таблице 176 тыс значений с пробелами, вставка 410тыс значений.

select l.lake_name, f.Item into testa0 from lake l
cross apply dbo.fn_splitter1( l.lake_name, ' ' ) f
where CHARINDEX(' ', l.lake_name ) > 0 


С моей функцией вставка 1 сек, со встроеной фунцией 7 сек.

select l.lake_name, f.value into testb0 from lake l
cross apply STRING_SPLIT( l.lake_name, ' ' ) f
where CHARINDEX(' ', l.lake_name ) > 0 


Где засада?
13 авг 18, 18:21    [21640978]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Lepsik,

CHARINDEX? STRING_SPLIT не требует проверки.
13 авг 18, 18:37    [21640996]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Lepsik,

STRING_SPLIT в текущей реализации тот еще тормоз, но ваш "велосипед" еще тормознее.
Так что вы, видимо, что-то где-то напутали.
13 авг 18, 22:04    [21641175]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Владислав Колосов
Lepsik,

CHARINDEX? STRING_SPLIT не требует проверки.


каким образом where на что-то повлияет? Без него тот же результат. 1:7
13 авг 18, 23:40    [21641213]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
invm
Lepsik,

STRING_SPLIT в текущей реализации тот еще тормоз, но ваш "велосипед" еще тормознее.
Так что вы, видимо, что-то где-то напутали.


Есть что по существу сказать? Репо я выложил.
13 авг 18, 23:41    [21641214]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Lepsik
invm
Lepsik,

STRING_SPLIT в текущей реализации тот еще тормоз, но ваш "велосипед" еще тормознее.
Так что вы, видимо, что-то где-то напутали.


Есть что по существу сказать? Репо я выложил.
Я запустил, на маленькой таблице (1000) функция медленнее в 5 раз
На большой (1000000) сплит медленее в 10 раз (6 и 60 секунд)
На маленькой таблице, но с большими строками (1000 по 8000 символов), сплит выполняется 180 секунд, а функция падает The maximum recursion 100 has been exhausted before statement completion.
Ну и вообще (если поправить maxrecursion) в ней ошибки: Invalid length parameter passed to the LEFT or SUBSTRING function.
Lepsik
Где засада?
Хм, никакой засады, сплит медленнее вашей функции при определённых условиях.
Зато она работает без ошибок.
14 авг 18, 00:18    [21641232]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Lepsik
Есть что по существу сказать? Репо я выложил.
Именно, что "репо".
Репро - это то, что можно взять, запустить и увидеть (или не увидеть) проблему. Так что вашему репо до репро еще очень далеко.

На форуме есть темы с обсуждением и примерами различных вариантов функций разбиения строки. Найдите себе среди них подходящий.
14 авг 18, 10:06    [21641408]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
STRING_SPLIT предназначен по своему духу для преобразования входного параметра в табличную форму. "Можно" не равно "нужно". Хранение в таблице неатомарных данных - ересь чистой воды. ;)
14 авг 18, 11:51    [21641600]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Владислав Колосов
STRING_SPLIT предназначен по своему духу для преобразования входного параметра в табличную форму. "Можно" не равно "нужно". Хранение в таблице неатомарных данных - ересь чистой воды. ;)
Да, поэтому было принято решение сделать его медленным :-)

По моему, просто это делали криворукие программисты, да ещё наверняка обходными путями (типа, вкорячили через .NET)
Это же надо, сделать его сравнимым или даже более медленным, чем парс рекурсивными запросами по строкам на T-SQL
Возмутительно.
14 авг 18, 12:25    [21641693]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Если бы просили меня, то я бы ответил, что использую для тех же целей SplitString_Multi от Adam Machanic. А то действительно нет доверия индусам :)
14 авг 18, 12:35    [21641715]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов,

Если бы просили меня, то я бы ответил, что если вам постоянно надо пользоваться сплитами, то у вас кака-то проблема в архитектуре.
14 авг 18, 12:39    [21641723]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
invm
Lepsik
Есть что по существу сказать? Репо я выложил.
Именно, что "репо".
Репро - это то, что можно взять, запустить и увидеть (или не увидеть) проблему. Так что вашему репо до репро еще очень далеко.

На форуме есть темы с обсуждением и примерами различных вариантов функций разбиения строки. Найдите себе среди них подходящий.
Например:

Функция, которая делит строку на слова
14 авг 18, 12:42    [21641728]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
TaPaK,

Я о том же. Надо хранить атомарные данные в таблицах. Разбивать требуется списки перечислений для процедур репортинг сервиса.
14 авг 18, 12:42    [21641730]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
alexeyvg
По моему, просто это делали криворукие программисты
Да. Только в другом месте :)

1. Функция split_string действительно тормозная;
2. Тем не менее, split_string гораздо быстрее функции ТС'а;
3. Есть аномальное поведение split_string в параллельных планах.

+ Репро
use tempdb;
go

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

create FUNCTION [dbo].[fn_splitter]( @String NVARCHAR(4000), @ch char )
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN 
(
    WITH Split( stpos, endpos ) 
    AS(
        SELECT 0          AS stpos, CHARINDEX(@ch, @String)              AS endpos
        UNION ALL
        SELECT endpos + 1 AS stpos, CHARINDEX(@ch, @String, endpos + 1 ) AS endpos 
          FROM Split
          WHERE endpos > 0
    )
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id,
           LTRIM(RTRIM(SUBSTRING( @String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String) + 1) - stpos))) AS Item
      FROM Split
)
GO

/************************/
/* Settings of the Test */
/************************/
declare
 @s nvarchar(max),
 @pl int = 30,
 @pc int = 5,
 @rc int = 100000;
/************************/

select
 @s = stuff((
  select top (@pc) ',' + right(replicate('0', @pl) + cast(row_number() over (order by (select 1)) as nvarchar(30)), @pl) from (select a.number from master.dbo.spt_values a cross join master.dbo.spt_values b) t for xml path(''), type
 ).value('.', 'nvarchar(max)'), 1, 1, '');

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

select top(@rc)
 row_number() over (order by (select 1)) as n, @s as s
into
 #t
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

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

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

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

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

set statistics xml, time on;
go

select t.n, a.Item into dbo.t1 from #t t cross apply dbo.fn_splitter(t.s, ',') a option (querytraceon 8649);
select t.n, a.Item into dbo.t2 from #t t cross apply dbo.fn_splitter(t.s, ',') a option (maxdop 1);

select t.n, a.Value into dbo.t3 from #t t cross apply string_split(t.s, ',') a option (querytraceon 8649);
select t.n, a.Value into dbo.t4 from #t t cross apply string_split(t.s, ',') a option (maxdop 1);
go

set statistics xml, time off;
go

drop function dbo.fn_splitter;
drop table dbo.t1, dbo.t2, dbo.t3, dbo.t4;
go
14 авг 18, 13:41    [21641823]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm,

автор
3. Есть аномальное поведение split_string в параллельных планах.


на что смотреть?
14 авг 18, 15:38    [21642036]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
TaPaK
на что смотреть?
На CPU при сериальном и параллельном планах.
14 авг 18, 15:46    [21642051]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
TaPaK
Владислав Колосов,

Если бы просили меня, то я бы ответил, что если вам постоянно надо пользоваться сплитами, то у вас кака-то проблема в архитектуре.


Представьте что ваша работа - писать репорты, где в качестве аргументов могут использоватся списки.

Архитектура плохая?
14 авг 18, 16:09    [21642101]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Lepsik
TaPaK
Владислав Колосов,

Если бы просили меня, то я бы ответил, что если вам постоянно надо пользоваться сплитами, то у вас кака-то проблема в архитектуре.


Представьте что ваша работа - писать репорты, где в качестве аргументов могут использоватся списки.

Архитектура плохая?

вы точтно понимаете, что означает архитектура?
14 авг 18, 16:10    [21642103]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
TaPaK
на что смотреть?
На CPU при сериальном и параллельном планах.

Ну как по мне здесь не из-за STRING_SPLIT, а из за вставки.
14 авг 18, 16:14    [21642110]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
TaPaK
Ну как по мне здесь не из-за STRING_SPLIT, а из за вставки.
Поясните, что именно вызвало рост CPU time в параллельном плане с string_split, в сравнении с сериальным и не вызвало в аналогичном с fn_splitter.
14 авг 18, 16:57    [21642175]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
TaPaK
Ну как по мне здесь не из-за STRING_SPLIT, а из за вставки.
Поясните, что именно вызвало рост CPU time в параллельном плане с string_split, в сравнении с сериальным и не вызвало в аналогичном с fn_splitter.

Ну я смотрел только на string_split.
Сам оператор в параллельном плане даёт 4572/299 против 2145/2145 в последовательном. Это на MAXDOP 16. Говорить про аномальное поведение как минимум странно.
14 авг 18, 17:02    [21642182]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm,

ну и

INSERT INTO	dbo.t1  select t.n, a.Item from #t t cross apply dbo.fn_splitter(t.s, ',') a option (querytraceon 8649, MAXDOP 16);
INSERT INTO	dbo.t2  select t.n, a.Value from #t t cross apply string_split(t.s, ',') a option (use hint('ENABLE_PARALLEL_PLAN_PREFERENCE'),MAXDOP 16);

даёт абсолютно обратную картину, имхо вопрос именно к SELECT INTO которая как по мне проклята :)
14 авг 18, 17:16    [21642203]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
TaPaK,

У меня для string_split парллельный/сериальный CPU time - 7689/3078
А должно быть сопоставимо. Что и видно для fn_splitter - 6155/5750
14 авг 18, 17:48    [21642251]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
TaPaK,

У меня для string_split парллельный/сериальный CPU time - 7689/3078
А должно быть сопоставимо. Что и видно для fn_splitter - 6155/5750

вы про выполнение целиком?
14 авг 18, 17:51    [21642254]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с производительностью STRING_SPLIT  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
invm
1. Функция split_string действительно тормозная;
2. Тем не менее, split_string гораздо быстрее функции ТС'а;
3. Есть аномальное поведение split_string в параллельных планах.
Так вот у ТС и получается своя функция быстрее split_string, именно из за тормознутости последней в параллельных планах. Это и по вашему репро видно.
В 9 раз тормознутее, если split_string выполняется параллельно, и в 5 раз - если она выполняется в одном потоке, а функция ТС'а во многих (там, понятно, сама split_string быстрее, но функция ТС'а берёт параллельным выполнением)
ТС'а
Для миллиона строк:
function queryoptionCPUdurationreads
fn_splitter querytraceon 8649634061782046081026
fn_splitter maxdop 1 19878120803346077786
string_split querytraceon 86492631411404066515962
string_split maxdop 1 79266878376515919
14 авг 18, 18:24    [21642297]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить