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

Откуда:
Сообщений: 58
Здравствуйте.
Помогите, пожалуйста, новичку.
Как в создаваемую табличную функцию (Inline Table-Valued Function) добавить ветвление в зависимости от введенного параметра. Ниже в коже прилагаю пример создания функции MonthList, которая выводит список месяцев в зависимости от введенного параметра - номера квартала (@QNum). Значение по умолчанию 0. Необходимо сделать так, чтобы при использовании параметра по умолчанию, функция возвращала полный список месяцев.

Заранее благодарен за помощь.

IF OBJECT_ID(N'Months', N'U') IS NOT NULL
	DROP TABLE Months;
GO
CREATE TABLE Months (MonthNum tinyint, QuarterNum tinyint);
GO
DECLARE @month as tinyint
SET @month = 1
WHILE @month <=12
	BEGIN
		INSERT INTO Months(MonthNum, QuarterNum)
		VALUES (@month
				,CASE
					WHEN @month <=3 THEN 1
					WHEN @month <=6 THEN 2
					WHEN @month <=9 THEN 3
					WHEN @month <=12 THEN 4
				END);
		SET @month = @month+1
	END;
GO

IF OBJECT_ID(N'MonthList', N'IF') IS NOT NULL
	DROP FUNCTION MonthList;
GO

CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum = @QNum
	);
GO

SELECT * FROM MonthList(DEFAULT);
GO
8 июл 16, 01:55    [19383765]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum = case @QNum when 0 then QuarterNum else @QNum end
	);
GO
8 июл 16, 02:02    [19383771]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum = isnull(nullif(@QNum,0),QuarterNum)
	);
GO
8 июл 16, 02:17    [19383786]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
aleks2
Guest
3unknown
CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum = isnull(nullif(@QNum,0),QuarterNum)
	);
GO


Зачем этот изврат?

CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum =  @QNum or @QNum = 0
	);
GO
8 июл 16, 06:07    [19383838]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
FOX75
Member

Откуда:
Сообщений: 29
Я не эксперт..., но мож через union all?
8 июл 16, 09:48    [19384338]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
dmitriy_po
Member

Откуда:
Сообщений: 58
Благодарю за ответы.

А допустимо ли при создание функции в синтаксисе использовать конструкцию IF... ELSE..? Если параметр такой-то, то такой запрос, если другой параметр, то другой запрос?
8 июл 16, 10:00    [19384390]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
dmitriy_po
Благодарю за ответы.

А допустимо ли при создание функции в синтаксисе использовать конструкцию IF... ELSE..? Если параметр такой-то, то такой запрос, если другой параметр, то другой запрос?

создание функции позволяет, инлайновой - нет
8 июл 16, 10:05    [19384417]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
правильный проходящий.
Guest
dmitriy_po
Благодарю за ответы.

А допустимо ли при создание функции в синтаксисе использовать конструкцию IF... ELSE..? Если параметр такой-то, то такой запрос, если другой параметр, то другой запрос?
Если запросы возвращают одинаковые по структуре результаты, то можно их обхеднить через union, но в каждый добавить условие по параметру. В качестве примера кусок из инлайновой функции:
	select eur, usd from
	(Select Top 1 eur, usd From cambio Where Data<=@Data_Cambio and Not exists(select cmb_fix from sistema where sistema.cmb_fix = 1) Order By Data Desc) c
	union
	Select Top 1 eur, usd From sistema where sistema.cmb_fix = 1
8 июл 16, 10:16    [19384469]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
aleks2
Guest
FOX75
Я не эксперт..., но мож через union all?

Можно.
Только надо понимать чего вы при этом теряете.
8 июл 16, 10:42    [19384578]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
Владислав Колосов
Member

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


Это опасная идея. "Универсальность" запроса может привести к тому, что планировщик может выбрать неоптимальный план.
Надо делать или две ветки кода или UNION, как написано выше.
8 июл 16, 11:25    [19384827]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
iljy
Member

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

такие вещи реализуют через union all. Причина банальна: в варианте с or или case, если параметр не известен при компиляции, планировщик всегда выберет полное сканирование таблицы. Использование union all позволяет строить более оптимальные даже универсальные планы.
8 июл 16, 11:43    [19384986]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
o-o
Guest
Владислав Колосов
dmitriy_po,
Необходимо сделать так, чтобы при использовании параметра по умолчанию, функция возвращала полный список месяцев.


Это опасная идея. "Универсальность" запроса может привести к тому, что планировщик может выбрать неоптимальный план.
Надо делать или две ветки кода или UNION, как написано выше.

серьезно?
и какие же варианты плана при выборе 1 строки из 12 или всех 12?
-------
а главное, зараза начала распространяться (источник МуМу?)
оптимизатор отныне планировщик.
а планировщих(допустим, виндовый) тогда кто, оптимизатор?
8 июл 16, 11:44    [19384993]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
aleks2
Guest
iljy
dmitriy_po,

такие вещи реализуют через union all. Причина банальна: в варианте с or или case, если параметр не известен при компиляции, планировщик всегда выберет полное сканирование таблицы. Использование union all позволяет строить более оптимальные даже универсальные планы.


Наивный.
Union All ликвидирует всякую возможность использования индексов при фильтрации результатов функции.
8 июл 16, 11:52    [19385078]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
iljy
Member

Откуда:
Сообщений: 8711
o-o
серьезно?
и какие же варианты плана при выборе 1 строки из 12 или всех 12?


Выполните и посмотрите
create table #ttt
(
	id int primary key
)

insert into #ttt
select top(12)  ROW_NUMBER() over(order by 1/0)
from master..spt_values

declare @x int = 5

select * from #ttt
where id = @x or @x = 0

select * from #ttt
where @x = 0
	union all
select * from #ttt
where @x <> 0 and id = @x



drop table #ttt
8 июл 16, 11:53    [19385092]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2
Наивный.
Union All ликвидирует всякую возможность использования индексов при фильтрации результатов функции.


Учится тебе надо, барин.
8 июл 16, 11:56    [19385119]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
aleks2
Guest
iljy
aleks2
Наивный.
Union All ликвидирует всякую возможность использования индексов при фильтрации результатов функции.


Учится тебе надо, барин.


Ученого учить - тока портить

create table #ttt
(
	id int 
    , n int

    primary key(n, id)

)

insert into #ttt
select top(12)  ROW_NUMBER() over(order by 1/0), 1
from master..spt_values

insert into #ttt
select top(12)  ROW_NUMBER() over(order by 1/0), 2
from master..spt_values

declare @x int = 5

select * from #ttt
where ( id = @x or @x = 0 )
     and n = 1

     
select *
from (
select * from #ttt
where @x = 0
	union all
select * from #ttt
where @x <> 0 and id = @x
) as t
where n = 1;

drop table #ttt
8 июл 16, 11:59    [19385138]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
iljy
Member

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

эмммм.... и где там не используются индексы?? Даже наоборот, используются более эффективно, поскольку в варианте с or всегда идет поиск долько по одному значению, т.е. по сути то же сканирование. Ты б хоть свои запросы запускал чтоли.

К сообщению приложен файл (ttt.sqlplan - 36Kb) cкачать
8 июл 16, 12:05    [19385193]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
o-o
Guest
iljy
o-o
серьезно?
и какие же варианты плана при выборе 1 строки из 12 или всех 12?


Выполните и посмотрите
create table #ttt
(
	id int primary key
)

insert into #ttt
select top(12)  ROW_NUMBER() over(order by 1/0)
from master..spt_values

declare @x int = 5

select * from #ttt
where id = @x or @x = 0

select * from #ttt
where @x = 0
	union all
select * from #ttt
where @x <> 0 and id = @x



drop table #ttt

ну и?
какой же вариант просел в производительности?
что-то в обоих 2 логических чтения.
не вижу разницы между 2 и 2.
что 12 строк, что 1, в таблице из 12 строк с содержанием month/quarter лежат на одной странице
8 июл 16, 12:05    [19385194]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
aleks2
Guest
iljy
aleks2,

эмммм.... и где там не используются индексы?? Даже наоборот, используются более эффективно, поскольку в варианте с or всегда идет поиск долько по одному значению, т.е. по сути то же сканирование. Ты б хоть свои запросы запускал чтоли.


Извиняй, глазки я не лечу.
8 июл 16, 12:06    [19385212]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
iljy
Member

Откуда:
Сообщений: 8711
o-o
ну и?
какой же вариант просел в производительности?
что-то в обоих 2 логических чтения.
не вижу разницы между 2 и 2.
что 12 строк, что 1, в таблице из 12 строк с содержанием month/quarter лежат на одной странице


на 12 ее и не будет просто так заметно. а на 1000 - очень даже. Планы разные, это видно. А на таблице из 12 строк и индекс-то особо не нужен
8 июл 16, 12:08    [19385224]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
aleks2
Guest
Для менее тупых.

1. Инлайн-функция разворачивается оптимизатором в текст запроса.
2. Поэтому, если вы используете дополнительную фильтрацию результатов функции, то оптимизатор может применить этот фильтр к таблице ДО фильтра функции.
3. UNION ALL в функции исключает такую возможность.
8 июл 16, 12:10    [19385250]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
На малых количествах нет смысла смотреть и на неиспорченных статистиках. Это сферический конь в вакууме.
8 июл 16, 12:12    [19385265]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
aleks2
Union All ликвидирует всякую возможность использования индексов при фильтрации результатов функции.
Есть такой механизм - "predicate pushdown". Настоятельно рекомендую изучить.
8 июл 16, 12:14    [19385277]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
o-o
Guest
iljy
на 12 ее и не будет просто так заметно. а на 1000 - очень даже. Планы разные, это видно. А на таблице из 12 строк и индекс-то особо не нужен

так вы коня тут обсуждаете или 12 строк ТС-а?
у меня вообще-то в уточняющем вопросе повторно 12 строк упоминаются.
и еще раз, никагого "не так заметно" не наблюдаю.
у обоих запросов 2 чтения
8 июл 16, 12:15    [19385287]     Ответить | Цитировать Сообщить модератору
 Re: Ветвление в табличной функции (Inline Table-Valued Function)  [new]
iljy
Member

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

some people never learn.

Для тех, кто не писатель: в приведенном примере по плану видно, что дополнительное условие очень даже убрано оптимизатором внутрь функции. Для активных членов лиги лени - вот запрос, переделанный под использование функции (чтоб уж наверняка)

create table ttt
(
	id int, n int, primary key(n,id)
)

insert into ttt
select top(12) ROW_NUMBER() over(order by 1/0), 1
from master..spt_values
	union all
select top(12) ROW_NUMBER() over(order by 1/0), 2
from master..spt_values

go

create function f(@x int) returns table as
return (
	select * from ttt
	where @x = 0
		union all
	select * from ttt
	where @x <> 0 and id = @x
)
go


declare @x int = 5

select * from dbo.f(@x)
where n = 1

drop function dbo.f
drop table ttt
8 июл 16, 12:17    [19385295]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить