Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Рекурсивный вызов функции.  [new]
Dzianis
Member

Откуда:
Сообщений: 76
Есть определенный код (функции), вызывающие сами себя.
с трудом понимаю, почему конструкция
select id
from fnF1(@string, @delimiter)
WHERE CHARINDEX('|', @string) <> 0

вообще передает управление в фукнцию, если по входным параметрам условие не выполняется.
в итоге получаю при входных параметрах SELECT * FROM fnF2('3',',') бесконечную рекурсию.

Сформулирую то что интересует.
1) можно ли изменить блок так, чтобы управление в функцию не попадало когда в строке нет '|'?
select id
from fnF1(@string, @delimiter)
WHERE CHARINDEX('|', @string) <> 0
2) как определить "уровень" цикла самовызова в фукнции fnF1, чтобы добавить дополнительное условие от зацикливания.

DROP FUNCTION IF EXISTS fnF1
DROP FUNCTION IF EXISTS fnF2
GO
CREATE FUNCTION fnF1(@string varchar(max), @delimiter char(1))
RETURNs  @output  table (id int not null)
as
BEGIN
	
	IF CHARINDEX('|', @string) = 0
	begin
		INSERT @output (id)
		SELECT intSplit.Id 
			FROM fnF2(@string, @delimiter) AS intSplit
	END ELSE BEGIN
		INSERT @output (id)
		SELECT intSplit.Id 
			FROM string_split(@string, '|') svalue
				CROSS APPLY fnF2(svalue.value, @delimiter) AS intSplit
	END

	RETURN
END
GO
CREATE FUNCTION fnF2(@string varchar(max), @delimiter char(1))
RETURNs table
as
return
	select id 
	from fnF1(@string, @delimiter)
	WHERE CHARINDEX('|', @string) <> 0
	UNION ALL
	SELECT DISTINCT CAST(LTRIM(RTRIM(Value)) AS INT) as id
	FROM string_split(@string, @delimiter)
    WHERE len(LTRIM(RTRIM(Value)))>0
    AND CHARINDEX('|', @string) = 0

GO

SELECT * FROM fnF2('3',',')
13 ноя 18, 17:26    [21733317]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный вызов функции.  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Ну и жуть...

declare @s varchar(max) = '3', @delimeter char(1) = ',';

select distinct
 b.value
from
 string_split(replace(@s, @delimeter, '|'), '|') a cross apply
 (select rtrim(ltrim(a.value))) b(value)
where
 b.value > '';
13 ноя 18, 18:27    [21733426]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный вызов функции.  [new]
Dzianis
Member

Откуда:
Сообщений: 76
invm
Ну и жуть...

declare @s varchar(max) = '3', @delimeter char(1) = ',';

select distinct
 b.value
from
 string_split(replace(@s, @delimeter, '|'), '|') a cross apply
 (select rtrim(ltrim(a.value))) b(value)
where
 b.value > '';


э.. спс )
Но это не поясняет заданные вопросы.
Я упростил реальный код чтобы показать конкретно то, что меня удивило.
в реальном проекте там более сложные блоки

Пока ушел в вариант

CREATE FUNCTION fnF2(@string varchar(max), @delimiter char(1))
RETURNs table
as
return
	select ss.id 
	from (select @string as string, CHARINDEX('|', data.string) as I) as data
               cross apply fnF1(data.string, @delimiter) as ss
	WHERE data.I <> 0
	UNION ALL
	SELECT DISTINCT CAST(LTRIM(RTRIM(Value)) AS INT) as id
	FROM string_split(@string, @delimiter)
    WHERE len(LTRIM(RTRIM(Value)))>0
    AND CHARINDEX('|', @string) = 0
13 ноя 18, 19:43    [21733519]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный вызов функции.  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Dzianis
это не поясняет заданные вопросы.
См. план выполнения - там фильтр применяется к результату функции fnF1, т.е. уже после ее выполнения.
Так происходит потому что значения параметров при вызове - константы времени выполнения. Результат такой функции считается статическим и кешируется для возможного многократного использования далее в запросе.

Поставив костыль в виде cross apply, вы обманули оптимизатор и теперь он считает значения параметров не константными.
Но никто не гарантирует, что в следующей версии или в SP, или в CU оптимизатор не поумнеет и не станет опять считать параметры константными.

ЗЫ: В 99.9% случаев, подобные вещи, даже при наличии "более сложных блоков кода", решаются без подобных рекурсивных извращений. Пример как это сделать был продемонстрирован.
13 ноя 18, 22:40    [21733632]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный вызов функции.  [new]
Dzianis
Member

Откуда:
Сообщений: 76
invm
Так происходит потому что значения параметров при вызове - константы времени выполнения. Результат такой функции считается статическим и кешируется для возможного многократного использования далее в запросе.

В общем то случае разумно. Раз юзаются переменные "извне", значит 1 раз посчитать и запомнить.

invm
ЗЫ: В 99.9% случаев, подобные вещи, даже при наличии "более сложных блоков кода", решаются без подобных рекурсивных извращений. Пример как это сделать был продемонстрирован.


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

Есть ли еще другие способы "более стабильно" обмануть оптимизатор?
13 ноя 18, 23:26    [21733650]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный вызов функции.  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Dzianis
Есть ли еще другие способы "более стабильно" обмануть оптимизатор?
CREATE FUNCTION fnF2(@string varchar(max), @delimiter char(1))
RETURNs table
as
return
	select b.id 
	from
     (select case when @string > '' then @string end where CHARINDEX('|', @string) <> 0) a(string) cross apply
     fnF1(a.string, @delimiter) b
	UNION ALL
	SELECT DISTINCT CAST(LTRIM(RTRIM(Value)) AS INT) as id
	FROM string_split(@string, @delimiter)
    WHERE len(LTRIM(RTRIM(Value)))>0
    AND CHARINDEX('|', @string) = 0
13 ноя 18, 23:59    [21733672]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный вызов функции.  [new]
Dzianis
Member

Откуда:
Сообщений: 76
invm,
большое спасибо!
14 ноя 18, 09:33    [21733797]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить