Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
как пооптимальней на любой из последних 3х-4х версий SQL Server (в т.ч. 2019 CTP)
во view вернуть колонну из SP (или в table: computed column из SP)?

t-sql udf функцией вызов SP естественно не работает из-за:
'Only functions and some extended stored procedures can be executed from within a function'

SP конечно-же возвращает скаляр, например:
--drop procedure dbo.xx;
create procedure dbo.xx @x int=10 as
begin select datepart(ms,getdate())+@x as x end;
go
exec dbo.xx 1

запросы к таблицам/представлениям динамически генерирует внешнее приложение/UI
над которым нет контроля (генерирующее только select запросы, т.е. нет возможности execute)
поэтому единственный проходящий на ум путь получения фактов - запуск SP на каждую строку
предварительного набора ключей измерений (благо наборы довольно малые: до 1000 строк)

пока единственное что вижу более-менее рабочее:
это писать функцию через assembly (CLR) {пс: уже написал, т.е. через CLR работает} - что будет
на порядок медленнее т.к. эта CLR по доступу к данным, надо вызывать .NET, устанавливать
соединение и т.д, т.е. чего очень не хотелось-бы (т.к. возможно пойдёт в облако
{Azure} а там с этим немного напряг, хотя уже тоже поддерживает).
а Extended Stored Procedure (ESP) уходят в небытиё.

в общем возвращает к сабжу - как в computed column получить результат из SP (scalar) ?
4 мар 19, 06:40    [21824000]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30806
vikkiv
SP конечно-же возвращает скаляр, например:
В вашем примере SP вернула не "скаляр", а таблицу, состоящую из одной колонки типа datetime, без имени колонки, при этом в возвращаемой табюлице одна запись.
vikkiv
или в table: computed column из SP
Так нельзя.
vikkiv
во view вернуть колонну из SP (или в table: computed column из SP)?
А вот во view или в computed column вызвать UDF можно

vikkiv
запросы к таблицам/представлениям динамически генерирует внешнее приложение/UI
над которым нет контроля (генерирующее только select запросы, т.е. нет возможности execute)
поэтому единственный проходящий на ум путь получения фактов - запуск SP на каждую строку
предварительного набора ключей измерений (благо наборы довольно малые: до 1000 строк)
В общем, что то задача пока непонятна.

Приложение умеет только делать запросы к таблицам? Менять вы эти запросы можете?
Тогда делайте запросы.

Запросы фиксированные, и вам нужно изменить их поведение, не меняя сами запросы?
Тогда меняйте таблицы на view, хотя, конечно, так можно изменить что либо совсем чуть чуть.
4 мар 19, 09:02    [21824039]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
alexeyvg
alexeyvg
...Менять вы эти запросы можете?
...Запросы фиксированные, и вам нужно изменить их поведение, не меняя сами запросы?..


спасибо,
могу только подменить объекты (view/table)

может не понятно выражаюсь, попробую ещё раз:

1) приложение генерирует только простые select a,b,c..from..join..where

2) необходимо туда вставлять результат процедуры SP
- например view: ..x.id,dbo.my_foo(x)as y from dbo.x
или table: ..dbo.z (id smallint, y as dbo.my_foo(z))
где my_foo() вызывает SP (что естественно не работает из-за причин в начальном посте)

3) известная (и сейчас работающая) возможность делать это через функцию как-нибудь вызывающую SP
(пока получилось только через CLR.NET, т.е: create assembly .. create function ...)

есть-ли другие варианты получения обычным SQL (но не сложным Т-SQL)
результата SP в select (без батчей, cte, derived, коррелированных, @, # ..)

в крайнем случае если обходных вариантов совсем нет то наверное можно
попробовать добавить немного T-SQL через @ или # таблицы

(предварительно материализовать результаты не представляется возможным
из-за сложности расчётов в SP и высокой динамики контекста)
4 мар 19, 13:31    [21824302]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
вообще странно что MS всё ещё запрещают делать пользовательские
функции на основе пользовательских процедур, могли-бы
давно вынести это на усмотрение DBA запретив по умолчанию.
4 мар 19, 13:33    [21824306]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
vikkiv,

create proc _retdataset
as
select 1 a
GO
select * from OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'exec _retdataset') AS a;
4 мар 19, 13:54    [21824348]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
WarAnt,

да, openquery/openrowset тоже вариант но в моём случае нужно на вход SP (и как следствие функции)
возвращающей факт - передать данные (набор ключей измерений) без использования переменных @
(чтобы сгенерировать последний параметр openquery), хотя твой приемлеммый вариант по
производительности наверное близко к CLR.NET SP будет, т.к. тоже нужно открывать соединение.
4 мар 19, 14:18    [21824391]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30806
vikkiv
производительности наверное близко к CLR.NET SP будет, т.к. тоже нужно открывать соединение.
для CLR необяззательно открывать соединение, она может работать в том же вызывающем соединении (и в той же транзакции).
vikkiv
3) известная (и сейчас работающая) возможность делать это через функцию как-нибудь вызывающую SP
(пока получилось только через CLR.NET, т.е: create assembly .. create function ...)
Да, только через CLR.

Вообще самый правильный вариант - не вызывать SP, раз уж архитектура на это не расчитана.
Ну или научить приложение вызывать SP?

Вот странно, вы пишите, что приложение может делать только простые запросы, но потом пишите, что "наверное можно попробовать добавить немного T-SQL через @ или # таблицы"
4 мар 19, 14:45    [21824443]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
alexeyvg
Ну или научить приложение вызывать SP?
Вот странно, вы пишите, что приложение может делать только простые запросы, но потом пишите, что "наверное можно попробовать добавить немного T-SQL через @ или # таблицы"

эта просто альтернатива которую держать в запасе на всякий случай (давить на изменение клиенского софта) - просто усилия/ресурсы потребуются намного большие (в несколько раз) чем текущее CLR решение
...пойду копать свой C# на предмет максимальной оптимизации

alexeyvg
Вообще самый правильный вариант - не вызывать SP, раз уж архитектура на это не расчитана.
ну как-бы лучшие практики это одно, а бизнес задачи и необходимость их
решения никто пока не отменял, всё-таки IT работает на бизнес а не наоборот
типа частого мнения что бизнес существует для создания зоны комфорта и феншуя в IT

по наблюдениям: у нас админов встающих в позу и не предлагающих работоспособных и
эффективных альтернатив (с чётким раскладом по рискам) - довольно быстро выкидывают.
4 мар 19, 15:07    [21824485]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30806
vikkiv
alexeyvg
Вообще самый правильный вариант - не вызывать SP, раз уж архитектура на это не расчитана.
ну как-бы лучшие практики это одно, а бизнес задачи и необходимость их
решения никто пока не отменял, всё-таки IT работает на бизнес а не наоборот
типа частого мнения что бизнес существует для создания зоны комфорта и феншуя в IT
Ээээ, бизнес, сэйлы и бухгалтеры, принимают технические решения??? Говорят "вызывайте SP из вьюх"???
Гениально!
vikkiv
по наблюдениям: у нас админов встающих в позу и не предлагающих работоспособных и
эффективных альтернатив (с чётким раскладом по рискам) - довольно быстро выкидывают.
Угу, так я и рассказываю расклады по рискам

Эти принимаемые бухгалтерией и сейлами идиотские технические решения будут кривые, ненадёжные, в разы увеличат расходы на ИТ.
Я понимаю, закрытые решения иногда приходится "приоткрывать", править, скреплять и подпирать палками. Но не настолько же??? Что за странное сочетание несочетаемого?

В общем, примерно так, как если бы программисты указывали продажникам как, по их мнению, надо продавать, и бухгалтерам, как начислять налоги. Что то мне подсказывает, что тут уже бизнес "встанет в позу" и пошлёт таких программистьов "на" и "в" :-)

Но сделать можно, да, с учётом этих рисков.
4 мар 19, 15:24    [21824520]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
Бизнесу вообще по барабану, что внутри. Использовать SP_ (XP_) процедуры в бизнес-задачах - это грехи программиста.
4 мар 19, 15:39    [21824551]     Ответить | Цитировать Сообщить модератору
 Re: как пооптимальней во view вернуть колонну из SP (или в table: computed column из SP)?  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
alexeyvg,

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

проведена оценка выгоды от автоматизации процесса, нужно узнать варианты чего будет стоить решения
выгода > стоимость в разы (если менять софт - то выгода < стоимость)

разработан и оттестирован прототип, архитектура на это рассчитана (CLR, openrowset), риски оценены.

просто думал может местные гуру специализирующиеся в вопросе знают вариант получше - а то вдруг у
меня что забылось (хотя openrowset часто использую - но тут почему-то выскочило как вариант)

так что если альтернатив нет - то вопрос закрыт, всем спасибо.
4 мар 19, 15:46    [21824561]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить