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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Попытаюсь объяснить проблему на примерах (не везде, но по возможности упрощённых).

Есть вот такая функция изначально

ALTER function [dbo].[fnCommonDictionaryGetTreeByPkOrSyn]
(
 @pk uniqueidentifier, --primary key of ROOT in heirarchy
 @synonym nvarchar(255), --synonym of @pk
 @deleted bit --logical deletion "null" - all values
)
returns table
AS return

(

 select cd.pk,
        cd.fkOwner,
        cd.hrchLevel - td.hrchLevel + 1 level,
        cd.deleted,
        cd.hrchID treeSequence,
        cd.fkType
        
   from dbo.tCommonDictionary  td with(nolock)
  inner
   join (select coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym)) as pk) p
     on td.pk = p.pk
     or p.pk is null and td.fkOwner is null 
     
  inner
   join dbo.tCommonDictionary cd with(nolock)
     on cd.hrchID.IsDescendantOf(td.hrchID) = 1 
    and (cd.deleted = @deleted or @deleted is null)
     
  where  (td.deleted  = @deleted or @deleted is null)

)


таблица dbo.tCommonDictionary - обычный иерархический справочник.

в нём есть второе "уникальное текстовое поле" установленное для некоторых записей "synonym".
Функция dbo.fnCommonDictionaryGetPkBySyn(@synonym) получает первичный ключ по синониму (обычный подзапрос).

1) Для меня очень странно что подазпрос с параметрами (часто удобно там много написать) выполняется многократно, хотя если написать проверку в "where" и явно проверить td.pk = dbo.fnCommonDictionaryGetPkBySyn(@synonym) работает быстро.

т. е.

and (td.pk = coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym)) or coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym)) is null)


Только я так не хочу писать и хочу что бы мой вариант работал быстро и не вижу причин что бы он работал медленно.

если бы функция была бы не in-line (а я хочу inline), то я бы спокойно мог бы написать до этого

set @pk = dbo.fnCommonDictionaryGetPkBySyn(N'DIC_ATTR_INSTITUTION_PRCH_SYS')


2) В функциях вообще динамическими запросами не пахнет, а писать логику с "or" оператором тоже замедляет запрос
даже если это банальный

and (td.pk = @pk or @pk is null)


Что в процедурах обходится динамическими запросами

Как обходить подобные моменты?!
23 апр 12, 15:58    [12457702]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Могу сказать в добавок что

    and (  
           td.pk = @pk 
        or td.synonym = @synonym
        --or @synonym is null and @pk is null
        )


работает быстро

а

    and (  
           td.pk = @pk 
        or td.synonym = @synonym
        or @synonym is null and @pk is null
        )


уже дольше значительно при том что хоть один из параметров передан
23 апр 12, 16:03    [12457755]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Что вернет
select objectproperty(object_id('dbo.fnCommonDictionaryGetPkBySyn', 'FN'), 'IsDetermenistic')
?
23 апр 12, 16:32    [12458044]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
invm
Что вернет
select objectproperty(object_id('dbo.fnCommonDictionaryGetPkBySyn', 'FN'), 'IsDetermenistic')
?
inline function - 'IF'
SELECT * FROM master.dbo.spt_values WHERE type='O9T'
23 апр 12, 16:34    [12458064]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
invm
Member

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

она скалярная.
23 апр 12, 16:37    [12458107]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
hpv
Member

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

Сорри за оффтоп, а так по логике и должно быть?

or @synonym is null and @pk is null

Или
or (@synonym is null and @pk is null)
23 апр 12, 16:40    [12458135]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
hpv
NIIIK,

Сорри за оффтоп, а так по логике и должно быть?

or @synonym is null and @pk is null

Или
or (@synonym is null and @pk is null)


Да, но это один из тестовых вариантов @pk и @synonym по сути одно и то же (только одно GUID, а другое строка на которую можно хардкодить).

2invm, jap
Функуия, естественно, не детерменированная.

по сути пока как "затычку" поставил


  where  (td.deleted  = @deleted or @deleted is null)
    and (  
           td.pk = @pk 
        or td.synonym = @synonym
        or td.fkOwner is null and @synonym is null and @pk is null
        )
23 апр 12, 17:03    [12458380]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
NIIIK, иногда так пишут
WITH BlaBla AS (
	SELECT	BlaBla-BlaBla
....
)	SELECT * FROM BlaBla WHERE X = @X -- AND @Y IS ULL
UNION ALL
	SELECT * FROM BlaBla WHERE Y = @Y -- AND @X IS NULL
UNION ALL
	SELECT * FROM BlaBla WHERE @X IS NULL AND @Y IS NULL
Но видно что это логика клиента, а не сервера. В серверной такое писать низя.
На самом деле это из-за неидеальных интерфейсов клиента. Нормальный дэзёйнер UI оторвал бы кое чё.
Хотя и с точки зрения безопасности тоже не ахти. Выдаст там 100500 строк данных при определённых параметрах.
Обычно разные параметры несут разный бизнес-смысл (ну и выражение), что с точки зрения ...

NIIIK, только не воспринимайте это слишком близко. Немного лицемерил, сам такой же быдлокодер.
23 апр 12, 21:19    [12459505]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
aleks2
Guest
[quot NIIIK]Попытаюсь объяснить проблему на примерах (не везде, но по возможности упрощённых).

...
если бы функция была бы не in-line (а я хочу inline), то я бы спокойно мог бы написать до этого

set @pk = dbo.fnCommonDictionaryGetPkBySyn(N'DIC_ATTR_INSTITUTION_PRCH_SYS')


Как обходить подобные моменты?![/quot]

1. Нихера не понял.
2. 
[src]set @pk =  (select top 1 [что-то там] FROM dbo.fnCommonDictionaryGetPkBySyn(N'DIC_ATTR_INSTITUTION_PRCH_SYS'))

3. Проще фсего "подобные моменты" не создавать.
24 апр 12, 06:35    [12460403]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
>1. Нихера не понял.
1) инлайн табличная функция.
2) Параметры могут быть необязательными, если "не передано - не проверять"
3) Некоторые параметры могут быть переданы непосредственно по первичному ключу, а некоторые по уникальной строке.
Если фронтэнд хочет "отобразить список городов" - он передаст синоним с веткой городов, который "захардкоден".
Если кто-то выбрал какое-то значение на фронтэнде, то пердаст ГУИД.
Но в инлайн функции нельзя написать
set @pk = coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym));

зато можно написать

  inner
   join (select coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym)) as pk) p
     on td.pk = p.pk

Главное что бы это "прохавывалось"

>2.
set @pk =  (select top 1 [что-то там] FROM dbo.fnCommonDictionaryGetPkBySyn(N'DIC_ATTR_INSTITUTION_PRCH_SYS'))


Нет, так нельзя писать. dbo.fnCommonDictionaryGetPkBySyn не табличная функция.


>3. Проще фсего "подобные моменты" не создавать.
Кто же спорти, но я не хочу плодить кучу однотипных функций в зависимости от набора параметров. Я не хочу что бы фронтнд принимал решение "какую вызвать" (хотя по факту фронтэнд должен вызвать процедуру в которой запрос динамически генерится). Да и самому проще вызывать одну функцию.

___________________________________________________________________________________________________________________

Вот на этом скрине "не правильный план выполнения"

Хотя "подзапрос" в красном прямоугольнике должен возвращать одну запись. Функция конечно не детермнированная, но это не мешает выполнятся ей один раз когда я пишу условие

 where td.pk = coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym))


но уже хреново выполняется (хотя лучше чем в основном методе)

когда

and (td.pk = coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym)) or coalesce(@pk, dbo.fnCommonDictionaryGetPkBySyn(@synonym)) is null)
.

Ещё раз. Я хочу написать "inline table function", которая будет являтся в основном частью других запросов и не хочу превращать её в multi statement.

Я хочу решить проблему
1) единожды вычислить переменные в подзапросе
2) единожды выполнять условия а-ля
(cd.deleted = @deleted or @deleted is null)


К сообщению приложен файл. Размер - 72Kb
24 апр 12, 13:51    [12462768]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вот в такоем варианте выполняется значительно лучше (пока оставлено так).

К сообщению приложен файл. Размер - 61Kb
24 апр 12, 13:52    [12462776]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Если бы запрос был динамический, то при передаче только одного параметра он бы "написАлся так".

2Mnior
C union-ами колхозить не хочу, считаю что это хуже моего "or" оператора
Не люблю и OR (вообще не любли "Или то или то" в базах данных. Люблю когда "одно из множества" (может даже когда единичное значение в нём).

Пытаюсь заставить СКЛ сервер "правильно понимать".

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

ЧитатьНеОбязательноЭтоПример
Есть какая-то строка в отчете, туда входят группы из определённых веток МКБ. Каждая ветка имеет дочерние записи (многоуровневые). Листья справочника МКБ устанавливаются как диагнозы и сохраняются. Необходимо получить "какие ветки есть в этой группе/строчке отчёта", потом "необходимо определить какие листья/диагнозы есть в этой группе для всех веток", а после этого сгруппировать.


К сообщению приложен файл. Размер - 54Kb
24 апр 12, 14:01    [12462860]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
NIIIK
Кто же спорти, но я не хочу плодить кучу однотипных функций в зависимости от набора параметров. Я не хочу что бы фронтнд принимал решение "какую вызвать" (хотя по факту фронтэнд должен вызвать процедуру в которой запрос динамически генерится). Да и самому проще вызывать одну функцию.
Посыл понятен. :)
NIIIK
C union-ами колхозить не хочу, считаю что это хуже моего "or" оператора
Чём? Кажется вы не поняли.
В том-то и смысл. В зависимости от параметра включится только один нужный стайтмент в нём.
NIIIK
Пытаюсь заставить СКЛ сервер "правильно понимать".
Не получится. Нужно или постоянно рекомпилить запрос или чтоб в плане был выбор нужного плана. Третьего не дано, хоть тресни, это вам не магия.
NIIIK
... динамически генерирую запрос.
Это тоже самое что ReCompile, но только запутаннее в 10 раз.
NIIIK
Сама табличная функция нужна в других процедурах
Вот это уже хамба. Что-то не так в архитектуре.
24 апр 12, 17:26    [12464526]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mnior
В зависимости от параметра включится только один нужный стайтмент в нём.

теперь понял

Mnior
чтоб в плане был выбор нужного плана

не понимаю, не шарю. Что это и как?!

Mnior
Это тоже самое что ReCompile, но только запутаннее в 10 раз.

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

Mnior
Вот это уже хамба. Что-то не так в архитектуре.

Пока всё ещё думаю что "правильно".
Вопрос с "or" он для статического запроса всегда актуален
А вопрос с "расчётами в отдельном подзапросе и заставить выполнится его один раз" - только для in-line функции актуален. И ту же функцию активно не использую, в процедурах запросы напрямую, особенно динамических.

Но функции хочется оставить для прикладных задач/запросов. Когда-то они были написаны рекусивными CTE, потом было изменение на иерархический тип. Вообщем "мало ли".
24 апр 12, 17:54    [12464832]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Внутрях системы должна быть полная однозначность.
Никаких по синонимам, по ID/GUID или есть/нету фильтра.

Если на внутренности отчёта влияют параметры, то тут надо думать о реализации.
[Внутренности, означает что нельзя просто на Report наложить фильтр, а нужно, к примеру до группировок что-то отфильтровать]
Т.е. специально реализовать отчёт отдельно, т.е. кроме как из интерфейса он не вызывается.

Поффторяю в 100500 раз, это логика клиента, а не сервера. Серверу на вариации запросов должно быть фиолетово.
Что динамика, что ReCompile, что UNION ALL всё будет выходить боком в использовании ещё где-то (в серверной логике).
24 апр 12, 18:18    [12465028]     Ответить | Цитировать Сообщить модератору
 Re: Предложите логику написания табличной функции in-line  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
NIIIK
Mnior
чтоб в плане был выбор нужного плана

не понимаю, не шарю. Что это и как?!
См картинку.

NIIIK
Mnior
Динамика, это тоже самое что ReCompile, но только запутаннее в 10 раз.

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

Надеюсь вы понимаете, что особенность OR/UNION и т.п. это из-за разных планов. Где разные индексы, где стратегии связывания. Поэтому эти ухищрения полезны только для интерфейсных запросов (последняя миля). Иначе у вас будет каша в логике и планах.

К сообщению приложен файл. Размер - 25Kb
24 апр 12, 18:35    [12465110]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить