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

Откуда: Москва
Сообщений: 1176
есть два внешних массива, которые сейчас передаются через времянки.
exec sp_executesql N'


IF OBJECT_ID(''tempdb..#p_Get_Form_Data_Tables'') IS NOT NULL DROP TABLE #p_Get_Form_Data_Tables 
create table #p_Get_Form_Data_Tables(Table_ID uniqueidentifier not null primary key,Temp_Table_Name sysname null) 

insert into #p_Get_Form_Data_Tables(Table_ID) values (''83d4e4f5-89de-46f2-b99f-6d066acb069b'') 
insert into #p_Get_Form_Data_Tables(Table_ID) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'') 

IF OBJECT_ID(''tempdb..#p_Get_Form_Data__Table_Fields'') IS NOT NULL DROP TABLE #p_Get_Form_Data__Table_Fields 
create table #p_Get_Form_Data__Table_Fields (Table_ID uniqueidentifier not null, Column_Name sysname collate database_default not null) 

insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'', ''КОСГУ.Code'') 
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'', ''Ф12712_4_SV.Name'') 
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'', ''Column_1'') 
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'', ''Column_2'') 
...
...
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''83d4e4f5-89de-46f2-b99f-6d066acb069b'', ''docflow_Status_ID'') 
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''83d4e4f5-89de-46f2-b99f-6d066acb069b'', ''Document_Status'') 
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'', ''ID'') 
insert into #p_Get_Form_Data__Table_Fields(Table_ID, Column_Name) values (''683697a4-dc8f-41e3-8568-0165aa83cfc8'', ''Document_ID'') 

EXECUTE [global].[p_Get_Form_Data]
@FormVersion_ID = @FormVersion_ID,
@bit_Show_Virtual_Row_Property = @bit_Show_Virtual_Row_Property


',N'@FormVersion_ID uniqueidentifier,@bit_Show_Virtual_Row_Property bit',@FormVersion_ID='110895C2-0F2B-4170-B9CD-84D4A84DAB61',@bit_Show_Virtual_Row_Property=1


подобное приводит к куче мусора в кеше планов, может и еще к чему, я не дочитался.

я вижу три решения:
1.
отдельно создается времянка
отдельно параметризированная вставка каждой строки
отдельно вызов процедуры

2.булк инстерт во времянки

3. массив конвертим в ХМЛ, передаем через 1 параметр. Внутри процедуры перекладываем во времянку

что посоветуете?)
9 июл 15, 14:27    [17872363]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
а если версия позволяет то и можно таблицу паметром в процедуру запхать .
А так в ФАКе по форуму помоему все разобрано по етому поводу
9 июл 15, 14:45    [17872557]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Владислав Колосов
Member

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

почему такая экзотика? Вы знаете об области видимости временных таблиц?
create table #t1 (f1 int)
insert #t1 values(1)
go

alter proc #p1
as 
begin
    if object_id('tempdb..#t1') is null
        return
    select * from #t1
end
go

exec #p1
go

Не хорошо, но, тем не менее...
9 июл 15, 15:23    [17872942]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31977
Владислав Колосов
почему такая экзотика? Вы знаете об области видимости временных таблиц?
Так Mike_za так и делает.
Mike_za
2.булк инстерт во времянки
Это самое быстрое; если данных много, то стоит именно так.
9 июл 15, 15:28    [17872999]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
iap
Member

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

почему такая экзотика? Вы знаете об области видимости временных таблиц?
create table #t1 (f1 int)
insert #t1 values(1)
go

alter proc #p1
as 
begin
    if object_id('tempdb..#t1') is null
        return
    select * from #t1
end
go

exec #p1
go


Не хорошо, но, тем не менее...
Откуда же взялась временная процедура, если ей уже делают ALTER?
Зачем она вообще понадобилась, - не разбирался.
9 июл 15, 15:29    [17873003]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
а в чем у меня проблема с видимостью?
9 июл 15, 15:29    [17873005]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
alexeyvg, много начинается со скольки?
с булком невозможность снять трассу не нравится(
9 июл 15, 15:30    [17873014]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Mike_za
а в чем у меня проблема с видимостью?
внешняя таблица #p_Get_Form_Data_Tables дропается внутри DSQL,
потом с этим именем создаётся локальная для DSQL временная таблица,
которая удаляется после завершения выполнения DSQL.
Снаружи эта временная таблица неизвестна.
Как-то так...
9 июл 15, 15:32    [17873025]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
iap
Mike_za
а в чем у меня проблема с видимостью?
внешняя таблица #p_Get_Form_Data_Tables дропается внутри DSQL,
потом с этим именем создаётся локальная для DSQL временная таблица,
которая удаляется после завершения выполнения DSQL.
Снаружи эта временная таблица неизвестна.
Как-то так...
Разве что эта временная таблица используется в процедуре [global].[p_Get_Form_Data]
9 июл 15, 15:33    [17873032]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Владислав Колосов
Member

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

экзотика в sp_executesql ...
9 июл 15, 15:33    [17873035]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
iap
iap
пропущено...
внешняя таблица #p_Get_Form_Data_Tables дропается внутри DSQL,
потом с этим именем создаётся локальная для DSQL временная таблица,
которая удаляется после завершения выполнения DSQL.
Снаружи эта временная таблица неизвестна.
Как-то так...
Разве что эта временная таблица используется в процедуре [global].[p_Get_Form_Data]
Но даже если это так, дропать всё равно нет никакой необходимости:
внутренняя временная таблица перекрывает видимость всех внешних временных таблиц с тем же именем.
9 июл 15, 15:35    [17873055]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8823
Перекрывает, единственное условие - полное совпадение столбцов таблицы.
9 июл 15, 15:38    [17873082]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31977
Mike_za
alexeyvg, много начинается со скольки?
Ну не знаю... Зависит от ширины записи, больше 1-10 тысяч, наверное... Или даже больше на порядок...

Если меньше, то я бы использовал передачу через табличный параметр.
iap
Разве что эта временная таблица используется в процедуре [global].[p_Get_Form_Data]
Ну, вроде из названий понятно, что это именно так :-)
А то зачем вообще она нужна, эта временная таблица?
9 июл 15, 15:40    [17873092]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31977
Владислав Колосов
экзотика в sp_executesql ...
Это уже особенности настройки клиента.
9 июл 15, 15:41    [17873096]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
alexeyvg
А то зачем вообще она нужна, эта временная таблица?
Если её не дропать, то можно снаружи использовать.
Может, дропают по ошибке, откуда я знаю?
9 июл 15, 15:45    [17873129]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
alexeyvg
Если меньше, то я бы использовал передачу через табличный параметр.


вариант номер 4.
какая нибудь специфика с табличным параметром есть? рекомпиляции, статистика?
9 июл 15, 15:53    [17873181]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31977
Mike_za
какая нибудь специфика с табличным параметром есть? рекомпиляции, статистика?
Специфика такая же, как и при использовании табличных переменных - рекомпиляции нет, планы строятся без учёта статистики, возможность строить дополнительные инедксы ограничена.

Сделано это, или, лучше сказать, недоделано, кривовато, неудобно.
При использовании табличных параметров запрещено менять данные в процедуре, и нужно для них определять тип данных.
В остальном удобная вещь.
9 июл 15, 17:27    [17873754]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
alexeyvg
В остальном удобная вещь.
я бы еще добавил головняки, когда в табличный параметр надо добавить/убрать пару полей, а он уже используется в паре десятков хранимок
9 июл 15, 17:39    [17873816]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
alexeyvg, а что если мне нужна статистика?
я так понимаю, что он всегда ожидает 1 строку
9 июл 15, 17:45    [17873849]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
+ Заливка в переменную @t с клиента как осуществляться будет? опять большая простыня текста? она ж не видна между батчами
9 июл 15, 17:46    [17873856]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31977
Гадя Петрович
alexeyvg
В остальном удобная вещь.
я бы еще добавил головняки, когда в табличный параметр надо добавить/убрать пару полей, а он уже используется в паре десятков хранимок
Ну да, даже это не сделано :-(
Mike_za
alexeyvg, а что если мне нужна статистика?
я так понимаю, что он всегда ожидает 1 строку
Если нужна, то нельзя использовать табличный параметр :-)

Ну и потом, можно ведь поставить опцию RECOMPILE
Mike_za
+ Заливка в переменную @t с клиента как осуществляться будет? опять большая простыня текста? она ж не видна между батчами
У клиентов (библиотек доступа) есть методы для передачи таких параметров, тут всё нормально.

Иначе зачем всё это городить?
9 июл 15, 18:31    [17874070]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
alexeyvg
Гадя Петрович
я бы еще добавил головняки, когда в табличный параметр надо добавить/убрать пару полей, а он уже используется в паре десятков хранимок
Ну да, даже это не сделано :-(
Не все так плохо.
Если среди зависимых от табличного типа объектов нет привязанных к схеме или тех, в которые нужно вносить изменения при изменениях в табличном типе, то вполне можно обойтись без их пересоздания.
9 июл 15, 19:07    [17874171]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31977
invm
alexeyvg
пропущено...
Ну да, даже это не сделано :-(
Не все так плохо.
Если среди зависимых от табличного типа объектов нет привязанных к схеме или тех, в которые нужно вносить изменения при изменениях в табличном типе, то вполне можно обойтись без их пересоздания.
Как "обойтись"?

При попытке дропнуть тип, и создать новый, будет сообщение:
Msg 3732, Level 16, State 1, Line 16
Cannot drop type 'тип' because it is being referenced by object 'процедура'. There may be other objects that reference this type.

А ALTER для типа сделать нельзя.

Так что всё ужасно :-(
9 июл 15, 21:11    [17874480]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
Mike_za
Member

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

Так. А если внутри процедуры перекладывать в #, то опять перекомпиляции постоянные. Можно как-то уменьшить вред от работы с времянками для кеша планов? Есть какие-то бестпрактикс?
9 июл 15, 21:20    [17874495]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше передать массив параметров в процедуру  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
alexeyvg
Как "обойтись"?
+ Примерно так
use tempdb;
go

create type dbo.tTable as table
(
 id int
);
go

create procedure dbo.spTest
 @t dbo.tTable readonly
as
begin
 select * from @t;
end;
go

create function dbo.fnTest
(
 @t dbo.tTable readonly
)
returns table
as
return (select * from @t);
go

declare @v dbo.tTable;
exec dbo.spTest @v;
select * from dbo.fnTest(@v);
go

create type dbo.tTableNew as table
(
 id int,
 v int
);
go

exec sp_rename 'dbo.tTable', 'tTableOld';
exec sp_rename 'dbo.tTableNew', 'tTable';
go

declare @s varchar(max);

select @s = (
 select
  'exec sp_refreshsqlmodule ' + quotename(quotename(referencing_schema_name) + '.' + quotename(referencing_entity_name), '''') + '; '
 from
  sys.dm_sql_referencing_entities('dbo.tTable', 'type')
 for xml path(''), type
).value('.', 'varchar(max)');
exec(@s);
go

drop type dbo.tTableOld;
go

declare @v dbo.tTable;
exec dbo.spTest @v;
select * from dbo.fnTest(@v);
go

drop procedure dbo.spTest;
drop function dbo.fnTest;
drop type dbo.tTable;
go
9 июл 15, 21:40    [17874530]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить